http://sequelserver.blogspot.com/Free SQL Server Articles and Scripts. *New: Learn sql server 2005 Seriesennoreply@blogger.com (Aneesh)Tue, 01 May 2012 09:00:17 PDTBlogger http://www.blogger.com206125noreply@blogger.comnoFree SQL Server Articles and Scripts. *New: Learn sql server 2005 SeriesFree SQL Server Articles and Scripts. *New: Learn sql server 2005 Serieshttp://creativecommons.org/licenses/by/2.0/http://creativecommons.org/images/public/somerights20.gifSqlServer2000http://feedburner.google.comhttp://sequelserver.blogspot.com/2012/04/free-sql-server-command-line-tool.htmlnoreply@blogger.com (Aneesh)Thu, 12 Apr 2012 14:04:11 PDTtag:blogger.com,1999:blog-10550791.post-46615585038726569730There is a free SQL Server command line tool "SQLS*Plus" (on http://www.memfix.com ) - exactly like SQL*Plus for SQL Server. It is very flexible with data formatting (set lines size, pagesize,...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=NirXmeqgCSw:eySy7FDpnuA:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2012/04/moving-system-database.htmlnoreply@blogger.com (Aneesh)Thu, 12 Apr 2012 13:52:23 PDTtag:blogger.com,1999:blog-10550791.post-19081676519281562860If the system database "tempdb" is defined on several data files as recommended in general, each data file is moved by a ALTER DATABASE statement. USE master;GOALTER DATABASE tempdb MODIFY FILE...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=pNFDI5uW8LM:E8H0E7qldFg:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2012/04/modify-sql-agent-errorlog-path-and.htmlnoreply@blogger.com (Aneesh)Thu, 12 Apr 2012 13:47:05 PDTtag:blogger.com,1999:blog-10550791.post-58906364401529574810If you are moving databases to a new drive/volume or another server (different drive letter), you will find it useful to also change the SQL Server Agent log path as well as the Database Default...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=gEAzbVsYt34:rcXYdTi-3h8:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2012/04/tsql-script-to-return-files-and-free.htmlnoreply@blogger.com (Aneesh)Thu, 12 Apr 2012 11:16:40 PDTtag:blogger.com,1999:blog-10550791.post-59658326604708196100Here is the script   ;WITH c AS (SELECT g.name AS [FileGroupName] ,s.type_desc ,s.physical_name AS [FileName] ,s.size * CONVERT(FLOAT, 8) AS [Size] ,CAST(CASE s.type ...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=GXqhnNkWh8s:4z9cztImSjA:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2012/03/how-to-disable-constraints-on-table.htmlnoreply@blogger.com (Aneesh)Tue, 20 Mar 2012 08:48:33 PDTtag:blogger.com,1999:blog-10550791.post-8535155798798555330Sometimes it's useful to disable one or more constraints on a table, do something significant like a BULK INSERT / DELETE, and then re-enable the constraint(s) once you're done. You can disable /...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=FrnX-m-hjrk:_HYA0VbRX1w:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2012/03/script-to-find-size-of-index.htmlnoreply@blogger.com (Aneesh)Wed, 14 Mar 2012 16:04:22 PDTtag:blogger.com,1999:blog-10550791.post-88639558774729501160Simple query to find the size of  the indexes   SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, i.index_id AS IndexID, 8 * SUM(a.used_pages)/1024 AS 'IndexSize in KB'FROM...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=MfQA38wvvBI:1K_C9TjXHs4:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2012/03/sql-server-2012-virtual-launch-event.htmlnoreply@blogger.com (Aneesh)Tue, 06 Mar 2012 13:47:05 PSTtag:blogger.com,1999:blog-10550791.post-82661142655088222860Finally.. the most anticipated version of sql server is ready for launch. Just few more hours..want to register for the virtual launch, click the below link.  there will lot of sessions, including...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=qOakcFpzrF4:2RRqvcMtiFg:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/07/sql-server-migration-checklist.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-9340443101828394971Here is a quick checklist for your SQL Server Migration. 1. Build your New Server, Install SQL Server and required updates and keep the server ready for migration 2. Stop Application Service(s) and...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=eV-1LhLTGIs:41Z9beTSs18:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/06/dbcc-reindex-vs-dbcc-indexdefrag.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-71360774587229498360Note that DBCC DBREINDEX uses one big transaction, killing the operation in the middle would rollback the entire transaction.  It would not help you reduce log space consumption if you restart the...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=mh9VealKM8c:VyDkGZSp9sw:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/06/lists-user-login-attempts-to-sql-server.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-66755896523801136820This script is useful for security audits (helping you gather information about failed login attempts), and for checking recent activity by a particular login (before you delete a login, for...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=Oqy4WHhb1B8:7_fkZ-k-bBA:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/06/script-to-generate-bcp-out-and-bulk.htmlnoreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-44583959736469907210This will be useful while migrating the data from one server to another   SET NOCOUNT ONGOSELECT @@SERVERNAMEDECLARE @path nvarchar(2000), @batchsize nvarchar(40), @format nvarchar(40),...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=YgwCiQUae4c:V9fGexUzR6Y:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/06/query-to-return-fragmentation.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-73369678034258112810This will very useful, if you have implemented the partitioning functionality .   SELECT OBJECT_NAME(a.object_id) AS object_name ,a.index_id ,b.name ,b.type_desc ...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=BSdN1tDU14o:ppNYXgjnoDM:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/05/minimally-logged-inserts-in-sql-server.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-36579889648177939950This is another great feature that was introduced in sql server 2008. This is one of the enhanced features of ‘SELECT INTO ‘ statements. All you need to do is to make sure that the following...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=swS0CQHpjkY:u5sZQv8BZ6U:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/05/parameter-sniffing.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-29307677795046467510When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as "parameter sniffing". This is good as long as...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=wO8yCFGogg4:wL7yR6qEh-8:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/05/find-tables-having-composite-clustered.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-91968192984059128150script to find the tables having composite clustered index ;WITH cte AS (SELECT OBJECT_NAME(i.object_id) NAME, type_desc, i.index_id ,c.name columnName ,p.rows FROM sys.indexes i INNER JOIN...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=wu1fCKFt3QQ:ChDEUTr-apI:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/05/historic-information-sql-backups.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-84335372229715024600Need to find out the historic information of backups , try this script   SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, ...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=um6h1CDCkeo:9gL36QFO_3U:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/04/suggested-max-memory-settings-for-sql.htmlsql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-89147002708091091520It is important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants,...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=Vu_0z5DrlaU:w0KT4e0Mo_8:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/04/use-sysdmosperformancecounters-to-get.htmlScriptssql server 2008noreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-63834812060534078570Buffer cache hit ratio This means what is the percentage that SQL Server had the data in cache and did not have to read the data from disk. Ideally you want this number to be as close to 100 as...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=ZvnKC3LR-Fw:cyOpg8evwNw:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/04/script-percentage-of-identity-values.htmlnoreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-50398504564642364860Here is a script which list out all the identity columns and percentage used   SET NOCOUNT ONSELECT SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName, idc.name AS ColumnName,CASE...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=h4nXymYERZg:mGyYizoMDZQ:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/04/index-fragmentation-on-partitioned.htmlnoreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-4411056880879708050Partitioning can be effectively used to break up a very large table into multiple partitions based on a column or partitioning key. However, there can be significant management overhead in...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=Rqrd1R6Q43I:Lldd_H2KWMM:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/04/find-size-of-database.htmlnoreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-51562919631883139840I encountered the situation recently where I needed to find the size of the database. --size of database SELECT DB_NAME(database_id) AS DatabaseName,SUM((size*8)/1024) SizeInMBFROM...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=5V-ingXG4tM:DzZFxeR6z64:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/03/best-practices-for-indexing.htmlnoreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-32119818735913539370Create index on columns that are frequently used for data retrieval or in filter conditions. For example in SELECT statement and WHERE clause or in ORDER BY, GROUP BY, and DISTINCT clauses. As in our...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=4yCaY-05H5I:Q8wvZ5ezPFM:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/02/how-to-run-stored-procedure-at-sql.htmlnoreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-52637603686101474360This is actually quite simple. There is 'startup' option that you can set to the procedure. There are a few limitations though: - sp must reside in the [master] database - it's owner must be...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=KV-ttlrnGM4:h634AlhDr28:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/02/alternate-to-if-exists-update-else.htmlnoreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-69949825824909384380On some occasions we need to update an entry in a table in case there is an entry otherwise we need to insert that data into the table. The easiest way to do this is something like this IF EXISTS (...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=4ILnkesywDo:ZBUbP-X0P7A:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/http://sequelserver.blogspot.com/2011/02/script-to-check-and-fix-orphan-users.htmlnoreply@blogger.com (Aneesh)Tue, 19 Jul 2011 19:15:02 PDTtag:blogger.com,1999:blog-10550791.post-90434695789711176050    -- Script to check the orphan userEXEC sp_change_users_login 'Report'--Use below code to fix the Orphan User issueDECLARE @username varchar(25)DECLARE fixusers CURSOR FORSELECT UserName = name...<br/> <br/> [[ This is a content summary only. Visit my website for full links, other content, and more! ]]<div class="feedflare"> <a href="http://feeds.feedburner.com/~ff/SqlServer2000?a=x71UWO-BvqY:H_neSO0cilc:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/SqlServer2000?d=yIl2AUoC8zA" border="0"></img></a> </div>http://creativecommons.org/licenses/by/2.0/nonadulthttp://good-times.webshots.com/album/563926801qKuhtaPhotosFri, 27 Jun 2008 07:48:31 PDT<a href="http://good-times.webshots.com/album/563926801qKuhta"><img src="http://videothumb37.webshots.com/thumb/25516/3097887030093185927rbcpMpth_002_0.jpg" width="100" height="74" border="0" alt="P1040129.MOVby aneeshattingal"></a>&nbsp; <a href="http://good-times.webshots.com/album/563926801qKuhta"><img src="http://videothumb22.webshots.com/thumb/25181/3088104140093185927pNrdvzth_002_0.jpg" width="100" height="74" border="0" alt="P1040131.MOVby aneeshattingal"></a>&nbsp; <a href="http://good-times.webshots.com/album/563926801qKuhta"><img src="http://videothumb31.webshots.com/thumb/22390/3095496890093185927yLQjbrth_002_0.jpg" width="100" height="74" border="0" alt="P1040133.MOVby aneeshattingal"></a>&nbsp; <p>my Wien trip</p> <p> by aneeshattingal<br><a href="http://good-times.webshots.com/album/563926801qKuhta">see more photos from this album (156) ...</a></p>