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];

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;

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;

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;

/*

    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;

-- 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;

/*

    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;

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;

/*

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;

/*

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;

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

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;

-- Mark Ident column as NOT FOR REPLICATION

EXEC sp_msforeachtable 

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

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  

SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

USE --< Enter user database name ;

DECLARE @compression_type sysname;

SELECT @compression_type = N'PAGE'; --<---- Valid values are: PAGE or ROW

IF OBJECT_ID('tempdb..#estimate_data_compression_savings') IS NOT NULL DROP TABLE #estimate_data_compression_savings; 

CREATE TABLE #estimate_data_compression_savings 

(

     Id int identity (1,1) PRIMARY KEY CLUSTERED NOT NULL

     ,[File_Group]                                                                                        sysname NULL

    ,[is_read_only]                                                                                    bit  NULL

    ,[index_name]                                                                                        sysname  NULL

    ,[type]                                                                                                    tinyint NULL

    ,[data_compression_desc]                                                                sysname  NULL

    ,[rows]                                                                                                    bigint NULL    

    ,[reserved_page_count]                                                                    bigint NULL

    ,[reserved_MB]                                                                                    bigint   NULL

    ,object_name                                                                                          sysname NOT NULL

    ,schema_name                                                                                          sysname NOT NULL

    ,index_id                                                                                                 int NOT NULL

    ,partition_number                                                                                 int NOT NULL

    ,partition_count                                                                                int  NULL

    ,[size_with_current_compression_setting (KB)]                         bigint NOT NULL

    ,[size_with_requested_compression_setting (KB)]                     bigint NOT NULL

    ,[sample_size_with_current_compression_setting (KB)]         bigint NOT NULL

    ,[sample_size_with_requested_compression_setting (KB)]     bigint NOT NULL

    ,[command]                                                                                            varchar(8000) NULL

    ,[error]                                                                                                varchar(8000) NULL

);

IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data; 

CREATE TABLE #Data  

(  

   schemaname sysname  

  ,tablename  sysname  

  ,objectid  int  

  ,indexname  sysname  

  ,indexid   int  

  ,[blob]   bit  

);  

-- Get meta data for the rebuild process.  

INSERT #Data (schemaname, tablename, objectid, indexname, indexid, blob)  

SELECT   

  Derived.schemaname  

 ,Derived.tablename  

 ,Derived.tableid  

 ,Derived.indexname  

 ,Derived.indexid  

 ,Derived.blob  

FROM  

  (  

   -- Get clustered indexes information   

   SELECT  

     SCHEMA_NAME(Derived2.[schema_id])    AS schemaname  

    ,Derived2.tablename                                    AS tablename  

    ,Derived2.[object_id]                                AS tableid  

    ,i.name                                                            AS indexname  

    ,i.index_id                                                    AS indexid  

    ,Derived2.blob                                            AS blob  

   FROM  

     (  

      SELECT  

         t.[object_id] AS [object_id]  

        ,t.[name]    AS tablename  

        ,t.[schema_id] AS [schema_id]  

        ,CASE WHEN EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND (system_type_id IN (35, 34, 241, 99) /* column type that doesn't support on-line index rebuild. (text,image,XML,ntext -> 35,34,241,99) */ OR max_length < 0) ) THEN 1 ELSE 0 END AS blob  

       FROM sys.tables t

      )Derived2  

   INNER JOIN sys.indexes i ON Derived2.[object_id] = i.[object_id]  

   WHERE i.type = 1 /* clustered */  

   UNION   

    -- Get nonclustered index information  

    SELECT  

      SCHEMA_NAME(t.schema_id)    AS schemaname  

     ,t.Name                              AS [tablename]   

     ,t.[object_id]                       AS tableid  

     ,i.name                              AS [indexname]  

     ,i.index_id                          AS indexid  

     ,CASE WHEN EXISTS ( SELECT 1 FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id AND (c.system_type_id IN (35, 34, 241, 99) /* column type that doesn't support on-line index rebuild. (text,image,XML,ntext -> 35,34,241,99) */ OR c.max_length < 0) ) THEN 1 ELSE 0 END AS blob  

    FROM sys.tables t   

    INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id]  

    WHERE i.type > 1 /* nonclustered */  

  )Derived  

ORDER BY Derived.tablename;  

DECLARE CUR CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 

SELECT DISTINCT 

    --TOP 2

     fg.name AS [File_Group]

    ,fg.is_read_only

    ,s.[name] AS [schema_name]

    --,o.[object_id]

    ,o.[name] AS [object_name]

    ,i.index_id

    ,i.[name] [index_name]

    ,i.[type]

    ,p.partition_number

    ,(SELECT COUNT(*) FROM sys.partitions part WHERE part.[object_id] = p.[object_id] AND part.[index_id] = p.[index_id]) AS partitions_count

    ,p.data_compression_desc

    ,p.[rows]

    --,au.total_pages 

    --,ps.used_page_count 

    --,ps.row_count 

    ,ps.reserved_page_count 

    ,(ps.reserved_page_count * 8) / 1024 AS reserved_MB 

    ,#Data.blob

    ,#Data.indexid

FROM  sys.indexes i

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

INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

INNER JOIN sys.partitions p ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id

INNER JOIN sys.allocation_units au ON  p.hobt_id = au.container_id

INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id   

INNER JOIN sys.dm_db_partition_stats ps ON ps.partition_id = p.partition_id      

INNER JOIN #Data ON #Data.tablename COLLATE DATABASE_DEFAULT = o.name AND #Data.schemaname COLLATE DATABASE_DEFAULT = s.name AND #Data.indexid = i.index_id

WHERE 1=1

AND fg.is_read_only = 0

AND p.[rows] > 0

AND (ps.reserved_page_count * 8) / 1024 > 500 /* MB */

/* Get the active partition */

AND

( 

    EXISTS(SELECT 1 FROM sys.partitions p_empty WHERE p.[object_id] = p_empty.[object_id] AND p.index_id = p_empty.index_id AND p.partition_number = p_empty.partition_number - 1 AND p_empty.[rows] = 0 ) 

    OR

    NOT EXISTS(SELECT 1 FROM sys.partitions p_empty2 WHERE p.[object_id] = p_empty2.[object_id] AND p.index_id = p_empty2.index_id AND p_empty2.partition_number = 2 )

)

ORDER BY o.[name] DESC;

OPEN CUR;

DECLARE @File_Group sysname, @is_read_only bit, @schema_name sysname, @object_name sysname, @index_id int, @index_name sysname, @type sysname

            , @partition_number int, @data_compression_desc sysname, @rows int, @reserved_page_count int, @reserved_MB int, @partition_count int;

DECLARE @Ident int, @command varchar(8000), @error varchar(8000), @blob BIT, @indexid    int;

    FETCH NEXT FROM CUR INTO @File_Group, @is_read_only, @schema_name, @object_name,@index_id, @index_name, @type, @partition_number, @partition_count, @data_compression_desc, @rows, @reserved_page_count, @reserved_MB, @blob, @indexid ;

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN;

    BEGIN TRY

    /* exec the procedure sp_estimate_data_compression_savings into a table */

    INSERT dbo.#estimate_data_compression_savings

             ([object_name]

             ,[schema_name]

             ,[index_id]

             ,[partition_number]

             ,[size_with_current_compression_setting (KB)]

             ,[size_with_requested_compression_setting (KB)]

             ,[sample_size_with_current_compression_setting (KB)]

             ,[sample_size_with_requested_compression_setting (KB)])

    EXEC sp_estimate_data_compression_savings 

                @schema_name             = @schema_name

             ,@object_name             = @object_name

             ,@index_id                    = @index_id

             ,@partition_number    = @partition_number 

             ,@data_compression    = @compression_type;

        SELECT @Ident = SCOPE_IDENTITY();

/*

SELECT @command = '

EXEC sp_estimate_data_compression_savings 

            @schema_name             = '''    + @schema_name + '''

             ,@object_name             = '''    + @object_name + '''

             ,@index_id                    = '        + CAST(@index_id AS varchar(20)) + '

             ,@partition_number    = '        + CAST(@partition_number AS varchar(20)) + '

             ,@data_compression    = ''' + @compression_type + ''';';

*/

/* If the table is not partitioned */

IF (@partition_count = 1)

BEGIN;

    /* If the index is clustered and the table has a blob column it can only be rebuild offline */

    IF (@indexid = 1 AND @blob = 1)

    BEGIN;

        SELECT @command = 'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' +  @object_name + '] REBUILD WITH (DATA_COMPRESSION = ' + @compression_type +  ', ONLINE = OFF );';

    END

    /* All other cases; clustered index with no blob and noneclsutered indexes can be rebuild online */

    ELSE IF (@indexid = 1 AND @blob = 0) OR (@indexid > 1)

    BEGIN

            SELECT @command = 'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' +  @object_name + '] REBUILD WITH (DATA_COMPRESSION = ' + @compression_type +  ', ONLINE = ON );';

    END;

END;

/* If the table is partitioned it can only be rebuild offline */

ELSE IF (@partition_count > 1)

    BEGIN;

        SELECT @command = 

        'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' +  @object_name + '] REBUILD PARTITION = ' + CAST(@partition_number AS varchar(20)) + ' WITH (DATA_COMPRESSION = ' + @compression_type + ', ONLINE = OFF );';

    END;

PRINT @command;

/* update the table with additional info */

        UPDATE dbo.#estimate_data_compression_savings SET 

             [File_Group]                            = @File_Group

      ,[is_read_only]                        = @is_read_only

      ,[index_name]                            = @index_name

      ,[type]                                        = @type

      ,[data_compression_desc]    = @data_compression_desc

      ,[rows]                                        = @rows

      ,[reserved_page_count]        = @reserved_page_count

      ,[reserved_MB]                        = @reserved_MB 

      ,[command]                                = @command

      ,[partition_count]                = @partition_count

        WHERE Id = @Ident;

END TRY

BEGIN CATCH;

    SELECT @error = ERROR_MESSAGE();

        UPDATE dbo.#estimate_data_compression_savings SET 

             [File_Group]                            = @File_Group

      ,[is_read_only]                        = @is_read_only

      ,[index_name]                            = @index_name

      ,[type]                                        = @type

      ,[data_compression_desc]    = @data_compression_desc

      ,[rows]                                        = @rows

      ,[reserved_page_count]        = @reserved_page_count

      ,[reserved_MB]                        = @reserved_MB 

      ,[command]                                = @command

      ,[partition_count]                = @partition_count

      ,[error]                                    = @error

        WHERE Id = @Ident;

END CATCH;

        SELECT @Ident = NULL, @error = NULL;

    FETCH NEXT FROM CUR INTO @File_Group, @is_read_only, @schema_name, @object_name, @index_id, @index_name, @type, @partition_number, @partition_count, @data_compression_desc, @rows, @reserved_page_count, @reserved_MB, @blob, @indexid ;    

    END;

CLOSE CUR; DEALLOCATE CUR;

GO

/* Get the final results */

SELECT [Id]

      ,[File_Group]

      ,[is_read_only]

      ,[index_name]

      ,[type]

      ,[data_compression_desc]

      ,[rows]

      ,[reserved_page_count]

      ,[reserved_MB]

      ,[object_name]

      ,[schema_name]

      ,[index_id]

      ,[partition_number]

      ,[partition_count]

      ,[size_with_current_compression_setting (KB)] / 1024                    AS [size_with_current_compression_setting (MB)]

      ,[size_with_requested_compression_setting (KB)] / 1024                AS [size_with_requested_compression_setting (MB)]

      ,([size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) / 1024 AS diff_MB

      ,[sample_size_with_current_compression_setting (KB)] / 1024        AS [sample_size_with_current_compression_setting (MB)]

      ,[sample_size_with_requested_compression_setting (KB)] / 1024 AS [sample_size_with_requested_compression_setting (MB)]

      ,[command]

FROM dbo.#estimate_data_compression_savings WHERE 1=1

AND (

                [size_with_requested_compression_setting (KB)] < [size_with_current_compression_setting (KB)]

         OR [sample_size_with_requested_compression_setting (KB)] < [sample_size_with_current_compression_setting (KB)]

        )

--AND File_Group = ''

ORDER BY ( [size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) DESC;