Yaniv Etrogi

Kill all open transactions using the DBCC OPENTRAN command

Yaniv — Sat, 14 Jul 2012 19:48:00 GMT

Recently (last weekend ) I run into a situation where the transaction log of database ReportServerTempDB grew far beyond it’s regular size.

When looking at the log_reuse_wait_desc column of sys.databases it indicated ACTIVE_TRANSACTION so I run a DBCC OPENTRAN command just to find out that the oldest open transaction dates 2 days back. I killed the spid returned from DBCC OPENTRAN and still there was an active transaction from the same time period. So after having to issue the DBCC OPENTRAN command several times followed by a kill command I took a few moments to write the bellow script that did just what I needed; The script executes the results of the DBCC OPENTRAN command into a table and then constructs the kill command based on the data in the tables while looping through until there are no rows in the table.

Note that you will have to run the script in the context of the database you are troubleshooting so that the DBCC OPENTRAN command will take your dbid from the current db eliminating the need to pass it as an argument to the command.

     USE --<---------- edit db name here

    SET NOCOUNT ON;

    IF OBJECT_id('tempdb.dbo.#t1') IS NOT NULL DROP TABLE #t1;

        CREATE TABLE #t1 (ReportServerTempDB sysname, opentran sysname );

    DECLARE @spid sysname, @command varchar(1000), @dt datetime;

    WHILE 1=1

    BEGIN;

        INSERT INTO #t1 EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');

            IF @@ROWCOUNT = 0 BEGIN; PRINT 'BREAK' BREAK; END;

        SELECT @spid = (SELECT TOP 1 opentran FROM #t1 WHERE ReportServerTempDB = 'OLDACT_SPID');

    SELECT    @command = 'kill ' + @spid;

    PRINT        @command;

    --EXEC     (@command);

    --SELECT * FROM #t1

    /*

    SELECT @dt = (SELECT TOP 1 opentran FROM #t1 WHERE ReportServerTempDB = 'OLDACT_STARTTIME');

    SELECT 

         DATEDIFF(MINUTE, @dt, GETDATE()) AS diff_mm

        ,(SELECT TOP 1 opentran FROM #t1 WHERE ReportServerTempDB = 'OLDACT_STARTTIME') AS tran_time

        ,GETDATE() AS now

        ,@spid AS spid ;

    */

    TRUNCATE TABLE #t1;

    END;

    /*

    -- return file information at the database level

    SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT   

         data_space_id AS [fg_id]

        ,FILEGROUP_NAME(data_space_id) [fg_name]

        ,[file_id]

        ,type_desc

        ,f.state_desc

        ,f.name AS logical_name

        ,physical_name

        ,size / 128 SizeMB

        ,FILEPROPERTY(f.name, 'SpaceUsed') / 128 SpaceUsedMB

        ,ROUND((CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS FLOAT) / size) * 100, 2) UsedPct

        ,growth / 128 [GrowthMB]

        ,max_size / 128 [MaxSizeMB]

        ,d.log_reuse_wait_desc

        ,d.recovery_model_desc

    FROM     sys.database_files f

    LEFT JOIN  sys.databases d on d.database_id = DB_ID() and f.type_desc = 'LOG'  

    WHERE 1=1

    --AND state_desc NOT LIKE 'ONLINE'

    ORDER BY data_space_id;

    */

Get the machine uptime in T-SQL

Yaniv — Sun, 29 Apr 2012 20:07:00 GMT

Here is how to get the operating system uptime by querying the sys.dm_os_sys_info DMV.

To get the SQL Server service uptime checkout this post

     SELECT

         DATEADD(s,((-1)*([ms_ticks]/1000)), CURRENT_TIMESTAMP)    AS machine_start_time

        ,CAST([ms_ticks]/1000/60/60.0 AS DECIMAL(15,2))                    AS diff_hh

        ,CAST([ms_ticks]/1000/60/60/24.0 AS DECIMAL(15,2))            AS diff_dd

    FROM sys.[dm_os_sys_info];

Get SQL Server last restart time and uptime

Yaniv — Sun, 29 Apr 2012 19:53:00 GMT

It is sometimes needed to get the sql server uptime, for example you may want to deploy a policy that restarts the service or machine after a specific uptime or because you relay on DMVs for monitoring and reports and since the information they store is cleared when the sql server service is restarted you would like to be aware of such an event and calculate the diff etc..

There are a few ways to get the time that the sql server service was last started and I show here the ways that I am familiar with. Needless to say that it is always recommended to use a documented method where applicable.

The documented way is based on sys.dm_os_sys_info which has the sqlserver_start_time column returning a datetime data type populated with the value of the date and time that the SQL Server service was last started. Note that the DMV got the sqlserver_start_time column added in version 2008 so it can’t be used in 2005.

     SELECT 

         sqlserver_start_time 

        ,DATEDIFF(hour, sqlserver_start_time , CURRENT_TIMESTAMP) AS diff_hh

        ,DATEDIFF(day, sqlserver_start_time , CURRENT_TIMESTAMP)  AS diff_dd

    FROM sys.dm_os_sys_info;

The undocumented ways:

1. The first method relies on tempdb data file creation time. Since tempdb gets created upon every time that the sql server service is started we can use that time pretty much safely even though this is an undocumented method.

2. The second method relies on the first process/ connection established to the server having an spid with a value of 1. If you look at the BOL explanation for the login_time column of sys.sysprocesses it states the following: Time at which a client process logged into the server. For system processes, the time at which the SQL Server startup occurred is stored. This is correct for versions 2005, 2008 & 2008R2 but that last phrase which I marked in bold does not appear at the BOL of version 2012.

    SELECT 

         create_date AS ServiceStartTime

        ,DATEDIFF(hour, create_date, CURRENT_TIMESTAMP) AS diff_hh

        ,DATEDIFF(day, create_date, CURRENT_TIMESTAMP) AS diff_dd

    FROM SYS.databases WHERE database_id = 2;

    SELECT 

         login_time AS ServiceStartTime

        ,DATEDIFF(hour, login_time, CURRENT_TIMESTAMP) AS diff_hh

        ,DATEDIFF(day, login_time, CURRENT_TIMESTAMP) AS diff_dd

    FROM sys.sysprocesses WHERE spid = 1;

Use the Missing Indexes Feature to optimize and tune sql queries

Yaniv — Sat, 28 Apr 2012 20:38:00 GMT

The the Missing Indexes Feature is a great tool that can and should be used by DBAs as it provides useful information out of the box at no additional cost. It has it’s cons but overall this is a great feature. See the bellow link about the feature’s limitations.

Here is a simple query that uses 3 out of the 4 DMVs that make up the database representation of the Missing Indexes Feature to show up information about missing indexes in the current db and construct the CREATE INDEX command.

Some points to consider

-- Before adding indexes always check to see if the new index can be merged/ combined with an existing index

-- Consider the tradeoff between the improvement and cost, i.e. the usage benefit compared with the maintenance cost

-- Use sp_helpindex2 to see indexes information for the table being tuned

     SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @online BIT = 0;

    USE --<-------------- edit database name here

    SELECT TOP 50

       db_name(d.database_id)        AS [database]

        ,object_name(d.object_id) AS [object]

        ,d.equality_columns                     

        ,d.inequality_columns

        ,d.included_columns

        ,s.unique_compiles      

        ,s.user_seeks           

        ,s.user_scans           

        ,s.last_user_seek          

        ,s.last_user_scan          

        ,s.avg_total_user_cost    

        ,s.avg_user_impact    

        ,command = 'CREATE NONCLUSTERED INDEX IX_' + object_name(d.object_id) + '__' + REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns, ''), '[','') , ']',''), ', ','_') 

            + CASE WHEN d.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(d.inequality_columns,''), '[','') ,']','') ,', ','_')

            + ' ON [' + object_name(d.object_id) + '] ('

            + CASE WHEN d.equality_columns IS NOT NULL THEN d.equality_columns ELSE '' END 

            + CASE WHEN d.inequality_columns IS NOT NULL AND d.equality_columns IS NOT NULL THEN ', ' + d.inequality_columns ELSE ISNULL(d.inequality_columns, '') END + ')'

            + CASE WHEN d.included_columns IS NOT NULL THEN ' INCLUDE (' + d.included_columns + ')' ELSE '' END

            + ' WITH (ONLINE = ' + CASE WHEN @online = 1 THEN 'ON);' ELSE 'OFF);' END

    FROM sys.dm_db_missing_index_details d

    INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle

    INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle

    WHERE 1=1

    AND d.database_id = db_id() 

    AND avg_user_impact > 50

    --AND object_name(d.object_id) LIKE ''

    ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans)DESC;

Note that the data in the DMVs (Dynamic Management Objects) is lost when the sql server service is stopped so bare that in mind when taking your decisions and be sure that there is enough uptime to reflect the workload before adding/ dropping indexes and that the tool is not always accurate and has it’s cons.

The following are some relevant links from BOL regarding the feature:

About the Missing Indexes Feature

Limitations of the Missing Indexes Feature

Perform a count comparison on replicated tables between the publisher and subscriber– enhanced

Yaniv — Mon, 23 Apr 2012 19:55:00 GMT

Here is an improvement to a code posted in an older post Perform a count comparison on replicated tables between the publisher and subscriber.

The code introduced in this post also generates (prints) the INSERT commands required to close the diff/gap between the subscriber and publisher.

The script is designed to be executed on the subscriber and you will have to edit the 2 parameters: @LinkedServer and @database which represent the linked server to be used for accessing the publisher and the published database.

     /*

        Run this script on a subscriber of transactional or p2p replication

        The script accesses the publisher via a linked server to pull metadata of row counts for tables participating in the replication 

        (belonging to all publications for the database being queried) and performs a comparison against the tables at the subscription database. 

        The rows with a diff are returned and in addition the script prints the INSERT commands required to close the diff/gap of those rows that 

        exist at the publisher and do not exist at the subscriber

    */

    USE ; --<-------- Edit database name here

    /*

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitMeToString]') AND type in (N'TF'))

        DROP FUNCTION [dbo].[SplitMeToString];

    GO

    CREATE FUNCTION [dbo].[SplitMeToString]

    (

         @sep VARCHAR(32)

        ,@s VARCHAR(MAX)

    )

    RETURNS @t TABLE (val VARCHAR(MAX))

    AS 

    BEGIN;

       DECLARE @xml XML

       SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

       INSERT   INTO @t(val)

                SELECT   RTRIM(LTRIM(r.value('.', 'VARCHAR(max)'))) AS Item

                FROM     @xml.nodes('//root/r') AS RECORDS (r);

       RETURN;

    END;

    */

    SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @LinkedServer sysname, @database sysname, @command varchar(max), @dubug BIT, @diff int; /* Eliminate a diff smaller than this value */

    SELECT @dubug = 0, @diff = 0

    , @LinkedServer = N''    --<-------- Edit linked server name here

    , @database = N''    --<-------- Edit the published database name here

    IF OBJECT_id('tempdb..#sysarticles ') IS NOT NULL DROP TABLE #sysarticles;

    IF OBJECT_id('tempdb..#subscriber ')    IS NOT NULL DROP TABLE #subscriber;

    IF OBJECT_id('tempdb..#publisher ')        IS NOT NULL DROP TABLE #publisher;

    IF OBJECT_id('tempdb..#indexes')            IS NOT NULL DROP TABLE #indexes;

    IF OBJECT_id('tempdb..#data')                    IS NOT NULL DROP TABLE #data;

    /* Get the replication articles from the publisher */

    CREATE TABLE dbo.#sysarticles (article sysname, publication sysname, ident bit);

    SELECT @command = '

    INSERT dbo.#sysarticles (article, publication, ident)

    SELECT 

         a.name    AS article

        ,p.name    AS publication

        ,OBJECTPROPERTY(objid, ''TableHasIdentity'') AS ident 

    FROM [' + @LinkedServer + '].[' + @database + '].dbo.sysarticles a

    INNER JOIN [' + @LinkedServer + '].[' + @database + '].dbo.syspublications p ON p.pubid = a.pubid;';

    IF @dubug = 1 PRINT @command;

    EXEC (@command);

    -- Index information

    SELECT 

         SCHEMA_NAME(o.SCHEMA_ID) AS [Schema]

        ,o.name AS [Table]

        ,i.name AS [Index]

        ,LEFT(list, ISNULL(splitter-1,LEN(list))) AS [Columns]

        ,a.ident

    INTO #indexes

    FROM sys.indexes i

    INNER JOIN sys.objects o ON i.[object_id] = o.[object_id] 

    INNER JOIN dbo.#sysarticles a ON a.article = o.name /* replication tables only */

    INNER JOIN  sys.stats s ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id

    CROSS APPLY (SELECT NULLIF(CHARINDEX('|',indexCols.list),0) splitter , list

                                FROM (SELECT CAST((SELECT CASE WHEN sc.is_included_column = 1 AND sc.ColPos = 1 THEN '|' ELSE '' END + CASE WHEN sc.ColPos  > 1 THEN ', ' ELSE '' END + name

                                                             FROM (SELECT 

                                                                                 sc.is_included_column

                                                                                ,index_column_id

                                                                                ,name

                                                                                ,ROW_NUMBER() OVER (PARTITION BY sc.is_included_column ORDER BY sc.key_ordinal) ColPos

                                                                            FROM sys.index_columns  sc

                                                                            INNER JOIN sys.columns c ON sc.[object_id] = c.[object_id] AND sc.column_id = c.column_id

                                                                            WHERE sc.index_id = i.index_id AND sc.[object_id] = i.[object_id] ) sc

                                                        ORDER BY sc.is_included_column, ColPos

                                                FOR XML PATH (''), TYPE) AS VARCHAR(MAX)) list)indexCols ) indCol

    WHERE i.is_primary_key = 1; /* PK only */

    --AND o.name = '' 

    --AND a.ident = 0

    /* subscriber */

    SELECT

        (row_number() OVER(ORDER BY t3.name, t2.name))%2 AS l1

        ,DB_NAME() AS [database]

        ,t3.name AS [schema]

        ,t2.name AS [table]

        ,t1.rows AS row_count

        ,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB 

        ,(t1.data * 8) / 1024 AS data_MB

        ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) * 8) /1024 AS index_size_MB

        ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

        ,i.[index]

        ,i.columns

        ,i.ident

    INTO dbo.#subscriber

    FROM

     (SELECT 

         ps.object_id

        ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

        ,SUM (ps.reserved_page_count) AS reserved

        ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

        ,SUM (ps.used_page_count) AS used

      FROM sys.dm_db_partition_stats ps

      GROUP BY ps.object_id) AS t1

    LEFT OUTER JOIN 

         (SELECT 

                 it.parent_id

                ,SUM(ps.reserved_page_count) AS reserved

                ,SUM(ps.used_page_count) AS used

            FROM sys.dm_db_partition_stats ps

            INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)

            GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id

            )

    INNER JOIN sys.all_objects t2  ON ( t1.object_id = t2.object_id) 

    INNER JOIN sys.schemas t3 ON (t2.schema_id = t3.schema_id)

    INNER JOIN dbo.#sysarticles a ON a.article = t2.name

    LEFT JOIN #indexes i ON i.[Schema] = SCHEMA_NAME(t2.schema_id) AND i.[Table] = t2.name

    WHERE t2.type <> 'S' and t2.type <> 'IT';

    /* publisher */

    CREATE TABLE dbo.#publisher (l1 int, [database] sysname, [schema] sysname, [table] sysname, row_count int, reserved_MB float, data_MB float, index_size_MB float, unused_MB float);

    SELECT @command = '

    INSERT dbo.#publisher (l1, [database], [schema], [table], row_count, reserved_MB, data_MB, index_size_MB, unused_MB)

    SELECT

        (row_number() over(order by t3.name, t2.name))%2 as l1

        ,DB_NAME() AS [database]

        ,t3.name AS [schema]

        ,t2.name AS [table]

        ,t1.rows AS row_count

        ,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB 

        ,(t1.data * 8) / 1024 AS data_MB

        ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) * 8) /1024 AS index_size_MB

        ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

    FROM

     (SELECT 

         ps.object_id

        ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

        ,SUM (ps.reserved_page_count) AS reserved

        ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

        ,SUM (ps.used_page_count) AS used

      FROM [' + @LinkedServer + '].[' + @database + '].sys.dm_db_partition_stats ps

      GROUP BY ps.object_id) AS t1

    LEFT OUTER JOIN 

     (SELECT 

           it.parent_id

          ,SUM(ps.reserved_page_count) AS reserved

          ,SUM(ps.used_page_count) AS used

      FROM [' + @LinkedServer + '].[' + @database + '].sys.dm_db_partition_stats ps

      INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)

      GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id)

    INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.all_objects t2  ON ( t1.object_id = t2.object_id) 

    INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.schemas t3 ON (t2.schema_id = t3.schema_id)

    INNER JOIN [' + @LinkedServer + '].[' + @database + '].dbo.sysarticles a ON a.name = t2.name

    WHERE t2.type <> ''S'' and t2.type <> ''IT'';';

    IF @dubug = 1 PRINT @command;

    EXEC (@command);

    /* Prepare the final output */

    SELECT 

         @@servername AS [server]

        ,p.[schema]

        ,p.[table]

        ,a.publication

        ,p.reserved_MB    

        ,p.row_count                                 AS publisher_row_count

        ,s.row_count                                 AS subscriber_row_count

        ,p.row_count - s.row_count    AS diff

        ,(SELECT SUM(p.row_count - s.row_count)    AS diff_total FROM #publisher p

                    INNER JOIN #subscriber s ON s.[table] = p.[table] 

                    INNER JOIN #sysarticles a ON a.article = s.[table]

                    WHERE s.row_count <> p.row_count AND (p.row_count - s.row_count > @diff OR @diff IS NULL) ) AS diff_total

        ,s.[columns]

        ,s.[index]

        ,s.ident

    INTO #data

    FROM #publisher p

    INNER JOIN #subscriber s ON s.[table] = p.[table] AND s.[schema] = p.[schema]

    INNER JOIN #sysarticles a ON a.article = s.[table] 

    WHERE s.row_count <> p.row_count

    AND (p.row_count - s.row_count > @diff OR @diff IS NULL);

    /* Return tables with diff */

    SELECT  

             [server]

            ,[schema]

            ,[table]

            ,reserved_MB

            ,publisher_row_count

            ,subscriber_row_count

            ,diff

            ,diff_total 

    FROM #data 

    ORDER BY diff DESC;

    /* Use a cursor over the table to construct the INSERT commands */

    DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR 

        SELECT [schema], [table], [index], [columns], [Ident] FROM #data ORDER BY [table];

    OPEN cur;

    DECLARE @schema sysname, @table sysname, @index sysname, @columns sysname, @cnt int, @where nvarchar(4000), @col_cnt int, @ident bit ;

    DECLARE @colname nvarchar(4000);

    DECLARE @cols table (name nvarchar(4000), col_order int);

    DECLARE @min int, @max int, @columns_list varchar(max), @column sysname, @debug bit;

    --SELECT @columns_list = '';

        FETCH NEXT FROM cur INTO @schema, @table, @index, @columns, @ident;

        WHILE (@@FETCH_STATUS <> -1)

        BEGIN;

        /* Handle cases where the primary keys is composit consisting of more than a single column */

        INSERT INTO @cols SELECT val, ROW_NUMBER() OVER(ORDER BY val) FROM [dbo].[SplitMeToString](',', @columns);

        SELECT @where = 'WHERE';

        SELECT @col_cnt = 0;

        WHILE (SELECT MAX(col_order) FROM @cols) > @col_cnt

        BEGIN;

            SELECT @col_cnt = @col_cnt + 1;

            SELECT @colname = name FROM @cols WHERE col_order = @col_cnt;

            SELECT @where = @where + ' s.' + QUOTENAME(@colname) + ' = p.' + QUOTENAME(@colname) + ' AND';

        END;

        /* remove the last AND of the WHERE string */

        SELECT @where = SUBSTRING(@where,0, LEN(@Where) - 3);

        /*

         Get the PK diff

        SELECT @command =     

        'SELECT @cnt = (SELECT COUNT(*)

         FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

          WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

                + ' s ' + @where + '));'

        IF @debug = 1 PRINT @command;

        --Execute

      EXEC sp_executesql 

          @statement = @command, 

          @params         = N'@cnt int OUTPUT', 

          @cnt             = @cnt OUTPUT;

      */

        /* Tables with no identity column use a simple INSERT...SELECT command */

            IF (@ident = 0)

            BEGIN;

                    SELECT @command =     

                    'INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + CHAR(10) +

                    'SELECT * FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

                        WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

                            + ' s ' + @where + ');' + CHAR(10);

                    PRINT @command;

            END

            /* Tables with identity column construct an explicit column list */

            ELSE IF (@ident = 1)

            BEGIN;

                    SELECT @max = MAX(ORDINAL_POSITION), @min = MIN(ORDINAL_POSITION)     

                    FROM    INFORMATION_SCHEMA.COLUMNS 

                    WHERE TABLE_NAME = @table AND TABLE_SCHEMA = @schema;

                    /* Loop through the columns to build the column list */

                    SELECT @column = '', @columns_list = '';

                    WHILE (@min <= @max )

                        BEGIN;

                            SELECT     

                                 @column = QUOTENAME(COLUMN_NAME) + ','

                            FROM     INFORMATION_SCHEMA.COLUMNS

                            WHERE ORDINAL_POSITION = @min AND TABLE_NAME = @table AND TABLE_SCHEMA = @schema; 

                            SELECT @columns_list = @columns_list + @column, @min = @min + 1 ;

                        END;

                        /* Remove the last comma */

                        SELECT @columns_list = LEFT(@columns_list, LEN(@columns_list) - 1);

                        SELECT @command = 

                        'SET IDENTITY_INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' ON;

                        INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table)+ ' (' + @columns_list + ')'  + CHAR(10) +

                        'SELECT ' + @columns_list + CHAR(10) +

                        ' FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

                            WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

                                + ' s ' + @where + ');

                        SET IDENTITY_INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' OFF;' + CHAR(10);

                        PRINT @command;

                END;

    /*

        --Save the results to a table

        SELECT @command = 

        'INSERT dbo.#Data ([database], [schema], [table], [index], [columns], [cnt])

         SELECT '''

            + DB_NAME() + ''''

            + ',''' + @schema + ''''

            + ',''' + @table + ''''

            + ',''' + @index    + ''''

            + ',''' + @columns + ''''

            + ','        + CAST(@cnt AS sysname) + '';

        --PRINT @command;

        --EXEC (@command);

    */

        SELECT @command = '', @where = '';

        DELETE @cols;

        FETCH NEXT FROM cur INTO @schema, @table, @index, @columns, @ident;

        END;

    CLOSE cur; DEALLOCATE cur;

Get the server wide configuration options that were modified using sp_configure

Yaniv — Mon, 23 Apr 2012 14:32:37 GMT

When working with SQL Server you sometimes have the need to know what server configuration options were modified. The bellow simple script does just that returning 2 result set; the first shows the option that was modified including the default value and the current value being in use and the second result set is a print out of the constructed command.

Download the code here

     -- Yaniv Etrogi 20100628

    -- Get all the server configuration options that were modified from the default settings

    USE master;

    SET NOCOUNT ON;

    IF OBJECT_ID('TEMPDB.dbo.#DefaultConfig') IS NOT NULL DROP TABLE #DefaultConfig;

    CREATE TABLE #DefaultConfig ([name] sysname, minimum int, maximum int, config_value int, run_value int );

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('access check cache bucket count',0,16384,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('access check cache quota',0,2147483647,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Ad Hoc Distributed Queries',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('affinity I/O mask',-2147483648,2147483647,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('affinity mask',-2147483648,2147483647,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Agent XPs',0,1,1,1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('allow updates',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('awe enabled',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('backup compression default',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('blocked process threshold (s)',0,86400,10,10)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('c2 audit mode',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('clr enabled',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('common criteria compliance enabled',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cost threshold for parallelism',0,32767,5,5)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cross db ownership chaining',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cursor threshold',-1,2147483647,-1,-1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Database Mail XPs',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default full-text language',0,2147483647,1033,1033)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default language',0,9999,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default trace enabled',0,1,1,1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('disallow results from triggers',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('EKM provider enabled',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('filestream access level',0,2,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('fill factor (%)',0,100,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft crawl bandwidth (max)',0,32767,100,100)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft crawl bandwidth (min)',0,32767,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft notify bandwidth (max)',0,32767,100,100)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft notify bandwidth (min)',0,32767,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('index create memory (KB)',704,2147483647,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('in-doubt xact resolution',0,2,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('lightweight pooling',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('locks',5000,2147483647,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max degree of parallelism',0,64,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max full-text crawl range',0,256,4,4)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max server memory (MB)',16,2147483647,2147483647,2147483647)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max text repl size (B)',-1,2147483647,65536,65536)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max worker threads',128,32767,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('media retention',0,365,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('min memory per query (KB)',512,2147483647,1024,1024)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('min server memory (MB)',0,2147483647,0,8)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('nested triggers',0,1,1,1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('network packet size (B)',512,32767,4096,4096)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Ole Automation Procedures',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('open objects',0,2147483647,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('optimize for ad hoc workloads',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('PH timeout (s)',1,3600,60,60)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('precompute rank',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('priority boost',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('query governor cost limit',0,2147483647,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('query wait (s)',-1,2147483647,-1,-1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('recovery interval (min)',0,32767,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote access',0,1,1,1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote admin connections',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote login timeout (s)',0,2147483647,20,20)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote proc trans',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote query timeout (s)',0,2147483647,600,600)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Replication XPs',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('scan for startup procs',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('server trigger recursion',0,1,1,1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('set working set size',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('show advanced options',0,1,1,1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('SMO and DMO XPs',0,1,1,1)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('SQL Mail XPs',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('transform noise words',0,1,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('two digit year cutoff',1753,9999,2049,2049)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('user connections',0,32767,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('user options',0,32767,0,0)

    INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('xp_cmdshell',0,1,0,0)

    IF OBJECT_ID('TEMPDB.dbo.#CurrentConfig') IS NOT NULL DROP TABLE #CurrentConfig;

    CREATE TABLE #CurrentConfig ([name] sysname, minimum int, maximum int, config_value int, run_value int );

    INSERT #CurrentConfig ([name], minimum, maximum, config_value, run_value) EXEC sp_configure;

    SELECT 

        c.name, c.minimum, c.maximum, d.config_value AS Default_config_value, d.run_value AS Default_run_value, c.config_value, c.run_value

    FROM #CurrentConfig c

    INNER JOIN #DefaultConfig d ON d.name = c.name

    WHERE c.config_value <> d.config_value OR c.run_value <> d.run_value

    ORDER BY c.name;

    SELECT 'USE master; EXEC sp_configure ' + '''' + c.name + '''' + ', ' + CAST(c.run_value as sysname )

                    + '; RECONFIGURE WITH OVERRIDE;'

        --, c.minimum, c.maximum, d.config_value AS Default_config_value, d.run_value AS Default_run_value

        --, c.config_value, c.run_value

    FROM #CurrentConfig c

    INNER JOIN #DefaultConfig d ON d.name = c.name

    WHERE c.config_value <> d.config_value OR c.run_value <> d.run_value

    ORDER BY c.name;

update to sp_helpindex2

Yaniv — Mon, 23 Apr 2012 14:16:31 GMT

I have added the following 2 useful columns to the output: reserved_mb and compression derived from sys.partitions and sys.allocation_units respectively showing the index size and if it is compressed (PAGE, ROW or NONE).

Go a head and download the latest version.

Perform a count comparison on replicated tables between the publisher and subscriber

Yaniv — Sat, 11 Feb 2012 21:17:00 GMT

Here is useful script to be run on the subscriber and get the diff in row counts between the publisher and subscriber for tables participating in Transactional or P2P replication (Merge replication will require a quick modification for tables i.e.: sysarticles --> sysmergearticles and syspublications --> sysmergepublications).

     /*

        To be run on the subscriber of a Transactional Replication

        Perform a count comparison on replicated tables between the publisher and subscriber based on the storage meta data 

        (remember that there may be still commands in the distribution queue).

        Yaniv Etrogi 20120211

        www.sqlserverutilities.com

    */

    --USE DatabaseName;

    SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @LinkedServer varchar(100), @command varchar(8000), @Dubug bit;

    SELECT @Dubug = 0, @LinkedServer = 'xxxxxxxxxxxxxxx'; --<----------- Enter linked server name here

    IF OBJECT_id('tempdb..#sysarticles ') IS NOT NULL DROP TABLE #sysarticles;

    IF OBJECT_id('tempdb..#subscriber ')    IS NOT NULL DROP TABLE #subscriber;

    IF OBJECT_id('tempdb..#publisher ')        IS NOT NULL DROP TABLE #publisher;

    CREATE TABLE #sysarticles (article sysname, publication sysname);

    CREATE TABLE #publisher (l1 INT, [database] sysname, [schema] sysname, [table] sysname, row_count int, reserved_MB int, data_MB int, index_size_MB int, unused_MB int) ;

    /* Get the replication articles from the publisher */

    SELECT @command = 

    'INSERT dbo.#sysarticles (article, publication)

    SELECT 

         a.name    AS article

        ,p.name    AS publication

    FROM ' + @LinkedServer + '.BurstingDB.dbo.sysarticles A

    INNER JOIN ' + @LinkedServer + '.BurstingDB.dbo.syspublications p ON p.pubid = a.pubid;' + CHAR(10);

    IF @Dubug = 1 PRINT @command;

    EXEC (@command);

    /* subscriber */

    SELECT

        (row_number() over(order by t3.name, t2.name))%2 as l1

        ,DB_NAME() AS [database]

        ,t3.name AS [schema]

        ,t2.name AS [table]

        ,t1.rows AS row_count

        ,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB 

        ,(t1.data * 8) / 1024 AS data_MB

        ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) * 8) /1024 AS index_size_MB

        ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

    INTO dbo.#subscriber

    FROM

     (SELECT 

         ps.object_id

        ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

        ,SUM (ps.reserved_page_count) AS reserved

        ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

        ,SUM (ps.used_page_count) AS used

      FROM sys.dm_db_partition_stats ps

      GROUP BY ps.object_id) AS t1

    LEFT OUTER JOIN 

     (SELECT 

           it.parent_id

          ,SUM(ps.reserved_page_count) AS reserved

          ,SUM(ps.used_page_count) AS used

      FROM sys.dm_db_partition_stats ps

      INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)

      GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id)

    INNER JOIN sys.all_objects t2  ON ( t1.object_id = t2.object_id) 

    INNER JOIN sys.schemas t3 ON (t2.schema_id = t3.schema_id)

    INNER JOIN dbo.#sysarticles a ON a.article = t2.name

    WHERE t2.type <> 'S' and t2.type <> 'IT';

    /* publisher */

    SELECT @command = 

    'INSERT dbo.#publisher

    SELECT

        (row_number() over(order by t3.name, t2.name))%2 as l1

        ,DB_NAME() AS [database]

        ,t3.name AS [schema]

        ,t2.name AS [table]

        ,t1.rows AS row_count

        ,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB 

        ,(t1.data * 8) / 1024 AS data_MB

        ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) * 8) /1024 AS index_size_MB

        ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

    FROM

     (SELECT 

         ps.object_id

        ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

        ,SUM (ps.reserved_page_count) AS reserved

        ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

        ,SUM (ps.used_page_count) AS used

      FROM ' + @LinkedServer + '.[BurstingDB].sys.dm_db_partition_stats ps

      GROUP BY ps.object_id) AS t1

    LEFT OUTER JOIN 

     (SELECT 

           it.parent_id

          ,SUM(ps.reserved_page_count) AS reserved

          ,SUM(ps.used_page_count) AS used

      FROM ' + @LinkedServer + '.[BurstingDB].sys.dm_db_partition_stats ps

      INNER JOIN ' + @LinkedServer + '.[BurstingDB].sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)

      GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id)

    INNER JOIN ' + @LinkedServer + '.[BurstingDB].sys.all_objects t2  ON ( t1.object_id = t2.object_id) 

    INNER JOIN ' + @LinkedServer + '.[BurstingDB].sys.schemas t3 ON (t2.schema_id = t3.schema_id)

    INNER JOIN ' + @LinkedServer + '.[BurstingDB].dbo.sysarticles a ON a.name = t2.name

    WHERE t2.type <> ''S'' and t2.type <> ''IT'';';

    IF @Dubug = 1 PRINT @command;

    EXEC (@command);

    SELECT 

         @@servername

        ,p.[table]

        ,a.publication

        ,p.reserved_MB    

        ,p.row_count                                 AS publisher_row_count

        ,s.row_count                                 AS subscriber_row_count

        ,p.row_count - s.row_count    AS diff

    FROM #publisher p

    INNER JOIN #subscriber s ON s.[table] = p.[table] 

    INNER JOIN #sysarticles a ON a.article = s.[table]

    WHERE s.row_count <> p.row_count

    ORDER BY diff DESC;

Construct the RESTORE LOG commands

Yaniv — Tue, 13 Dec 2011 21:26:00 GMT

here is another quick and dirty option that can be very useful when you need to quickly construct the RESTORE LOG commands:

     USE master;

    DECLARE @t TABLE (fname varchar(max));

    INSERT @t EXEC xp_cmdshell 'dir /b /S N:\Backup\MyDB\*.trn'; --<---- edit path and database name here

    SELECT 'RESTORE LOG [MyDB] FROM DISK = ''' + fname + ''' WITH NORECOVERY;' FROM @t WHERE fname IS NOT NULL;

Modify foreign keys constraints to NOT FOR REPLICATION

Yaniv — Sun, 04 Dec 2011 18:17:00 GMT

Here is a script to modify the database schema and define all foreign keys as NFR (NOT FOR REPLICATION).

The script does not execute anything but only generates the commands.

NFR is required in a 2 way transactional replication and in p2p replication. In a 1 way transactional replication NFR is not required but is a good practice to have the subscriber’s schema set as NFR.

For additional information regarding NFR:

http://msdn.microsoft.com/en-us/library/ms152529.aspx

     /*

    1. Generate the DROP FK constraint command

    2. Generate the ADD WITH NOCHECK FK constraint command 

    3. Generate the CHECK FK constraint command 

    http://www.sqlserverutilities.com/

    */

    SET NOCOUNT ON;

    -- Generate the DROP FK constraint command 

    SELECT DISTINCT 

        'ALTER TABLE [' + object_name(f.parent_object_id) + '] DROP CONSTRAINT [' + f.name + '];' + CHAR(13) + CHAR(10) + 'GO'

    FROM sys.foreign_keys f 

    WHERE f.is_not_for_replication = 0;

    -- Generate the ADD FK constraint command WITH NOCHECK

    DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR

    SELECT object_id , parent_object_id, referenced_object_id FROM sys.foreign_keys WHERE is_not_for_replication = 0;

    SET NOCOUNT ON;

    OPEN cur;

    DECLARE @object_id int, @parent_object_id int, @referenced_object_id int, @i int = 1;

    DECLARE @fk_cols varchar(8000) = '', @ref_cols varchar(8000) = '';

    FETCH NEXT FROM cur INTO @object_id, @parent_object_id, @referenced_object_id;

    WHILE (@@FETCH_STATUS <> -1 ) 

    BEGIN;

        SELECT  

                 @fk_cols        = @fk_cols + COALESCE([fk_col_name] + ',', '')

                ,@ref_cols    = @ref_cols + COALESCE([ref_col_name] + ',', '')

            FROM    

                ( 

                            SELECT    

                                         '[' + c.name + ']' AS [fk_col_name]

                                        ,'[' + d.name + ']' AS [ref_col_name]

                                FROM      sys.foreign_keys f

                                                    INNER JOIN sys.foreign_key_columns b ON f.object_id = b.constraint_object_id

                                                    INNER JOIN sys.columns c ON b.parent_column_id = c.column_id AND f.parent_object_id = c.object_id

                                                    INNER JOIN sys.columns d ON b.referenced_column_id = d.column_id AND f.referenced_object_id = d.object_id

                                WHERE     f.parent_object_id = @parent_object_id

                                                    AND f.object_id = @object_id

                                                    AND f.is_not_for_replication = 0

                ) d;

        SELECT @fk_cols =  LEFT(@fk_cols, LEN(@fk_cols) - 1), @ref_cols =  LEFT(@ref_cols, LEN(@ref_cols) - 1);

            PRINT '-- ' + CAST(@i AS sysname)

        PRINT 'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(@parent_object_id)) + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id)) + ' FOREIGN KEY (' + @fk_cols + ')  

      REFERENCES ' + QUOTENAME(OBJECT_NAME(@referenced_object_id)) + ' (' + @ref_cols + ')NOT FOR REPLICATION;' + CHAR(10) + 'GO'

            SELECT @fk_cols = '', @ref_cols = '';

            SELECT @i = @i + 1;

    FETCH NEXT FROM cur INTO @object_id, @parent_object_id, @referenced_object_id;    

    END;

    CLOSE cur; DEALLOCATE cur;

    -- Generate the CHECK FK constraint command 

    SELECT DISTINCT 

        'ALTER TABLE [' + object_name(f.parent_object_id) + '] CHECK CONSTRAINT [' + f.name + '];' + CHAR(13) + CHAR(10) + 'GO'

    FROM sys.foreign_keys f 

    WHERE f.is_not_for_replication = 0;

Modify check constraints to NOT FOR REPLICATION

Yaniv — Sun, 04 Dec 2011 18:13:00 GMT

Here is a script to modify the database schema and define all Check constraints as NFR (NOT FOR REPLICATION).

The script does not execute anything but only generates the commands.

For additional information regarding NFR:

http://msdn.microsoft.com/en-us/library/ms152529.aspx

     /*

    1. Generate the DROP CK constraint command

    2. Generate the ADD WITH NOCHECK CK constraint command 

    3. Generate the CHECK CK constraint command 

    */

    SET NOCOUNT ON;

    -- Generate the DROP CK constraint command 

    SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.SCHEMA_ID)) + '.' + QUOTENAME(OBJECT_NAME(o.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME) + CHAR(10) + 'GO'

    FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS c

    INNER JOIN sys.objects o ON c.CONSTRAINT_NAME = o.[name]

    WHERE 1=1

    AND OBJECTPROPERTY(o.object_id, N'IsCheckCnst') = 1

    AND OBJECTPROPERTY(o.object_id, N'CnstIsNotRepl') = 0;

    -- Generate the ADD CK constraint command WITH NOCHECK

    SELECT  'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.SCHEMA_ID)) + '.' + QUOTENAME(OBJECT_NAME(o.parent_object_id)) 

            + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME) + ' CHECK NOT FOR REPLICATION ' + CHECK_CLAUSE + CHAR(10) + 'GO'

    FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS c

    INNER JOIN sys.objects o ON c.CONSTRAINT_NAME = o.[name]

    WHERE 1=1

    AND OBJECTPROPERTY(o.object_id, N'IsCheckCnst') = 1

    AND OBJECTPROPERTY(o.object_id, N'CnstIsNotRepl') = 0;

    -- Generate the CHECK CK constraint command 

    SELECT  'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.SCHEMA_ID)) + '.' + QUOTENAME(OBJECT_NAME(o.parent_object_id)) 

            + ' CHECK CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME) + CHAR(10) + 'GO'

    FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS c

    INNER JOIN sys.objects o ON c.CONSTRAINT_NAME = o.[name]

    WHERE 1=1

    AND OBJECTPROPERTY(o.object_id, N'IsCheckCnst') = 1

    AND OBJECTPROPERTY(o.object_id, N'CnstIsNotRepl') = 0;

Monitor Replication Delivery Latency

Yaniv — Mon, 21 Nov 2011 14:37:00 GMT

There are a few ways to measure and monitor the latency in transactional replication.

Here is one method which I like built on top of the system stored procedures sp_posttracertoken and sp_helptracertokenhistory.

I share the stored procedure I use called MonitorReplicationDeliveryLatency at the various production environments where I maintain and monitor transactional replication. The procedure measures the distribution latency which is the time that took for the token (a dummy tran) to make it from the published database transaction log to the distribution database, a task done by the Log Reader Agent and the Subscriber latency which is the time that took the Distribution Agent to move that same token from the distribution database to the subscriber’s subscription database.

For the details about the system stored procedures sp_posttracertoken and sp_helptracertokenhistory see the following link:

Measuring Latency and Validating Connections for Transactional Replication

The following command uses the stored procedure I share and passes a threshold latency of 5 seconds to the logreader and 4 seconds to the distribution agent and then waits for a 5 seconds time period before querying to find out the latency results.

When used manually exec with the @Debug = 1 so that the result set is returned and when implemented in a job or by a monitoring tool use with @Debug = 0 so that no result set is returned to the calling party because if the threshold is crossed the result set is sent by mail

     EXEC dbo.MonitorReplicationDeliveryLatency @DistributionThreshold = 5, @SubscriberThreshold = 4, @Publication = 'TEST', @Delay = '00:00:05', @Debug = 1;

Here is the store procedure:

    USE Published_Database;

    SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;

    GO

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MonitorReplicationDeliveryLatency]') AND type IN (N'P', N'PC'))

        DROP PROCEDURE dbo.MonitorReplicationDeliveryLatency;

    GO  

    CREATE PROCEDURE [dbo].MonitorReplicationDeliveryLatency    

    (

         @DistributionThreshold int

        ,@SubscriberThreshold        int

        ,@Publication                        sysname

        ,@Delay                                    char(8)= '00:00:10'

        ,@Debug                                    BIT = 0

    )

    /*

        Yaniv Etrogi

        http://www.sqlserverutilities.com

    */

    AS      

    SET NOCOUNT ON;     

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  

    --DECLARE @DistributionThreshold int, @SubscriberThreshold int, @Publication sysname, @Delay char(8);

    --SELECT @DistributionThreshold = 3, @SubscriberThreshold = 5, @Publication = 'test', @Delay = '00:00:03';

    --EXEC dbo.MonitorReplicationDeliveryLatency @DistributionThreshold = 5, @SubscriberThreshold = 4, @Publication = 'TEST', @Delay = '00:00:05', @Debug = 1;

    DECLARE @tokenid int;

    -- Insert a tracer token at the publication database.

    EXEC sys.sp_posttracertoken 

                             @publication            = @publication

                            ,@tracer_token_id = @tokenid OUTPUT;

    -- Wait for the token to make it to the Subscriber.

    WAITFOR DELAY @Delay;

    -- Get latency information for the last inserted token.

    CREATE TABLE #tokens (tracer_id    int, publisher_commit datetime);

    INSERT #tokens (tracer_id, publisher_commit) EXEC sys.sp_helptracertokens @publication = @publication;

    SELECT @tokenid = (SELECT TOP 1 tracer_id FROM #tokens ORDER BY publisher_commit DESC);

    -- Get history for the tracer token.

    CREATE TABLE #TokenHistory(distributor_latency bigint, subscriber    sysname, subscriber_db sysname, subscriber_latency bigint, overall_latency bigint);

    INSERT #TokenHistory (distributor_latency, subscriber, subscriber_db, subscriber_latency, overall_latency )

    EXEC sys.sp_helptracertokenhistory 

                             @publication = @publication

                            ,@tracer_id        = @tokenid;

    IF @Debug = 1 

        SELECT distributor_latency, subscriber, subscriber_db, subscriber_latency, overall_latency FROM #TokenHistory;

    /* If the threshold is crossed */

    IF   ( ((SELECT distributor_latency FROM #TokenHistory) IS NULL) OR ((SELECT distributor_latency FROM #TokenHistory) > @DistributionThreshold) )

        OR ( ((SELECT subscriber_latency  FROM #TokenHistory) IS NULL) OR ((SELECT subscriber_latency FROM #TokenHistory)  > @SubscriberThreshold  ) )

    BEGIN;

    -- send mail

        DECLARE @recipients nvarchar(1000), @subject nvarchar(1000), @profile_name nvarchar(1000), @body_format nvarchar(100), @msg nvarchar(4000);

        SELECT @msg =        N'The replication delivery letancy has crossed the letancy threshod defined: ' + 

                                        N' Distribution Threshold - ' + CAST (@DistributionThreshold AS sysname) + 

                                        N' Subscriber Threshold - '        + CAST (@SubscriberThreshold AS sysname) +

                                        N'<br><br><table CELLSPACING="0" border="1">

                                        <tr><td width="100"><font color="red">

                                        <U>distributor_latency</U></td><td width="100"><font color="red">

                                        <U>subscriber</U></td><td width="100"><font color="red">

                                        <U>subscriber_db</U></td><td width="100"><font color="red">

                                        <U>subscriber_latency</U></td><td width="100"><font color="red">

                                        <U>overall_latency</U>

                                        </td></tr>';

        SELECT  @msg = @msg 

                                    + '<tr><td>'  + CASE WHEN distributor_latency IS NULL THEN 'NULL' ELSE CAST(distributor_latency AS sysname) END

                                    + '</td><td>' + subscriber 

                                    + '</td><td>' + subscriber_db 

                                    + '</td><td>' + CASE WHEN subscriber_latency IS NULL THEN 'NULL' ELSE CAST(subscriber_latency AS sysname) END

                                    + '</td><td>' + CASE WHEN overall_latency IS NULL THEN 'NULL' ELSE CAST(overall_latency AS sysname) END

                                    + '</td><tr>'

                                    + '</table>'

        FROM #TokenHistory ;

        SELECT 

                     @recipients        = N'someone@domain.com' 

                    ,@subject                =    @@SERVERNAME + N': MonitorReplicationDeliveryLatency'

                    ,@profile_name    = N'SqlMail' 

                    ,@body_format        =    N'HTML'

        EXEC msdb.dbo.sp_send_dbmail 

                     @recipients        = @recipients

                    ,@body                    = @msg

                    ,@subject                = @subject 

                    ,@profile_name    = @profile_name 

                    ,@body_format        = @body_format;

    END;

Define identity columns as NOT FOR REPLICATION

Yaniv — Mon, 31 Oct 2011 20:29:00 GMT

In sql server 2000 there was no other way but the painful costly method of creating the table with a temp name while defining the ident col as NFR (NOT FOR REPLICATION ), move the data, drop the original table, rename the new table and create all other objects related to the table such as constraints. triggers etc.

However, sql server 2005 introduced the system procedure sp_identitycolumnforreplication that allows you to achieve the same results while not needing the data movement process described above.

-- Mark Ident column as NOT FOR REPLICATION for all tables in the database

     -- Mark Ident column as NOT FOR REPLICATION

    EXEC sp_msforeachtable 

        @command1 = 'declare @int int set @int =object_id("?") EXEC sys.sp_identitycolumnforreplication @int, 1';

-- Here is the procedure code:

CREATE PROCEDURE sys.sp_identitycolumnforreplication

 @object_id int,

 @value  bit

AS

BEGIN

 DECLARE @identity_column sysname

 IF IS_SRVROLEMEMBER('sysadmin') = 0

  AND IS_MEMBER('db_owner') = 0

 BEGIN

  RAISERROR(21050, 14, -1)

  RETURN 1

END

 SELECT @identity_column = NULL

 SELECT @identity_column = name

  FROM sys.columns

  WHERE object_id = @object_id

   AND COLUMNPROPERTY(object_id, name, 'IsIdentity') = 1

 IF @identity_column IS NOT NULL

 BEGIN

  EXEC %%ColumnEx(ObjectID = @object_id, Name = @identity_column).SetIdentityNotForRepl(Value = @value)

  IF @@ERROR <> 0

   RETURN 1

END

 RETURN 0

END

--------------------------------------------------------------------------------------

Using forfiles to delete backup files

Yaniv — Sun, 30 Oct 2011 21:44:45 GMT

Tired of SQL Server’s Maintenance Cleanup Task not doing it’s job ? well, there are many others like you out there….

Checkout the forfiles command as an alternative. It provides a simple and reliable way to handle the files clean up task.

Here is an example that can be used in a job step of type Operating System (CmdExc):

forfiles /p "D:\Backup\Daily" /m *.bak /s /c "cmd /c del @file" /d –8

-----------------------------------

Trace flag 2371

Yaniv — Mon, 26 Sep 2011 17:55:00 GMT

I know many DBAs that are not yet aware of this trace flag and will certainly be very happy to hear about it as they can get an immediate benefit. I just run into it recently at this post and plan to implement soon.

Trace flag 2371 finally comes in to address the well know problem of the current 20% threshold used in the automatic update of statistics.

The source to the problem is that statistics are updated when 20% (+ 500 rows) of the data has changed. This means that a table with 10,000 rows will cross the threshold at 2,500 rows and a table with 100,000 rows will cross the threshold at 20,500 rows and so on. Once the threshold is crossed the statistics are marked as invalidated and remain in this state until the next time the Optimizer needs the statistics when generating an execution plan.

While this may be a good choice in most cases it becomes problematic when dealing with large tables. As the table size grows the 20% figure increases accordingly and so does the time interval between each time the threshold is crossed. This leads to stale statistics resulting in suboptimal execution plans which in large sets of data can be a disaster.

Therefore in such environments DBAs typically create their own update statistics job to get the stats manually updated thus eliminating the need to rely on crossing the 20% threshold of the auto update of stats. With the new 2371 trace flag these jobs that generate a lot of IO will no longer be needed.

For additional information on statistics check out the Microsoft article Statistics Used by the Query Optimizer in Microsoft SQL Server and this post at sqlskills.

-Yaniv