Price Reductions On All My Online Courses!

I’m pleased to announce that prices have been permanently reduced on ALL my online SQL Server courses! Additionally, use the links below to save an additional 15% off the newly reduced prices!

 

Microsoft SQL Server 101 – This course is designed for new DBAs and involuntary DBAs or anyone who needs to learn the basics of SQL Server. The course can be found here at Udemy.com and includes demos and sample scripts. Use this link (or enter the coupon code 15BLOG) and get a 15% discount! This course is over 4 hours long and can be taken at your own pace.

Understanding Data Compression in SQL Server – This course delves into how SQL Server’s data compression feature works and how and when to implement it. The course can be found here at Udemy.com. It includes demos and sample scripts. Use this link (or enter the coupon code 15BLOG) and get a 15% discount! This course is approximately 1.5 hours long and can be taken at your own pace.

SQL Server Maintenance Plans – This course will teach you all about SQL Server’s maintenance plans and what they can (and can’t) do. The course can be found here at Udemy.com. It includes demos and sample scripts. Use this link (or enter the coupon code 15BLOG) and get a 15% discount! This course is almost 2 hours long and can be taken at your own pace.

Understanding Statistics in SQL Server – This course will teach you all about statistics in SQL Server – what they look like, how they are used, and how to maintain them. I also go over common problems caused by out of data statistics and how to find and fix them. The course can be found here at Udemy.com. It includes demos and sample scripts. Use this link (or enter the coupon code 15BLOG) and get a 15% discount! This course is almost 2 hours long and can be taken at your own pace.

Sample videos from my courses can be found on my YouTube channel here.

 

Share

Enabling Data Compression – January 2016 Update

Reader Dick H. posted a comment on my last version of this script stating that he got an error when this was run against tables containing sparse columns. Data compression does not support tables with sparse columns, so they should be excluded from this process. I’ve modified this script to correct this. I don’t have any tables with sparse columns in my environment, so thanks to Dick for pointing this out!

For instructions on using this script, look here.

 

CREATE PROC [dbo].[up_CompressDatabase]
      (
       @minCompression FLOAT = .25 /* e.g. .25 for minimum of 25% compression */
      ,@MaxRunTimeInMinutes INT = 60
      ,@ExecuteCompressCommand BIT = 0  /* 1 to execute command */
      ,@DBToScan sysname /* database to compress */
      )
AS
      SET NOCOUNT ON;
 
/*
    Original script by Paul Nielsen www.SQLServerBible.com March 13, 2008
    Modified by Shaun J. Stuart www.shaunjstuart.com February 27, 2013
 
  Sets compression for all objects and indexes in the database needing adjustment
  If estimated gain is equal to or greater than min compression parameter
    then enables row or page compression, whichever gives greater space savings
  If row and page have same gain then, enables row compression
  If estimated gain is less than min compression parameter, then compression is set to none
 
  - SJS 2/27/13
  - Added @MaxRunTimeInMinutes to limit run length (checked afer each command, so
        may run longer) Note: This timer only applies to the actual compression process.
        It does not limit the time it takes to perform a scan of all the tables / indexes
        and estimate compression savings.
  - Sorted compression cursor to compress smallest tables first
  - Added flag to execute compression command or not
  - Added sort in tempdb option (always)
  - Re-wrote slightly to persist initial scan results. Will only re-scan after all tables
        have been processed
 
    - SJS 7/17/13
    - Modified to only look at data that is not already compressed
    - Modified for that any items with null none_size, page_size, or row_size are no longer set as
        AlreadyProcessed and now have their sizes calculated (i.e. if process was cancelled before
        initial scan was completed)
    - Modified so that time limit now applies to compression estimate process as well as actual
        compression process
    - Only look at partitions that have at least one row
 
    - SJS 8/6/13
    - Changed name of dbEstimate table to dbCompressionEstimates for clarity
    - Added StatusText column to dbCompressionEstimates and added status messages
    - Modified to take database name as input parameter to allow use in utility database
        instead of the DB being compressed (Table dbCompressionEstimates, which stores sizes and
        compression estimates, is still created in the current database.)
    - Note: Compression estimates are only made for the database supplied as an input
        parameter. However, that database name is stored in the dbCompressionEstimates table and
        will be read from there and used when actually performing the compression. This allows you to
        create estimates only for multiple databases (using @ExecuteCompressCommand=0), then perform
        the compression across multiple databases later (with @ExecuteCompressCommand=1).
    - Removed all references to code that checked already compressed data since this routine now only
        looks at uncompressed data.
 
    - SJS 8/21/13
    - Put []s around table and index names in compression commands.
 
    - SJS 10/7/13
    - Added check to make sure table / index still exists before estimating or performing compression.
    - Fixed bug in cursor to determine compression estimates (left out db name in where clause)

    - SJS 1/31/14
    - Fixed bug where nonclustered indexes were always flagged as no longer present (Thanks to cp
		for pointing this out at
		http://shaunjstuart.com/archive/2013/10/enabling-data-compression-october-2013-update/comment-page-1/#comment)

    - SJS 4/14/14
    - Another fix to handle tables with spaces in their names
 
    - SJS 8/12/15
    - Put []s around table and schema names in estimate page and row compression commands
    - Added edition check

    - SJS 10/13/15
    - Changed PRINT statements to RAISERROR WITH NOWAITs to output info immediately during execution
    - Fixed bug where error was generated when max runtime exceeded

    - SJS 1/4/16
    - Excluded tables with sparse columns (they do not support compression)

*/

      DECLARE @EditionCheck VARCHAR(50);
      DECLARE @ProgressMessage VARCHAR(2044);

      SELECT    @EditionCheck = CAST(SERVERPROPERTY('Edition') AS VARCHAR(50));
      IF LEFT(@EditionCheck, 10) <> 'Enterprise'
         AND LEFT(@EditionCheck, 9) <> 'Developer'
         BEGIN
               PRINT 'Database compression is only supported on Enterprise and Developer editions '
                     + 'of SQL Server. This server is running '
                     + @EditionCheck + '.';
               RETURN;
         END;



      IF ISNULL(@DBToScan, '') NOT IN (SELECT
                                            [name]
                                       FROM sys.databases)
         BEGIN
               SELECT   'Database ' + ISNULL(@DBToScan,
                                            'NULL')
                        + ' not found on server.';
               RETURN;
         END;
 
      DECLARE @CompressedCount INT;
      SET @CompressedCount = 0;
 
      DECLARE @SQL NVARCHAR(MAX);
      DECLARE @ParmDefinition NVARCHAR(100);
      DECLARE @TestResult NVARCHAR(20);
      DECLARE @CheckString NVARCHAR(1000);
 
      DECLARE @StartTime DATETIME2;
      SET @StartTime = CURRENT_TIMESTAMP;
 
      DECLARE @CurrentDatabase sysname;
      SET @CurrentDatabase = DB_NAME();
 
      IF OBJECT_ID('tempdb..##ObjEst', 'U') IS NOT NULL
         DROP TABLE ##ObjEst;
 
      CREATE TABLE ##ObjEst
             (
              PK INT IDENTITY
                     NOT NULL
                     PRIMARY KEY
             ,object_name VARCHAR(250)
             ,schema_name VARCHAR(250)
             ,index_id INT
             ,partition_number INT
             ,size_with_current_compression_setting BIGINT
             ,size_with_requested_compression_setting BIGINT
             ,sample_size_with_current_compression_setting
                BIGINT
             ,sample_size_with_requested_compresison_setting
                BIGINT
             );
 
      IF NOT EXISTS ( SELECT    1
                      FROM      sys.objects
                      WHERE     object_id = OBJECT_ID(N'[dbo].[dbCompressionEstimates]')
                                AND type IN (N'U') )
         BEGIN
               CREATE TABLE dbo.dbCompressionEstimates
                      (
                       PK INT IDENTITY
                              NOT NULL
                              PRIMARY KEY
                      ,DatabaseName sysname
                      ,schema_name VARCHAR(250)
                      ,object_name VARCHAR(250)
                      ,index_id INT
                      ,ixName VARCHAR(255)
                      ,ixType VARCHAR(50)
                      ,partition_number INT
                      ,data_compression_desc VARCHAR(50)
                      ,None_Size INT
                      ,Row_Size INT
                      ,Page_Size INT
                      ,AlreadyProcessed BIT
                      ,StatusText VARCHAR(75)
                      );
         END;
 
  /*
 If all objects have been processed, rescan and start fresh. Useful for catching
  added objects since last scan. But beware - this is I/O intensive and can take a while.
*/
 
      IF NOT EXISTS ( SELECT    1
                      FROM      dbo.dbCompressionEstimates
                      WHERE     AlreadyProcessed = 0
                                AND DatabaseName = @DBToScan )
         BEGIN
               SET @ProgressMessage = 'No unprocessed items found. Starting new scan.';
               RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
               DELETE   FROM dbo.dbCompressionEstimates
               WHERE    DatabaseName = @DBToScan;
 
               SET @SQL = 'USE [' + @DBToScan + '];
                        INSERT   INTO [' + @CurrentDatabase
                   + '].dbo.dbCompressionEstimates
                                (DatabaseName
                                ,schema_name
                                ,object_name
                                ,index_id
                                ,ixName
                                ,ixType
                                ,partition_number
                                ,data_compression_desc
                                ,AlreadyProcessed
                                ,StatusText)
                        SELECT  ''' + @DBToScan
                   + '''
                                ,S.name
                               ,O.name
                               ,I.index_id
                               ,I.name
                               ,I.type_desc
                               ,P.partition_number
                               ,P.data_compression_desc
                               ,0 AS AlreadyProcessed
                               ,''Initial load'' AS StatusText
                        FROM    [' + @DBToScan
                   + '].sys.schemas AS S
                                JOIN [' + @DBToScan
                   + '].sys.objects AS O ON S.schema_id = O.schema_id
                                JOIN [' + @DBToScan
                   + '].sys.indexes AS I ON O.object_id = I.object_id
                                JOIN [' + @DBToScan
                   + '].sys.partitions AS P ON I.object_id = P.object_id
                                                            AND I.index_id = P.index_id
                        WHERE   O.TYPE = ''U''
                                AND P.data_compression_desc = ''NONE''
								AND O.object_id NOT IN (SELECT object_id 
									FROM    [' + @DBToScan + '].sys.columns	WHERE is_sparse = 1)
                                AND P.rows > 0;';     -- only look at objects with data
               EXEC (@SQL);
         END;
            -- Determine Compression Estimates
      SET @ProgressMessage = 'Beginning compression estimate phase.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
      DECLARE @PK INT
             ,@DatabaseName sysname
             ,@Schema VARCHAR(150)
             ,@object VARCHAR(150)
             ,@DAD VARCHAR(25)
             ,@partNO VARCHAR(3)
             ,@indexID VARCHAR(3)
             ,@ixName VARCHAR(250)
             ,@ixType VARCHAR(50)
             ,@Recommended_Compression VARCHAR(10);
 
      DECLARE cCompress CURSOR FAST_FORWARD
      FOR
              SELECT    schema_name
                       ,object_name
                       ,index_id
                       ,partition_number
                       ,data_compression_desc
                       ,ixName
              FROM      dbo.dbCompressionEstimates
              WHERE     (None_Size IS NULL
                         OR Row_Size IS NULL
                         OR Page_Size IS NULL)
                        AND DatabaseName = @DBToScan;
 
      OPEN cCompress;
 
      FETCH cCompress INTO @Schema, @object, @indexID,
            @partNO, @DAD, @ixName;
 
      WHILE @@Fetch_Status = 0
            BEGIN
                            /* evaluate objects with no compression */
                  IF DATEDIFF(mi, @StartTime,
                              CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
                     BEGIN
                            /* First, make sure the table / index still exists (in case this
                                process is run over multiple days */
 
                           IF @indexID = 0
                              BEGIN /* heaps */
                                    SET @CheckString = 'IF object_ID('''
                                        + @DBToScan + '.'
                                        + @Schema + '.'
                                        + @object
                                        + ''') IS NULL
                                    BEGIN
                                        SELECT @TestResultOUT = ''Does Not Exist''
                                    END
                                    ELSE
                                    BEGIN
                                        SELECT @TestResultOUT = ''Exists''
                                    END';
                              END;
                           ELSE
                              BEGIN /* indexes */
                                    SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
                                        + @DBToScan
                                        + '].[sys].[indexes] WHERE [name] ='''
                                        + @ixName
                                        + ''' AND OBJECT_ID('''
                                        + '[' + @DBToScan
                                        + ']' + '.['
                                        + @Schema + '].['
                                        + @object + ']'''
                                        + ') = [object_id])
                                    BEGIN
                                        SELECT @TestResultOUT = ''Does Not Exist''
                                    END
                                    ELSE
                                    BEGIN
                                        SELECT @TestResultOUT = ''Exists''
                                    END';
                              END;

                           SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
                           
                           EXECUTE sp_executesql
                            @CheckString
                           ,@ParmDefinition
                           ,@TestResultOUT = @TestResult OUTPUT;
                           IF @TestResult = 'Exists'
                              BEGIN
 
                                    IF @DAD = 'none'
                                       BEGIN
                                    /* estimate Page compression */
                                            SET @SQL = 'USE ['
                                            + @DBToScan
                                            + '];
                                        INSERT  ##ObjEst
                                                (object_name
                                                ,schema_name
                                                ,index_id
                                                ,partition_number
                                                ,size_with_current_compression_setting
                                                ,size_with_requested_compression_setting
                                                ,sample_size_with_current_compression_setting
                                                ,sample_size_with_requested_compresison_setting)
                                                EXEC sp_estimate_data_compression_savings
                                                    @Schema_name = ['
                                            + @Schema
                                            + ']
                                                   ,@object_name = ['
                                            + @object
                                            + ']
                                                   ,@index_id = '
                                            + @indexID
                                            + '
                                                   ,@partition_number = '
                                            + @partNO
                                            + '
                                                   ,@data_compression = ''PAGE'';';

                                            SET @ProgressMessage = 'Estimating PAGE compression for '
                                            + '['
                                            + @DBToScan
                                            + '].['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '], '
                                            + 'index id '
                                            + CAST(@indexID AS VARCHAR(3))
                                            + ', '
                                            + 'partition number '
                                            + CAST(@partNO AS VARCHAR(3));
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                            EXEC (@SQL);
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            None_Size = O.size_with_current_compression_setting
                                           ,Page_Size = O.size_with_requested_compression_setting
                                           ,StatusText = 'Compression estimate 50% complete'
                                            FROM
                                            dbo.dbCompressionEstimates D
                                            JOIN ##ObjEst O
                                            ON D.schema_name = O.schema_name
                                            AND D.object_name = O.object_name
                                            AND D.index_id = O.index_id
                                            AND D.partition_number = O.partition_number;
 
                                            DELETE
                                            ##ObjEst;
 
                                    -- estimate Row compression
                                            SET @SQL = 'USE ['
                                            + @DBToScan
                                            + '];
                                        INSERT  ##ObjEst
                                                (object_name
                                                ,schema_name
                                                ,index_id
                                                ,partition_number
                                                ,size_with_current_compression_setting
                                                ,size_with_requested_compression_setting
                                                ,sample_size_with_current_compression_setting
                                                ,sample_size_with_requested_compresison_setting)
                                                EXEC sp_estimate_data_compression_savings
                                                    @Schema_name = ['
                                            + @Schema
                                            + ']
                                                   ,@object_name = ['
                                            + @object
                                            + ']
                                                   ,@index_id = '
                                            + @indexID
                                            + '
                                                   ,@partition_number = '
                                            + @partNO
                                            + '
                                                   ,@data_compression = ''ROW'';';

                                            SET @ProgressMessage = 'Estimating ROW compression for '
                                            + '['
                                            + @DBToScan
                                            + '].['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '], '
                                            + 'index id '
                                            + CAST(@indexID AS VARCHAR(3))
                                            + ', '
                                            + 'partition number '
                                            + CAST(@partNO AS VARCHAR(3));
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;


                                            EXEC (@SQL);
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            Row_Size = O.size_with_requested_compression_setting
                                           ,StatusText = 'Compression estimate 100% complete'
                                            FROM
                                            dbo.dbCompressionEstimates D
                                            JOIN ##ObjEst O
                                            ON D.schema_name = O.schema_name
                                            AND D.object_name = O.object_name
                                            AND D.index_id = O.index_id
                                            AND D.partition_number = O.partition_number;
 
                                            DELETE
                                            ##ObjEst;
                                       END; /* end evaluating objects with no compression */
                              END;
                           ELSE /* table or index no longer exists */
                              BEGIN
                                    SET @ProgressMessage = 'Encountered table or index that no longer exists at compression estimate stage. Skipping.';
                                    RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                    UPDATE  dbo.dbCompressionEstimates
                                    SET     AlreadyProcessed = 1
                                           ,StatusText = 'Object no longer exists at compression estimate stage'
                                    WHERE   schema_name = @Schema
                                            AND object_name = @object
                                            AND index_id = @indexID
                                            AND partition_number = @partNO
                                            AND data_compression_desc = @DAD
                                            AND DatabaseName = @DBToScan;
                              END;
 
                           FETCH NEXT FROM cCompress INTO @Schema,
                                 @object, @indexID, @partNO,
                                 @DAD, @ixName;
                     END; -- end time check block
                  ELSE
                     BEGIN
                           SET @ProgressMessage = 'Max runtime reached. No compression performed. Compression estimate still processing. Exiting...';
                           RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                           CLOSE cCompress;
                           DEALLOCATE cCompress;
                           DROP TABLE ##ObjEst;
                           RETURN;
                     END;
            END; -- end while loop
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      SET @ProgressMessage = 'Compression estimate scan complete.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
         --END
  /* End evaluating compression savings. Now do the actual compressing. */
 
      SET @ProgressMessage = 'Beginning compression.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
 
  /* Do not process objects that do not meet our compression criteria */
 
      SET @ProgressMessage = 'Skipping empty objects and objects that do not meet '
          + 'the minimum compression threshold.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'Best compression method less than minCompression threshold'
      WHERE     (1 - (CAST(Row_Size AS FLOAT) / None_Size)) < @minCompression
                AND (Row_Size <= Page_Size)
                AND None_Size > 0
                AND AlreadyProcessed = 0;
 
      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'Best compression method less than minCompression threshold'
      WHERE     (1 - (CAST(Page_Size AS FLOAT) / None_Size)) < @minCompression
                AND (Page_Size <= Row_Size)
                AND None_Size > 0
                AND AlreadyProcessed = 0;
 
  /* Do not set compression on empty objects */
 
      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'No data in table to compress'
      WHERE     None_Size = 0
                AND AlreadyProcessed = 0;
 
 -- set the compression
      DECLARE cCompress CURSOR FAST_FORWARD
      FOR
              SELECT    DatabaseName
                       ,schema_name
                       ,object_name
                       ,partition_number
                       ,ixName
                       ,ixType
                       ,CASE WHEN (1
                                   - (CAST(Row_Size AS FLOAT)
                                      / None_Size)) >= @minCompression
                                  AND (Row_Size <= Page_Size)
                             THEN 'Row'
                             WHEN (1
                                   - (CAST(Page_Size AS FLOAT)
                                      / None_Size)) >= @minCompression
                                  AND (Page_Size <= Row_Size)
                             THEN 'Page'
                             ELSE 'None'
                        END AS Recommended_Compression
                       ,PK
              FROM      dbo.dbCompressionEstimates
              WHERE     None_Size <> 0
                        AND (CASE WHEN (1
                                        - (CAST(Row_Size AS FLOAT)
                                           / None_Size)) >= @minCompression
                                       AND (Row_Size <= Page_Size)
                                  THEN 'Row'
                                  WHEN (1
                                        - (CAST(Page_Size AS FLOAT)
                                           / None_Size)) >= @minCompression
                                       AND (Page_Size <= Row_Size)
                                  THEN 'Page'
                                  ELSE 'None'
                             END <> data_compression_desc)
                        AND AlreadyProcessed = 0
              ORDER BY  None_Size ASC;      /* start with smallest tables first */
 
      OPEN cCompress;
 
      FETCH cCompress INTO @DatabaseName, @Schema, @object,
            @partNO, @ixName, @ixType,
            @Recommended_Compression, @PK;  -- prime the cursor;
 
      WHILE @@Fetch_Status = 0
            BEGIN
 
                  IF @ixType = 'Clustered'
                     OR @ixType = 'heap'
                     BEGIN
                           SET @SQL = 'USE ['
                               + @DatabaseName + '];
                                ALTER TABLE [' + @Schema
                               + '].[' + @object
                               + '] Rebuild with (data_compression = '
                               + @Recommended_Compression
                               + ', SORT_IN_TEMPDB=ON)';
 
                           SET @CheckString = 'IF object_ID('''
                               + @DatabaseName + '.'
                               + @Schema + '.' + @object
                               + ''') IS NULL
                                BEGIN
                                    SELECT @TestResultOUT = ''Does Not Exist''
                                END
                                ELSE
                                BEGIN
                                    SELECT @TestResultOUT = ''Exists''
                                END';
                     END;
 
                  ELSE  /* non-clustered index */
                     BEGIN
                           SET @SQL = 'USE ['
                               + @DatabaseName + '];
                                ALTER INDEX [' + @ixName
                               + '] on [' + @Schema + '].['
                               + @object
                               + '] Rebuild with (data_compression = '
                               + @Recommended_Compression
                               + ',SORT_IN_TEMPDB=ON)';
 
                           SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
                               + @DBToScan
                               + '].[sys].[indexes] WHERE [name] ='''
                               + @ixName
                               + ''' AND OBJECT_ID(''' + '['
                               + @DBToScan + ']' + '.['
                               + @Schema + '].[' + @object
                               + ']'''
                               + ') = [object_id])
                            BEGIN
                                SELECT @TestResultOUT = ''Does Not Exist''
                            END
                            ELSE
                            BEGIN
                                SELECT @TestResultOUT = ''Exists''
                            END';
                     END;

                  IF DATEDIFF(mi, @StartTime,
                              CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
                     BEGIN
                           IF @ExecuteCompressCommand = 1
                              BEGIN
 
                            /* verify that table / index still exists before doing anything */
 
                                    SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
                                    EXECUTE sp_executesql
                                        @CheckString
                                       ,@ParmDefinition
                                       ,@TestResultOUT = @TestResult OUTPUT;
                                    IF @TestResult = 'Exists'
                                       BEGIN
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            StatusText = 'Compressing data...'
                                            WHERE
                                            PK = @PK;
 
                                            SET @ProgressMessage = 'Compressing table/index: '
                                            + '[' + @ixName
                                            + '] on ['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '] in database '
                                            + @DatabaseName;

                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                            EXEC sp_executesql
                                            @SQL;
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            AlreadyProcessed = 1
                                           ,StatusText = 'Compression complete'
                                            WHERE
                                            PK = @PK;
 
                                            SET @CompressedCount = @CompressedCount
                                            + 1;
                                       END;
                                    ELSE
                                       BEGIN
                                            SET @ProgressMessage = 'Encountered table or index that no longer exists at compression stage. Skipping.';
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;


                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            AlreadyProcessed = 1
                                           ,StatusText = 'Object no longer exists at compression stage'
                                            WHERE
                                            PK = @PK;
                                       END;
                              END;
                           ELSE
                              BEGIN

                                    SET @ProgressMessage = 'Command execution not enabled. Command is:'
                                        + @SQL;
                                    RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                              END;
                     END;
                  ELSE
                     BEGIN
                           SET @ProgressMessage = 'Max runtime reached. Some compression performed. Exiting...';
                           RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                           CLOSE cCompress;
                           DEALLOCATE cCompress;
                           DROP TABLE ##ObjEst;
                           RETURN;
                     END;
 
                  FETCH cCompress INTO @DatabaseName,
                        @Schema, @object, @partNO, @ixName,
                        @ixType, @Recommended_Compression,
                        @PK;
            END;
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      SET @ProgressMessage = 'Objects compressed: '
          + CONVERT(VARCHAR(10), @CompressedCount);
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

      DROP TABLE ##ObjEst;
      RETURN;

Share

Monitoring For Endless Index Defragmenting – November 2015

This is just a bug fix from my last post of this code. I ran into a case where you would not get notified if the only indexes in a database that were being repeatedly defragged had fill factors of 50 or less. I would think this would be a pretty rare case, but it happened to me, so maybe it isn’t so rare after all.

As usual, this script should be run in sync with the frequency of how often you clear out the CommandLog table or you could end up with unnecessarily small recommendations for fill factors.

CREATE PROCEDURE [dbo].[up_IndexDefragFrequencyCheck]
      (
       @DatabaseToInvestigate VARCHAR(100) = NULL
      ,@DatabasesToSkip VARCHAR(MAX) = NULL
      ,@CommandLogDatabase VARCHAR(100) = NULL
      ,@CountThreshold TINYINT = 4
      ,@OverRebuildThresholdNewFF TINYINT = 70
      ,@OverReorgThresholdNewFF TINYINT = 80
      ,@MailProfileName sysname
      ,@MailRecipients VARCHAR(MAX) = NULL
	  )
AS /*
	
	This procedure scans the CommandLog table of Ola Hallengren's index defrag script
		(http://ola.hallengren.com/) and identifies indexes that are being rebuilt or
		reorganized regularly. The original code comes courtesy of Tim Ford, who blogged
		this at http://thesqlagentman.com/2013/10/whats-the-frequency-kenneth/. This routine
		expands his work. The procedure is meant to be run on a regular basis and will send 
		an email when it finds indexes being defragged more than a specified number of times.
		It will also suggest rebuilding the index with a new fill factor to reduce the number
		of rebuilds or reorgs. The new fill factor recommended is one you specify, if the current
		fill factor of the index is 0 or 100%, otherwise it is 10% less than the current
		fill factor. It will not recommend anything less than 50%.

		IMPORTANT!!! This routine looks at the log table and the _current_ fill factor setting
		of the indexes. Therefore, in order to get appropriate fill factor recommendations,
		this routine should NOT be run more often than the log table is cleared. FOR EXAMPLE,
		IF YOU KEEP 30 DAYS WORTH OF LOG RECORDS IN YOUR TABLE, THIS ROUTINE SHOULD NOT BE RUN
		MORE OFTEN THAN EVERY 30 DAYS. This is because if you change the fill factor of an index
		it may result in stopping rebuilds, but the log table will still have the records of the
		prior rebuilds, which may trigger another recommendation to reduce the fill factor, which
		would then be 10% less than what you just changed it to.

		Do not blindly run the commands in the email notification! They are meant to make it easier
		for a DBA to change the fill factor, but they should be evaluated first to ensure they
		are appropriate for your situation.
		
		Input parameters:

		@DatabaseToInvestigate - The database to scan for excessive defrags. A value of *
									will scan all databases with log entries in the
									CommandLog table.
		@CommandLogDatabase - Database holding the CommandLog table of Ola's routine
		@CountThreshold - indexes being defragged equal to or greater this number of times
							are flagged as being excessively defragged
		@OverRebuildThresholdNewFF - Initial starting fill factor suggestion for indexes that
							are being rebuilt more than @CountThreshold times. Only used when
							current fill factor is 0 or 100
		@OverReorgThresholdNewFF - Initial starting fill factor suggestion for indexes that
							are being reorganized more than @CountThreshold times. Only used when
							current fill factor is 0 or 100
		@MailProfileName - name of mail profile the routine can use to send email
		@MailRecipients - list of email addresses the notification will be sent to. Must be of
							the form xxx@xxx.xxx  Multiple addresses should be separated by a ;


		Shaun J. Stuart
		shaunjstuart.com

		v1.0 - 10/8/13
		v1.1 - 10/13/13 - SJS - Added support for case where current fill factor between 100 and the
								min thresholds passed in.
		v1.2 - 1/16/14  - SJS - Added check for SQL 2005, which does not support the PARTITION = ALL option
							  - Added check for Enterprise edition to set ONLINE = ON or OFF
							  - Added square brackets around DB name where they were not before to
									handle db names with periods in them
							  - Added database name to email subject line
		v1.3 - 9/9/15  - SJS  - Added comment to command output showing reorg/rebuild count and
									current fill factor to explain why command was generated
							  - Corrected bug where recommendation was not decrementing current
							    fill factor correctly
		v1.4 - 9/16/15  - SJS - Added check for missing database to scan and missing CommandLog db
							  - Now a value of ALL_DATABASES for @DatabaseToInvestigate will scan all
									databases with entries in the CommandLog table
		v1.5 - 9/23/15  - SJS - Added output to indicate if SQL edition supports online index rebuilds
							  - Now identifies Developer edition as supporting online index rebuilds
		v1.6 - 9/23/15  - SJS - Added check for valid mail profile name. Note - this does not check if the executing
									user has permission to use the profile. It only checks that it exists.
							  - Added new input parameter @DatabasesToSkip, which can contain a CSV list
									of databases to skip
							  - Modified input parameter @DatabaseToInvestigate to support CSV list of 
									of databases to investigate
		v1.7 - 11/16/15	- SJS -	Fixed bug where notices of indexes @ 50 or less fill factor were
									note being reported if they were the only indexes in the db that
									were being repeatedly defragged.


*/

      SET NOCOUNT ON;


      DECLARE @SQLstr VARCHAR(2000);
      DECLARE @EmailBody VARCHAR(MAX);
      DECLARE @CRLF CHAR(2);
      DECLARE @EmailSubject VARCHAR(200);
      DECLARE @SQLVersion TINYINT;
      DECLARE @SupportsOnlineRebuilds BIT;
      DECLARE @delimiter CHAR(1) ;
      DECLARE @Items TABLE (Item NVARCHAR(4000));
      DECLARE @LenList INT;
      DECLARE @ld INT;

      SET @CRLF = CHAR(13) + CHAR(10);
	  SET @delimiter = ',';
      SET @SQLVersion = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR(15)),
                             CHARINDEX('.',
                                       CAST(SERVERPROPERTY('productversion') AS VARCHAR(15)))
                             - 1);
	/* SQLVersion: 9 = SQL 2005, 10 = SQL 2008 or 2008 R2, 11 = 2012 */

      IF LEFT(CAST(SERVERPROPERTY('edition') AS VARCHAR(50)),
              10) = 'Enterprise'
         OR LEFT(CAST(SERVERPROPERTY('edition') AS VARCHAR(50)),
                 10) = 'Datacenter'
         OR LEFT(CAST(SERVERPROPERTY('edition') AS VARCHAR(50)),
                 9) = 'Developer'
         BEGIN
               SET @SupportsOnlineRebuilds = 1;
               SELECT   'SQL Server edition does support online index rebuilds.';
         END;
      ELSE
         BEGIN
               SET @SupportsOnlineRebuilds = 0;
               SELECT   'SQL Server edition does not support online index rebuilds.';
         END;

/* Data validation */

      IF @DatabaseToInvestigate IS NULL
         OR @DatabaseToInvestigate = ''
         BEGIN
               SELECT   'A database to investigate must be specified.';
               RETURN;
         END;

      IF NOT EXISTS ( SELECT    1
                      FROM      sys.databases
                      WHERE     name = @DatabaseToInvestigate
                                AND state = 0 )
         AND @DatabaseToInvestigate <> 'ALL_DATABASES'
         AND @DatabaseToInvestigate NOT LIKE '%,%'
         BEGIN
               SELECT   'The database '
                        + @DatabaseToInvestigate
                        + ' is not accessible or '
                        + 'does not exist.';
               RETURN;
         END;

      IF @CommandLogDatabase IS NULL
         OR @CommandLogDatabase = ''
         BEGIN
               SELECT   'A database holding the CommandLog table must be specified.';
               RETURN;
         END;

      IF NOT EXISTS ( SELECT    1
                      FROM      sys.databases
                      WHERE     name = @CommandLogDatabase
                                AND state = 0 )
         BEGIN
               SELECT   'The specified commandLog database, '
                        + @CommandLogDatabase + ', is not '
                        + 'accessible or does not exist.';
               RETURN;
         END;


      IF @MailRecipients IS NULL
         OR @MailRecipients = ''
         BEGIN
               SELECT   'At least one email recipient must be specified.';
               RETURN;
         END;

      IF @MailRecipients NOT LIKE '%@%.%'
         BEGIN
               SELECT   'Email recipient not valid address format.';
               RETURN;
         END;

      IF NOT EXISTS ( SELECT    1
                      FROM      msdb.dbo.sysmail_profile sp
                      WHERE     name = @MailProfileName )
         BEGIN
               SELECT   'The specified mail profile, '
                        + @MailProfileName
                        + ', does not exist.';
               RETURN;
         END;

--============================================================================
-- Build list of dbs to check
--============================================================================
      IF EXISTS ( SELECT    name
                  FROM      tempdb.sys.objects
                  WHERE     name = '##DatabasesToInvestigate' )
         BEGIN
               DROP TABLE ##DatabasesToInvestigate;
         END;

      CREATE TABLE ##DatabasesToInvestigate (the_database
                                            sysname);

      IF @DatabaseToInvestigate = 'ALL_DATABASES'
         BEGIN
               SET @SQLstr = 'INSERT INTO ##DatabasesToInvestigate
				(the_database)
			SELECT DISTINCT	DatabaseName
			FROM	[' + @CommandLogDatabase
                   + '].dbo.CommandLog
			ORDER BY DatabaseName;';
			
               EXEC(@SQLstr);
         END;
      ELSE
         BEGIN

/* insert dbs to check */

               IF @DatabaseToInvestigate IS NOT NULL
                  BEGIN

                        SET @LenList = LEN(@DatabaseToInvestigate)
                            + 1;
                        SET @ld = LEN(@delimiter);
 
                        WITH    a AS (SELECT
                                            [start] = 1
                                           ,[end] = COALESCE(NULLIF(CHARINDEX(@delimiter,
                                            @DatabaseToInvestigate,
                                            1), 0), @LenList)
                                           ,[value] = SUBSTRING(@DatabaseToInvestigate,
                                            1,
                                            COALESCE(NULLIF(CHARINDEX(@delimiter,
                                            @DatabaseToInvestigate,
                                            1), 0), @LenList)
                                            - 1)
                                      UNION ALL
                                      SELECT
                                            [start] = CONVERT(INT, [end])
                                            + @ld
                                           ,[end] = COALESCE(NULLIF(CHARINDEX(@delimiter,
                                            @DatabaseToInvestigate,
                                            [end] + @ld), 0),
                                            @LenList)
                                           ,[value] = SUBSTRING(@DatabaseToInvestigate,
                                            [end] + @ld,
                                            COALESCE(NULLIF(CHARINDEX(@delimiter,
                                            @DatabaseToInvestigate,
                                            [end] + @ld), 0),
                                            @LenList)
                                            - [end] - @ld)
                                      FROM  a
                                      WHERE [end] < @LenList
                                     )
                             INSERT @Items
                                    SELECT  [value]
                                    FROM    a
                                    WHERE   LEN([value]) > 0
                             OPTION (MAXRECURSION 0);

                        SELECT  Item
                                + ' database name extracted'
                        FROM    @Items;

                        INSERT  INTO ##DatabasesToInvestigate
                                (the_database)
                                SELECT  Item
                                FROM    @Items;

                  END;

         END;

		/* Remove inaccessible databases */

      SELECT    the_database
                + ' database skipped (not accessible)'
      FROM      ##DatabasesToInvestigate
      WHERE     the_database IN (SELECT name
                                 FROM   sys.databases
                                 WHERE  state <> 0);

      DELETE    FROM ##DatabasesToInvestigate
      WHERE     the_database IN (SELECT name
                                 FROM   sys.databases
                                 WHERE  state <> 0);

		/* Remove databases no longer present */

      SELECT    the_database
                + ' database skipped (no longer present)'
      FROM      ##DatabasesToInvestigate
      WHERE     the_database NOT IN (SELECT name
                                     FROM   sys.databases);

      DELETE    FROM ##DatabasesToInvestigate
      WHERE     the_database NOT IN (SELECT name
                                     FROM   sys.databases);

		/* Remove databases requested to be skipped */

      IF @DatabasesToSkip IS NOT NULL
         BEGIN
               DELETE   FROM @Items;
               SET @LenList = LEN(@DatabasesToSkip) + 1;
               SET @ld = LEN(@delimiter);
 
               WITH a AS (SELECT    [start] = 1
                                   ,[end] = COALESCE(NULLIF(CHARINDEX(@delimiter,
                                            @DatabasesToSkip,
                                            1), 0), @LenList)
                                   ,[value] = SUBSTRING(@DatabasesToSkip,
                                            1,
                                            COALESCE(NULLIF(CHARINDEX(@delimiter,
                                            @DatabasesToSkip,
                                            1), 0), @LenList)
                                            - 1)
                          UNION ALL
                          SELECT    [start] = CONVERT(INT, [end])
                                    + @ld
                                   ,[end] = COALESCE(NULLIF(CHARINDEX(@delimiter,
                                            @DatabasesToSkip,
                                            [end] + @ld), 0),
                                            @LenList)
                                   ,[value] = SUBSTRING(@DatabasesToSkip,
                                            [end] + @ld,
                                            COALESCE(NULLIF(CHARINDEX(@delimiter,
                                            @DatabasesToSkip,
                                            [end] + @ld), 0),
                                            @LenList)
                                            - [end] - @ld)
                          FROM      a
                          WHERE     [end] < @LenList
                         )
                    INSERT  @Items
                            SELECT  [value]
                            FROM    a
                            WHERE   LEN([value]) > 0
                    OPTION  (MAXRECURSION 0);

               SELECT   Item
                        + ' database skipped (per input parameter)'
               FROM     @Items;
 
               DELETE   FROM ##DatabasesToInvestigate
               WHERE    the_database IN (SELECT
                                            Item
                                         FROM
                                            @Items);


         END;


/* Now loop through all databases and do the work
   
   Note: re-using @DatabaseToInvestigate variable. This is poor coding
	but eliminates me having to re-write everything following this to
	add in looping functionality.
*/

      WHILE @DatabaseToInvestigate IS NOT NULL
            BEGIN

                  SELECT TOP 1
                            @DatabaseToInvestigate = the_database
                  FROM      ##DatabasesToInvestigate;

                  IF @@rowcount = 0
                     BREAK; /* no more dbs */
--============================================================================
-- INDEX REBUILD COUNT 
--============================================================================

                  IF EXISTS ( SELECT    name
                              FROM      tempdb.sys.objects
                              WHERE     name = '##Recommendations' )
                     BEGIN
                           DROP TABLE ##Recommendations;
                     END;

                  IF EXISTS ( SELECT    name
                              FROM      tempdb.sys.objects
                              WHERE     name = '##Index_History' )
                     BEGIN
                           DROP TABLE ##Index_History;
                     END;
 
                  IF EXISTS ( SELECT    name
                              FROM      tempdb.sys.objects
                              WHERE     name = '##Indexes' )
                     BEGIN
                           DROP TABLE ##Indexes;
                     END;

                  CREATE TABLE ##Recommendations
                         (
                          CommandLine VARCHAR(MAX)
                         );



                  CREATE TABLE ##Index_History
                         (
                          the_database sysname
                         ,the_schema sysname
                         ,the_object sysname
                         ,the_index sysname
                         ,index_type VARCHAR(13)
                         ,fill_factor TINYINT
                         ,rebuild_count INT NULL
                         ,reorg_count INT NULL
                         );
 
                  CREATE TABLE ##Indexes
                         (
                          the_schema sysname
                         ,the_object sysname
                         ,the_index sysname
                         ,fill_factor TINYINT
                         );
 
                  SET @SQLstr = 'INSERT  INTO ##Indexes
        (the_schema
        ,the_object
        ,the_index
        ,fill_factor)
        SELECT  OBJECT_SCHEMA_NAME(SO.object_id, DB_ID('''
                      + @DatabaseToInvestigate
                      + ''')) AS the_schema
               ,SO.name AS the_object
               ,SI.name AS the_index
               ,SI.fill_factor
        FROM    [' + @DatabaseToInvestigate
                      + '].sys.objects SO
                INNER JOIN [' + @DatabaseToInvestigate
                      + '].sys.indexes SI ON SO.object_id = SI.object_id
        WHERE   SI.index_id > 0;';
		--PRINT @SQLstr
                  EXEC (@SQLstr);
 
                  SET @SQLstr = 'INSERT  INTO ##Index_History
        (the_database
        ,the_schema
        ,the_object
        ,the_index
        ,index_type
        ,fill_factor
        ,rebuild_count
        ,reorg_count)
        SELECT  C.DatabaseName
               ,C.SchemaName
               ,C.ObjectName
               ,C.IndexName
               ,CASE C.IndexType
                  WHEN 1 THEN ''Clustered''
                  ELSE ''Non-Clustered''
                END AS IndexType
               ,IX.fill_factor
               ,COUNT(C.ID) AS rebuild_count
               ,0 AS reorg_count
        FROM    [' + @CommandLogDatabase
                      + '].dbo.CommandLog C
                LEFT JOIN ##Indexes IX ON C.SchemaName = IX.the_schema
                                          AND C.ObjectName = IX.the_object
                                          AND C.IndexName = IX.the_index
        WHERE   C.CommandType = ''ALTER_INDEX''
                AND C.ObjectType = ''U''
                AND C.Command LIKE ''%REBUILD%''
                AND C.DatabaseName = '''
                      + @DatabaseToInvestigate + '''
        GROUP BY C.DatabaseName
               ,C.SchemaName
               ,C.ObjectName
               ,C.IndexName
               ,IndexType
               ,IX.fill_factor;';
		--PRINT @SQLstr
                  EXEC (@SQLstr); 
--============================================================================
-- INDEX REORGANIZE (ONLY) COUNT 
--============================================================================
                  SET @SQLstr = 'INSERT  INTO ##Index_History
        (the_database
        ,the_schema
        ,the_object
        ,the_index
        ,index_type
        ,fill_factor
        ,rebuild_count
        ,reorg_count)
        SELECT  C.DatabaseName
               ,C.SchemaName
               ,C.ObjectName
               ,C.IndexName
               ,CASE C.IndexType
                  WHEN 1 THEN ''Clustered''
                  ELSE ''Non-Clustered''
                END AS IndexType
               ,IX.fill_factor
               ,0 AS rebuild_count
               ,COUNT(C.ID) AS reorg__count
        FROM    [' + @CommandLogDatabase
                      + '].dbo.CommandLog C
                LEFT JOIN ##Indexes IX ON C.SchemaName = IX.the_schema
                                          AND C.ObjectName = IX.the_object
                                          AND C.IndexName = IX.the_index
                LEFT JOIN ##Index_History IH ON C.DatabaseName = IH.the_database
                                                AND C.SchemaName = IH.the_schema
                                                AND C.ObjectName = IH.the_object
                                                AND C.IndexName = IH.the_index
        WHERE   C.CommandType = ''ALTER_INDEX''
                AND C.ObjectType = ''U''
                AND C.Command LIKE ''%REORGANIZE%''
                AND C.DatabaseName = '''
                      + @DatabaseToInvestigate + '''
                AND IH.the_database IS NULL
        GROUP BY C.DatabaseName
               ,C.SchemaName
               ,C.ObjectName
               ,C.IndexName
               ,IndexType
               ,IX.fill_factor;';
		--PRINT @SQLstr
                  EXEC (@SQLstr);
--========================================================
-- ACCOUNT FOR INDEXES BOTH REBUILT AND REORGANIZED
--========================================================
                  SET @SQLstr = 'UPDATE  ##Index_History
					  SET     reorg_count = C2.reorganize_count
					  FROM    ##Index_History IH
					  INNER JOIN (SELECT  C.DatabaseName
						     ,C.SchemaName
							   ,C.ObjectName
							,C.IndexName
                           ,	COUNT(C.ID) AS reorganize_count
                      FROM    [' + @CommandLogDatabase
                      + '].dbo.CommandLog C
                      WHERE   C.CommandType = ''ALTER_INDEX''
                              AND C.ObjectType = ''U''
                              AND C.Command LIKE ''%REORGANIZE%''
                              AND C.DatabaseName = '''
                      + @DatabaseToInvestigate
                      + '''
			          GROUP BY C.DatabaseName
                            ,C.SchemaName
                            ,C.ObjectName
                            ,C.IndexName
                    ) C2 ON IH.the_database = C2.DatabaseName
                            AND IH.the_schema = C2.SchemaName
                            AND IH.the_object = C2.ObjectName
                            AND IH.the_index = C2.IndexName
					  WHERE   IH.rebuild_count > 0';
--print @SQLstr
                  EXEC (@SQLstr); 
--============================================================================
-- RETURN THE RESULTS
--============================================================================
 
                  SELECT    the_database
                           ,the_schema
                           ,the_object
                           ,the_index
                           ,index_type
                           ,ISNULL(CONVERT(VARCHAR(20), fill_factor),
                                   'No longer exists') AS fill_factor
                           ,rebuild_count
                           ,reorg_count
                  FROM      ##Index_History
                  WHERE     rebuild_count >= @CountThreshold
                            OR reorg_count >= @CountThreshold
                  ORDER BY  the_database
                           ,rebuild_count DESC
                           ,reorg_count DESC
                           ,the_object
                           ,the_index;
 

                  INSERT    INTO ##Recommendations
                            (CommandLine)
                            SELECT  'USE [' + the_database
                                    + ']; ' + @CRLF
                                    + 'ALTER INDEX ['
                                    + the_index + '] ON ['
                                    + the_schema + '].['
                                    + the_object + '] '
                                    + @CRLF + 'REBUILD '
                                    + CASE WHEN @SQLVersion = 9
                                           THEN ' '
                                           WHEN @SQLVersion > 9
                                           THEN 'PARTITION = ALL '
                                      END
                                    + 'WITH (PAD_INDEX = OFF, '
                                    + 'STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, '
                                    + CASE @SupportsOnlineRebuilds
                                        WHEN 1
                                        THEN 'ONLINE = ON, '
                                        WHEN 0
                                        THEN 'ONLINE = OFF, '
                                      END + @CRLF
                                    + 'ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = '
                                    + CASE WHEN fill_factor = '0'
                                           THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3))
                                           WHEN fill_factor = '100'
                                           THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3))
                                           WHEN fill_factor <= @OverRebuildThresholdNewFF
                                           THEN CAST(fill_factor
                                            - 10 AS VARCHAR(3))
                                           WHEN (fill_factor > @OverRebuildThresholdNewFF
                                            AND fill_factor < 100)
                                           THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3))
                                           ELSE NULL /* unknown case */
                                      END + '); ' + @CRLF
                                    + '/* Analysis: Excessive rebuilds ('
                                    + CAST(rebuild_count AS VARCHAR(3))
                                    + ') and '
                                    + 'current fill factor = '
                                    + CAST(fill_factor AS VARCHAR(3))
                                    + ' */' + @CRLF + @CRLF AS RebuildCommand
                            FROM    ##Index_History
                            WHERE   rebuild_count >= @CountThreshold
                                    AND (fill_factor = 0
                                         OR fill_factor >= 60)
                            UNION ALL
                            SELECT  'USE [' + the_database
                                    + ']; ' + @CRLF
                                    + 'ALTER INDEX ['
                                    + the_index + '] ON ['
                                    + the_schema + '].['
                                    + the_object + '] '
                                    + @CRLF + 'REBUILD '
                                    + CASE WHEN @SQLVersion = 9
                                           THEN ' '
                                           WHEN @SQLVersion > 9
                                           THEN 'PARTITION = ALL '
                                      END
                                    + 'WITH (PAD_INDEX = OFF, '
                                    + 'STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, '
                                    + CASE @SupportsOnlineRebuilds
                                        WHEN 1
                                        THEN 'ONLINE = ON, '
                                        WHEN 0
                                        THEN 'ONLINE = OFF, '
                                      END + @CRLF
                                    + 'ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = '
                                    + CASE WHEN fill_factor = '0'
                                           THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3))
                                           WHEN fill_factor = '100'
                                           THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3))
                                           WHEN fill_factor <= @OverReorgThresholdNewFF
                                           THEN CAST(fill_factor
                                            - 10 AS VARCHAR(3))
                                           WHEN (fill_factor > @OverReorgThresholdNewFF
                                            AND fill_factor < 100)
                                           THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3))
                                           ELSE NULL /* unknown case */
                                      END + '); ' + @CRLF
                                    + '/* Analysis: Excessive reorgs ('
                                    + CAST(reorg_count AS VARCHAR(3))
                                    + ') and '
                                    + 'current fill factor = '
                                    + CAST(fill_factor AS VARCHAR(3))
                                    + ' */' + @CRLF + @CRLF AS RebuildCommand
                            FROM    ##Index_History
                            WHERE   reorg_count >= @CountThreshold
                                    AND (fill_factor = 0
                                         OR fill_factor >= 60);

                  IF EXISTS ( SELECT    1
                              FROM      ##Recommendations )
							  OR EXISTS ( SELECT
                                            1
                                       FROM ##Index_History
                                       WHERE
                                            (rebuild_count >= @CountThreshold
                                            OR reorg_count >= @CountThreshold)
                                            AND fill_factor <= 50
                                            AND fill_factor > 0 )
                     BEGIN
                           SET @EmailBody = @CRLF
                               + 'Analysis of the index defrag log table has been performed and it appears '
                               + 'there are indexes that are being repeatedly defragged. Repeated defragging may '
                               + 'indicate the indexes need a lower fill factor to reduce page splits. Repeated '
                               + 'defragging also wastes resources and generates large amounts of potentially '
                               + 'unnecessary transaction log entries. The parameters used to generate this report '
                               + 'are: ' + @CRLF + @CRLF
                               + 'Server: ' + @@SERVERNAME
                               + @CRLF + 'Database: '
                               + @DatabaseToInvestigate
                               + @CRLF
                               + 'Min number of rebuilds / reorgs flagged as excessive: '
                               + CAST(@CountThreshold AS VARCHAR(3))
                               + '. ' + @CRLF
                               + 'Starting suggested fill factor for excessive rebuilds: '
                               + CAST(@OverRebuildThresholdNewFF AS VARCHAR(3))
                               + '. ' + @CRLF
                               + 'Starting suggested fill factor for excessive reorgs: '
                               + CAST(@OverReorgThresholdNewFF AS VARCHAR(3))
                               + '. ' + @CRLF + @CRLF
                               + @CRLF
                               + 'Below are the suggested commands to change the fill factor on the indexes in question. '
                               + 'These commands should be carefully evaluated before being run to ensure they are '
                               + 'appropriate for the situation! In particular, if this routine is run more frequently '
                               + 'than the defrag log table is cleared, it may result in inappropriate fill factor '
                               + 'recommendations.' + @CRLF
                               + @CRLF + @CRLF;

                           IF @SupportsOnlineRebuilds = 1
                              BEGIN
                                    SET @EmailBody = @EmailBody
                                        + 'SQL Server edition does support online index rebuilds.'
                                        + @CRLF + @CRLF;
                              END;
                           ELSE
                              BEGIN
                                    SET @EmailBody = @EmailBody
                                        + 'SQL Server edition does NOT support online index rebuilds.'
                                        + @CRLF + @CRLF;
                              END;


                           IF EXISTS ( SELECT
                                            1
                                       FROM ##Index_History
                                       WHERE
                                            (rebuild_count >= @CountThreshold
                                            OR reorg_count >= @CountThreshold)
                                            AND fill_factor <= 50
                                            AND fill_factor > 0 )
                              BEGIN
                                    SET @EmailBody = @EmailBody
                                        + @CRLF + @CRLF
                                        + 'Note: Some indexes were found that are being defragged regularly and have a fill factor '
                                        + 'setting of 50 or less. Reducing the fill factor further is not generally recommended '
                                        + 'and further investigation is warranted.'
                                        + @CRLF + @CRLF;

                                    INSERT  INTO ##Recommendations
                                            (CommandLine)
                                            SELECT
                                            '/* Database ['
                                            + the_database
                                            + '], index ['
                                            + the_index
                                            + ']' + ' on ['
                                            + the_schema
                                            + '].['
                                            + the_object
                                            + '] '
                                            + 'is being repeatedly rebuilt and its fill factor '
                                            + 'is already set to 50 or less. This likely is worth a deeper '
                                            + 'investigation. */' AS RebuildCommand
                                            FROM
                                            ##Index_History
                                            WHERE
                                            rebuild_count >= @CountThreshold
                                            AND fill_factor <= 50
                                            AND fill_factor > 0
                                            UNION ALL
                                            SELECT
                                            '/* Database ['
                                            + the_database
                                            + '], index ['
                                            + the_index
                                            + ']' + ' on ['
                                            + the_schema
                                            + '].['
                                            + the_object
                                            + '] '
                                            + 'is being repeatedly reorganized and its fill factor '
                                            + 'is already set to 50 or less. This likely is worth a deeper '
                                            + 'investigation. */' AS RebuildCommand
                                            FROM
                                            ##Index_History
                                            WHERE
                                            reorg_count >= @CountThreshold
                                            AND fill_factor <= 50
                                            AND fill_factor > 0;

                              END;
                           SET @EmailSubject = 'Excessive Index Defragmenting found on '
                               + @@Servername
                               + ', database '
                               + @DatabaseToInvestigate;
					
                           EXEC msdb.dbo.sp_send_dbmail
                            @profile_name = @MailProfileName
                           ,@recipients = @MailRecipients
                           ,@subject = @EmailSubject
                           ,@body = @EmailBody
                           ,@query = 'SELECT * FROM ##Recommendations'
                           ,@query_result_width = 32767
                           ,@query_no_truncate = 1;


                     END;

--============================================================================
-- CLEANUP THE MESS
--============================================================================

                  IF EXISTS ( SELECT    name
                              FROM      tempdb.sys.objects
                              WHERE     name = '##Index_History' )
                     BEGIN
                           DROP TABLE ##Index_History;
                     END;
 
                  IF EXISTS ( SELECT    name
                              FROM      tempdb.sys.objects
                              WHERE     name = '##Indexes' )
                     BEGIN
                           DROP TABLE ##Indexes;
                     END;

                  IF EXISTS ( SELECT    name
                              FROM      tempdb.sys.objects
                              WHERE     name = '##Recommendations' )
                     BEGIN
                           DROP TABLE ##Recommendations;
                     END;

                  DELETE    FROM ##DatabasesToInvestigate
                  WHERE     the_database = @DatabaseToInvestigate;

            END;

      IF EXISTS ( SELECT    name
                  FROM      tempdb.sys.objects
                  WHERE     name = '##DatabasesToInvestigate' )
         BEGIN
               DROP TABLE ##DatabasesToInvestigate;
         END;

      SET NOCOUNT OFF;

Share

Enabling Data Compression – November 2015 Update

It never fails. You work on something, be it a painting, a story, or even a SQL script, and tweak it until you’ve got it just right and, finally, after all your hard work, you publish it. Then you immediately find something you want to change.

Guess what? The very day my updated data compression script was published here, I found something I wanted to change. I mean, the thing hadn’t even been out for 2 hours yet, for cryin’ out loud!

But I discovered a new command that I just had to implement.

I had just completed enabling compression in a database and had shrunk it to reclaim some of the space. After that, of course, I had to defragment it, since the shrink process completely fragments the database. I was using Ola’s index maintenance script and noticed it was printing out messages as it ran. My scripts, which use either PRINT statements or SELECT statements to print out status messages, usually don’t display anything to the SSMS results window until the whole script finished. I decided to investigate how Ola was managing this feat.

Turns out, it’s not really a big secret. You simply use the RAISERROR WITH NOWAIT command and specify a severity level of 10 or less. The low severity means SQL doesn’t abort with an error message and keeps running the script. The NOWAIT instructs SQL to output the message to the SSMS Message pane immediately. Very handy for outputting progress updates for long running scripts.

Of course, this was a new shiny and I had to use it. I’ve updated my compression script to use this method to output messages. In addition to converting the existing messages, I added some additional progress messages as well. Full details on how to use the script can be found here.

This may not be a new concept to you, but it was to me. It’s always fun to learn something new!

CREATE PROC [dbo].[up_CompressDatabase]
      (
       @minCompression FLOAT = .25 /* e.g. .25 for minimum of 25% compression */
      ,@MaxRunTimeInMinutes INT = 60
      ,@ExecuteCompressCommand BIT = 0  /* 1 to execute command */
      ,@DBToScan sysname /* database to compress */
      )
AS
      SET NOCOUNT ON;
 
/*
    Original script by Paul Nielsen www.SQLServerBible.com March 13, 2008
    Modified by Shaun J. Stuart www.shaunjstuart.com February 27, 2013
 
  Sets compression for all objects and indexes in the database needing adjustment
  If estimated gain is equal to or greater than min compression parameter
    then enables row or page compression, whichever gives greater space savings
  If row and page have same gain then, enables row compression
  If estimated gain is less than min compression parameter, then compression is set to none
 
  - SJS 2/27/13
  - Added @MaxRunTimeInMinutes to limit run length (checked afer each command, so
        may run longer) Note: This timer only applies to the actual compression process.
        It does not limit the time it takes to perform a scan of all the tables / indexes
        and estimate compression savings.
  - Sorted compression cursor to compress smallest tables first
  - Added flag to execute compression command or not
  - Added sort in tempdb option (always)
  - Re-wrote slightly to persist initial scan results. Will only re-scan after all tables
        have been processed
 
    - SJS 7/17/13
    - Modified to only look at data that is not already compressed
    - Modified for that any items with null none_size, page_size, or row_size are no longer set as
        AlreadyProcessed and now have their sizes calculated (i.e. if process was cancelled before
        initial scan was completed)
    - Modified so that time limit now applies to compression estimate process as well as actual
        compression process
    - Only look at partitions that have at least one row
 
    - SJS 8/6/13
    - Changed name of dbEstimate table to dbCompressionEstimates for clarity
    - Added StatusText column to dbCompressionEstimates and added status messages
    - Modified to take database name as input parameter to allow use in utility database
        instead of the DB being compressed (Table dbCompressionEstimates, which stores sizes and
        compression estimates, is still created in the current database.)
    - Note: Compression estimates are only made for the database supplied as an input
        parameter. However, that database name is stored in the dbCompressionEstimates table and
        will be read from there and used when actually performing the compression. This allows you to
        create estimates only for multiple databases (using @ExecuteCompressCommand=0), then perform
        the compression across multiple databases later (with @ExecuteCompressCommand=1).
    - Removed all references to code that checked already compressed data since this routine now only
        looks at uncompressed data.
 
    - SJS 8/21/13
    - Put []s around table and index names in compression commands.
 
    - SJS 10/7/13
    - Added check to make sure table / index still exists before estimating or performing compression.
    - Fixed bug in cursor to determine compression estimates (left out db name in where clause)

    - SJS 1/31/14
    - Fixed bug where nonclustered indexes were always flagged as no longer present (Thanks to cp
		for pointing this out at
		http://shaunjstuart.com/archive/2013/10/enabling-data-compression-october-2013-update/comment-page-1/#comment)

    - SJS 4/14/14
    - Another fix to handle tables with spaces in their names
 
    - SJS 8/12/15
    - Put []s around table and schema names in estimate page and row compression commands
    - Added edition check

    - SJS 10/13/15
    - Changed PRINT statements to RAISERROR WITH NOWAITs to output info immediately during execution
    - Fixed bug where error was generated when max runtime exceeded


*/

      DECLARE @EditionCheck VARCHAR(50);
      DECLARE @ProgressMessage VARCHAR(2044);

      SELECT    @EditionCheck = CAST(SERVERPROPERTY('Edition') AS VARCHAR(50));
      IF LEFT(@EditionCheck, 10) <> 'Enterprise'
         AND LEFT(@EditionCheck, 9) <> 'Developer'
         BEGIN
               PRINT 'Database compression is only supported on Enterprise and Developer editions '
                     + 'of SQL Server. This server is running '
                     + @EditionCheck + '.';
               RETURN;
         END;



      IF ISNULL(@DBToScan, '') NOT IN (SELECT
                                            [name]
                                       FROM sys.databases)
         BEGIN
               SELECT   'Database ' + ISNULL(@DBToScan,
                                            'NULL')
                        + ' not found on server.';
               RETURN;
         END;
 
      DECLARE @CompressedCount INT;
      SET @CompressedCount = 0;
 
      DECLARE @SQL NVARCHAR(MAX);
      DECLARE @ParmDefinition NVARCHAR(100);
      DECLARE @TestResult NVARCHAR(20);
      DECLARE @CheckString NVARCHAR(1000);
 
      DECLARE @StartTime DATETIME2;
      SET @StartTime = CURRENT_TIMESTAMP;
 
      DECLARE @CurrentDatabase sysname;
      SET @CurrentDatabase = DB_NAME();
 
      IF OBJECT_ID('tempdb..##ObjEst', 'U') IS NOT NULL
         DROP TABLE ##ObjEst;
 
      CREATE TABLE ##ObjEst
             (
              PK INT IDENTITY
                     NOT NULL
                     PRIMARY KEY
             ,object_name VARCHAR(250)
             ,schema_name VARCHAR(250)
             ,index_id INT
             ,partition_number INT
             ,size_with_current_compression_setting BIGINT
             ,size_with_requested_compression_setting BIGINT
             ,sample_size_with_current_compression_setting
                BIGINT
             ,sample_size_with_requested_compresison_setting
                BIGINT
             );
 
      IF NOT EXISTS ( SELECT    1
                      FROM      sys.objects
                      WHERE     object_id = OBJECT_ID(N'[dbo].[dbCompressionEstimates]')
                                AND type IN (N'U') )
         BEGIN
               CREATE TABLE dbo.dbCompressionEstimates
                      (
                       PK INT IDENTITY
                              NOT NULL
                              PRIMARY KEY
                      ,DatabaseName sysname
                      ,schema_name VARCHAR(250)
                      ,object_name VARCHAR(250)
                      ,index_id INT
                      ,ixName VARCHAR(255)
                      ,ixType VARCHAR(50)
                      ,partition_number INT
                      ,data_compression_desc VARCHAR(50)
                      ,None_Size INT
                      ,Row_Size INT
                      ,Page_Size INT
                      ,AlreadyProcessed BIT
                      ,StatusText VARCHAR(75)
                      );
         END;
 
  /*
 If all objects have been processed, rescan and start fresh. Useful for catching
  added objects since last scan. But beware - this is I/O intensive and can take a while.
*/
 
      IF NOT EXISTS ( SELECT    1
                      FROM      dbo.dbCompressionEstimates
                      WHERE     AlreadyProcessed = 0
                                AND DatabaseName = @DBToScan )
         BEGIN
               SET @ProgressMessage = 'No unprocessed items found. Starting new scan.';
               RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
               DELETE   FROM dbo.dbCompressionEstimates
               WHERE    DatabaseName = @DBToScan;
 
               SET @SQL = 'USE [' + @DBToScan + '];
                        INSERT   INTO [' + @CurrentDatabase
                   + '].dbo.dbCompressionEstimates
                                (DatabaseName
                                ,schema_name
                                ,object_name
                                ,index_id
                                ,ixName
                                ,ixType
                                ,partition_number
                                ,data_compression_desc
                                ,AlreadyProcessed
                                ,StatusText)
                        SELECT  ''' + @DBToScan
                   + '''
                                ,S.name
                               ,o.name
                               ,I.index_id
                               ,I.name
                               ,I.type_desc
                               ,P.partition_number
                               ,P.data_compression_desc
                               ,0 AS AlreadyProcessed
                               ,''Initial load'' AS StatusText
                        FROM    [' + @DBToScan
                   + '].sys.schemas AS S
                                JOIN [' + @DBToScan
                   + '].sys.objects AS O ON S.schema_id = O.schema_id
                                JOIN [' + @DBToScan
                   + '].sys.indexes AS I ON o.object_id = I.object_id
                                JOIN [' + @DBToScan
                   + '].sys.partitions AS P ON I.object_id = P.object_id
                                                            AND I.index_id = p.index_id
                        WHERE   O.TYPE = ''U''
                                AND P.data_compression_desc = ''NONE''
                                AND P.rows > 0;';     -- only look at objects with data
               EXEC (@SQL);
         END;
            -- Determine Compression Estimates
      SET @ProgressMessage = 'Beginning compression estimate phase.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
      DECLARE @PK INT
             ,@DatabaseName sysname
             ,@Schema VARCHAR(150)
             ,@object VARCHAR(150)
             ,@DAD VARCHAR(25)
             ,@partNO VARCHAR(3)
             ,@indexID VARCHAR(3)
             ,@ixName VARCHAR(250)
             ,@ixType VARCHAR(50)
             ,@Recommended_Compression VARCHAR(10);
 
      DECLARE cCompress CURSOR FAST_FORWARD
      FOR
              SELECT    schema_name
                       ,object_name
                       ,index_id
                       ,partition_number
                       ,data_compression_desc
                       ,ixName
              FROM      dbo.dbCompressionEstimates
              WHERE     (None_Size IS NULL
                         OR Row_Size IS NULL
                         OR Page_Size IS NULL)
                        AND DatabaseName = @DBToScan;
 
      OPEN cCompress;
 
      FETCH cCompress INTO @Schema, @object, @indexID,
            @partNO, @DAD, @ixName;
 
      WHILE @@Fetch_Status = 0
            BEGIN
                            /* evaluate objects with no compression */
                  IF DATEDIFF(mi, @StartTime,
                              CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
                     BEGIN
                            /* First, make sure the table / index still exists (in case this
                                process is run over multiple days */
 
                           IF @indexID = 0
                              BEGIN /* heaps */
                                    SET @CheckString = 'IF object_ID('''
                                        + @DBToScan + '.'
                                        + @Schema + '.'
                                        + @object
                                        + ''') IS NULL
                                    BEGIN
                                        SELECT @TestResultOUT = ''Does Not Exist''
                                    END
                                    ELSE
                                    BEGIN
                                        SELECT @TestResultOUT = ''Exists''
                                    END';
                              END;
                           ELSE
                              BEGIN /* indexes */
                                    SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
                                        + @DBToScan
                                        + '].[sys].[indexes] WHERE [name] ='''
                                        + @ixName
                                        + ''' AND OBJECT_ID('''
                                        + '[' + @DBToScan
                                        + ']' + '.['
                                        + @Schema + '].['
                                        + @object + ']'''
                                        + ') = [object_id])
                                    BEGIN
                                        SELECT @TestResultOUT = ''Does Not Exist''
                                    END
                                    ELSE
                                    BEGIN
                                        SELECT @TestResultOUT = ''Exists''
                                    END';
                              END;

                           SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
                           
                           EXECUTE sp_executesql
                            @CheckString
                           ,@ParmDefinition
                           ,@TestResultOUT = @TestResult OUTPUT;
                           IF @TestResult = 'Exists'
                              BEGIN
 
                                    IF @DAD = 'none'
                                       BEGIN
                                    /* estimate Page compression */
                                            SET @SQL = 'USE ['
                                            + @DBToScan
                                            + '];
                                        INSERT  ##ObjEst
                                                (object_name
                                                ,schema_name
                                                ,index_id
                                                ,partition_number
                                                ,size_with_current_compression_setting
                                                ,size_with_requested_compression_setting
                                                ,sample_size_with_current_compression_setting
                                                ,sample_size_with_requested_compresison_setting)
                                                EXEC sp_estimate_data_compression_savings
                                                    @Schema_name = ['
                                            + @Schema
                                            + ']
                                                   ,@object_name = ['
                                            + @object
                                            + ']
                                                   ,@index_id = '
                                            + @indexID
                                            + '
                                                   ,@partition_number = '
                                            + @partNO
                                            + '
                                                   ,@data_compression = ''PAGE'';';

                                            SET @ProgressMessage = 'Estimating PAGE compression for '
                                            + '['
                                            + @DBToScan
                                            + '].['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '], '
                                            + 'index id '
                                            + CAST(@indexID AS VARCHAR(3))
                                            + ', '
                                            + 'partition number '
                                            + CAST(@partNO AS VARCHAR(3));
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                            EXEC (@SQL);
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            None_Size = O.size_with_current_compression_setting
                                           ,Page_Size = O.size_with_requested_compression_setting
                                           ,StatusText = 'Compression estimate 50% complete'
                                            FROM
                                            dbo.dbCompressionEstimates D
                                            JOIN ##ObjEst O
                                            ON D.schema_name = O.schema_name
                                            AND D.object_name = O.object_name
                                            AND D.index_id = O.index_id
                                            AND D.partition_number = O.partition_number;
 
                                            DELETE
                                            ##ObjEst;
 
                                    -- estimate Row compression
                                            SET @SQL = 'USE ['
                                            + @DBToScan
                                            + '];
                                        INSERT  ##ObjEst
                                                (object_name
                                                ,schema_name
                                                ,index_id
                                                ,partition_number
                                                ,size_with_current_compression_setting
                                                ,size_with_requested_compression_setting
                                                ,sample_size_with_current_compression_setting
                                                ,sample_size_with_requested_compresison_setting)
                                                EXEC sp_estimate_data_compression_savings
                                                    @Schema_name = ['
                                            + @Schema
                                            + ']
                                                   ,@object_name = ['
                                            + @object
                                            + ']
                                                   ,@index_id = '
                                            + @indexID
                                            + '
                                                   ,@partition_number = '
                                            + @partNO
                                            + '
                                                   ,@data_compression = ''ROW'';';

                                            SET @ProgressMessage = 'Estimating ROW compression for '
                                            + '['
                                            + @DBToScan
                                            + '].['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '], '
                                            + 'index id '
                                            + CAST(@indexID AS VARCHAR(3))
                                            + ', '
                                            + 'partition number '
                                            + CAST(@partNO AS VARCHAR(3));
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;


                                            EXEC (@SQL);
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            Row_Size = O.size_with_requested_compression_setting
                                           ,StatusText = 'Compression estimate 100% complete'
                                            FROM
                                            dbo.dbCompressionEstimates D
                                            JOIN ##ObjEst O
                                            ON D.schema_name = O.schema_name
                                            AND D.object_name = O.object_name
                                            AND D.index_id = O.index_id
                                            AND D.partition_number = O.partition_number;
 
                                            DELETE
                                            ##ObjEst;
                                       END; /* end evaluating objects with no compression */
                              END;
                           ELSE /* table or index no longer exists */
                              BEGIN
                                    SET @ProgressMessage = 'Encountered table or index that no longer exists at compression estimate stage. Skipping.';
                                    RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                    UPDATE  dbo.dbCompressionEstimates
                                    SET     AlreadyProcessed = 1
                                           ,StatusText = 'Object no longer exists at compression estimate stage'
                                    WHERE   schema_name = @Schema
                                            AND object_name = @object
                                            AND index_id = @indexID
                                            AND partition_number = @partNO
                                            AND data_compression_desc = @DAD
                                            AND DatabaseName = @DBToScan;
                              END;
 
                           FETCH NEXT FROM cCompress INTO @Schema,
                                 @object, @indexID, @partNO,
                                 @DAD, @ixName;
                     END; -- end time check block
                  ELSE
                     BEGIN
                           SET @ProgressMessage = 'Max runtime reached. No compression performed. Compression estimate still processing. Exiting...';
                           RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                           CLOSE cCompress;
                           DEALLOCATE cCompress;
                           DROP TABLE ##ObjEst;
                           RETURN;
                     END;
            END; -- end while loop
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      SET @ProgressMessage = 'Compression estimate scan complete.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
         --END
  /* End evaluating compression savings. Now do the actual compressing. */
 
      SET @ProgressMessage = 'Beginning compression.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;
 
  /* Do not process objects that do not meet our compression criteria */
 
      SET @ProgressMessage = 'Skipping empty objects and objects that do not meet '
          + 'the minimum compression threshold.';
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'Best compression method less than minCompression threshold'
      WHERE     (1 - (CAST(Row_Size AS FLOAT) / None_Size)) < @minCompression
                AND (Row_Size <= Page_Size)
                AND None_Size > 0
                AND AlreadyProcessed = 0;
 
      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'Best compression method less than minCompression threshold'
      WHERE     (1 - (CAST(Page_Size AS FLOAT) / None_Size)) < @minCompression
                AND (Page_Size <= Row_Size)
                AND None_Size > 0
                AND AlreadyProcessed = 0;
 
  /* Do not set compression on empty objects */
 
      UPDATE    dbo.dbCompressionEstimates
      SET       AlreadyProcessed = 1
               ,StatusText = 'No data in table to compress'
      WHERE     None_Size = 0
                AND AlreadyProcessed = 0;
 
 -- set the compression
      DECLARE cCompress CURSOR FAST_FORWARD
      FOR
              SELECT    DatabaseName
                       ,schema_name
                       ,object_name
                       ,partition_number
                       ,ixName
                       ,ixType
                       ,CASE WHEN (1
                                   - (CAST(Row_Size AS FLOAT)
                                      / None_Size)) >= @minCompression
                                  AND (Row_Size <= Page_Size)
                             THEN 'Row'
                             WHEN (1
                                   - (CAST(Page_Size AS FLOAT)
                                      / None_Size)) >= @minCompression
                                  AND (Page_Size <= Row_Size)
                             THEN 'Page'
                             ELSE 'None'
                        END AS Recommended_Compression
                       ,PK
              FROM      dbo.dbCompressionEstimates
              WHERE     None_Size <> 0
                        AND (CASE WHEN (1
                                        - (CAST(Row_Size AS FLOAT)
                                           / None_Size)) >= @minCompression
                                       AND (Row_Size <= Page_Size)
                                  THEN 'Row'
                                  WHEN (1
                                        - (CAST(Page_Size AS FLOAT)
                                           / None_Size)) >= @minCompression
                                       AND (Page_Size <= Row_Size)
                                  THEN 'Page'
                                  ELSE 'None'
                             END <> data_compression_desc)
                        AND AlreadyProcessed = 0
              ORDER BY  None_Size ASC;      /* start with smallest tables first */
 
      OPEN cCompress;
 
      FETCH cCompress INTO @DatabaseName, @Schema, @object,
            @partNO, @ixName, @ixType,
            @Recommended_Compression, @PK;  -- prime the cursor;
 
      WHILE @@Fetch_Status = 0
            BEGIN
 
                  IF @ixType = 'Clustered'
                     OR @ixType = 'heap'
                     BEGIN
                           SET @SQL = 'USE ['
                               + @DatabaseName + '];
                                ALTER TABLE [' + @Schema
                               + '].[' + @object
                               + '] Rebuild with (data_compression = '
                               + @Recommended_Compression
                               + ', SORT_IN_TEMPDB=ON)';
 
                           SET @CheckString = 'IF object_ID('''
                               + @DatabaseName + '.'
                               + @Schema + '.' + @object
                               + ''') IS NULL
                                BEGIN
                                    SELECT @TestResultOUT = ''Does Not Exist''
                                END
                                ELSE
                                BEGIN
                                    SELECT @TestResultOUT = ''Exists''
                                END';
                     END;
 
                  ELSE  /* non-clustered index */
                     BEGIN
                           SET @SQL = 'USE ['
                               + @DatabaseName + '];
                                ALTER INDEX [' + @ixName
                               + '] on [' + @Schema + '].['
                               + @object
                               + '] Rebuild with (data_compression = '
                               + @Recommended_Compression
                               + ',SORT_IN_TEMPDB=ON)';
 
                           SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
                               + @DBToScan
                               + '].[sys].[indexes] WHERE [name] ='''
                               + @ixName
                               + ''' AND OBJECT_ID(''' + '['
                               + @DBToScan + ']' + '.['
                               + @Schema + '].[' + @object
                               + ']'''
                               + ') = [object_id])
                            BEGIN
                                SELECT @TestResultOUT = ''Does Not Exist''
                            END
                            ELSE
                            BEGIN
                                SELECT @TestResultOUT = ''Exists''
                            END';
                     END;

                  IF DATEDIFF(mi, @StartTime,
                              CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
                     BEGIN
                           IF @ExecuteCompressCommand = 1
                              BEGIN
 
                            /* verify that table / index still exists before doing anything */
 
                                    SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
                                    EXECUTE sp_executesql
                                        @CheckString
                                       ,@ParmDefinition
                                       ,@TestResultOUT = @TestResult OUTPUT;
                                    IF @TestResult = 'Exists'
                                       BEGIN
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            StatusText = 'Compressing data...'
                                            WHERE
                                            PK = @PK;
 
                                            SET @ProgressMessage = 'Compressing table/index: '
                                            + '[' + @ixName
                                            + '] on ['
                                            + @Schema
                                            + '].['
                                            + @object
                                            + '] in database '
                                            + @DatabaseName;

                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                                            EXEC sp_executesql
                                            @SQL;
 
                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            AlreadyProcessed = 1
                                           ,StatusText = 'Compression complete'
                                            WHERE
                                            PK = @PK;
 
                                            SET @CompressedCount = @CompressedCount
                                            + 1;
                                       END;
                                    ELSE
                                       BEGIN
                                            SET @ProgressMessage = 'Encountered table or index that no longer exists at compression stage. Skipping.';
                                            RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;


                                            UPDATE
                                            dbo.dbCompressionEstimates
                                            SET
                                            AlreadyProcessed = 1
                                           ,StatusText = 'Object no longer exists at compression stage'
                                            WHERE
                                            PK = @PK;
                                       END;
                              END;
                           ELSE
                              BEGIN

                                    SET @ProgressMessage = 'Command execution not enabled. Command is:'
                                        + @SQL;
                                    RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                              END;
                     END;
                  ELSE
                     BEGIN
                           SET @ProgressMessage = 'Max runtime reached. Some compression performed. Exiting...';
                           RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

                           CLOSE cCompress;
                           DEALLOCATE cCompress;
                           DROP TABLE ##ObjEst;
                           RETURN;
                     END;
 
                  FETCH cCompress INTO @DatabaseName,
                        @Schema, @object, @partNO, @ixName,
                        @ixType, @Recommended_Compression,
                        @PK;
            END;
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      SET @ProgressMessage = 'Objects compressed: '
          + CONVERT(VARCHAR(10), @CompressedCount);
      RAISERROR(@ProgressMessage,0,1) WITH NOWAIT;

      DROP TABLE ##ObjEst;
      RETURN;

Share

Drive Space Monitoring Update – October 2015

Over five years ago, I wrote my first (and so far, only) article for SQLServerCentral.com with a routine for gathering database disk stats for your SQL Server databases that live on different SQL servers. I realized that the last update I posted here was four years ago. I continue to use the script and have made several more revisions to it over the years. Here’s the latest version. Please see the original article for full details of how it works. The entire solution consists of a couple of SQL tables, a stored procedure, and an optional PowerShell script. The article also discusses the permissions needed for the script to run correctly.

For this version, there is a modification needed to the main table, DatabaseDiskStats. I added a column to track databases that are using Enterprise edition-only features, such as compression or partitioning (valid only for SQL 2008+). This is handy when you are dealing with apps such as Sharepoint, which will enable enterprise edition features without any DBA input if it detects the SQL Server supports them.

The below script will create a version of the table that is compatible with this version of the stored procedure. The only index the script creates is on the primary key. You’ll want to look at your own reporting queries to determine if any other ones should be added for your environment.

CREATE TABLE [dbo].[DatabaseDiskStats](
	[PK] [INT] IDENTITY(1,1) NOT NULL,
	[ServersToCheckPK] [INT] NOT NULL,
	[Server_name] [VARCHAR](128) NOT NULL,
	[Database_name] [VARCHAR](128) NOT NULL,
	[Filename] [VARCHAR](260) NOT NULL,
	[Drive_letter] [CHAR](2) NOT NULL,
	[Size_in_MB] [INT] NULL,
	[MaxDriveSize_in_MB] [INT] NULL,
	[StatDate] [SMALLDATETIME] NULL CONSTRAINT [DF_DatabaseDiskStats_StatDate]  DEFAULT (CONVERT([VARCHAR](10),GETDATE(),(1))),
	[DriveFreeSpace_in_MB] [INT] NULL,
	[EntDevFeaturesEnabled] [VARCHAR](200) NULL CONSTRAINT [DF_DatabaseDiskStats_EntDevFeaturesEnabled]  DEFAULT (''),
 CONSTRAINT [PK_DatabaseDiskStats] PRIMARY KEY CLUSTERED 
(
	[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

There is also a change to the other table the solution uses, ServersToCheckDiskStats. I added three columns:

  • ServerDisplayName – this is a user-friendly name for the server that can be displayed on reports. Useful if you have servers in other domains that can only be reached by IP address and you want a report to show a more meaningful name.
  • IncludeInCheck – A flag to indicate if this server should be included in the disk gathering routine.
  • Notes – A short field for random notes you might want to include, such as why a server is not included in the routine.

The below script creates an appropriate version of this table.

CREATE TABLE [dbo].[ServersToCheckDiskStats](
	[PK] [INT] IDENTITY(1,1) NOT NULL,
	[ServerName] [VARCHAR](128) NOT NULL,
	[ServerDisplayName] [VARCHAR](128) NOT NULL,
	[PersistLink] [BIT] NOT NULL CONSTRAINT [DF_ServersToCheckDiskStats_PersistLink]  DEFAULT ((0)),
	[SQLServerVersion] [CHAR](4) NOT NULL,
	[TrackTotalDiskSpace] [BIT] NULL CONSTRAINT [DF_ServersToCheckDiskStats_TrackTotalDiskSpace]  DEFAULT ((1)),
	[IncludeInCheck] [BIT] NOT NULL CONSTRAINT [DF_ServersToCheckDiskStats_IncludeInCheck]  DEFAULT ((1)),
	[Notes] [VARCHAR](255) NULL,
 CONSTRAINT [PK_ServersToCheckDiskStats] PRIMARY KEY CLUSTERED 
(
	[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [IX_ServersToCheckDiskStats] UNIQUE NONCLUSTERED 
(
	[ServerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [IX_ServersToCheckDiskStats1] UNIQUE NONCLUSTERED 
) ON [PRIMARY]

GO

Supported values for the SQLServerVersion field are 2005, 2008 (which includes 2008 R2), 2012, 2014, and 2016.

Here is the latest version of the stored procedure. The major changes since I last published this are as follows:

  • Deletes existing records with same date as today’s date. This allows you to run the script multiple times on the same day without getting duplicate results. (Useful if a connection to a linked server failed for some reason and you need to re-run the script.)
  • Added some output for troubleshooting.
  • Added support for SQL 2012, 2014, and 2016.
  • Removed support for SQL 2000. (Good riddance!)
  • Improved code dealing with persisted linked servers.

In reviewing this code before posting it here, I noticed there is a lot of duplicate code. The command syntax generated is based on the version of SQL Server specified. This was mainly done to support SQL 2000, which had different syntax for some commands from SQL 2005,  SQL 2008, and SQL 2008 R2, which was the latest version at the time this was originally written. When I added SQL 2012 support later and then later still added support for SQL 2014 and 2016, I kept up this practice, even though the syntax for all those versions are the same. Now that I removed SQL 2000 support, it looks kind of silly because the resulting command is always the same. However, I find it useful and it allows me to easily add in some other option in the future that might have different syntax between versions. I like to have identical scripts on all my SQL Servers and this version specific coding helps with that.

Again, please be sure to read my original article on what the script does and how to use it. Thanks!

CREATE PROCEDURE [dbo].[GatherServerDiskUsageData]
AS
-- =============================================
-- Author: Shaun Stuart, shaunjstuart.com
-- Create date: August 24, 2010
-- Description: Procedure to collect disk space usage data
-- =============================================
-- 1/20/11 - Change server linked code to not run if @ServerName is same as @@SERVERNAME
--      (will remove entry for local server in sysservers otherwise and render
--      @@SERVERNAME = null on next restart of SQL Server) - SJS
--
-- 7/21/11 - Added IncludeInCheck flag to server cursor to allow skipping servers
--		   - Added ServerDisplayName field support
--
-- 12/25/12 - Added support for 2012 servers (same code as 2008)
--
-- 3/1/13 - Add step to delete existing records with current date to avoid
--				doubling counts if proc is re-run.
-- 12/13/13 - Added output showing which server we are trying to connect to (for
--				troubleshooting purposes)
-- 7/11/14	SJS	Moved code executing sp_addlinkedsrvlogin into begin/end block
--				adding linked server.
-- 7/24/14	SJS	Print status of @@SERVERNAME for troubleshooting
-- 8/18/15	SJS	Happy birthday!
--				Added support for SQL 2014 and 2016
-- 8/20/15	SJS	Removed support for SQL 2000
--				Added code to log Enterprise / Developer edition-only features (&amp;amp;gt;2005 only)
--				Alphabetized server list for processing
--				Added logic to better handle persisted linked servers. They might not use
--					logins with permissions we need, so this now creates another linked
--					server with a different name that has the permissions we need.

 
DECLARE @ServerName VARCHAR(128);
DECLARE @TempServerName VARCHAR(132); /* Used for servers with persisted links */
DECLARE @ServerDisplayName VARCHAR(128);
DECLARE @ServerNamePK INT;
DECLARE @PersistLink BIT;
DECLARE @SQLCmd VARCHAR(1000);
DECLARE @LinkedServerLogin VARCHAR(50);
DECLARE @LinkedServerLoginpwd VARCHAR(50);
DECLARE @SQLServerVersion CHAR(4);
DECLARE @TrackTotalDiskSpace BIT;
 
SET @LinkedServerLogin = 'DiskStatsUser';
SET @LinkedServerLoginpwd = 'password';
 
 /* Delete records for the current day, if any exist. This allows this procedure to
 be re-run if it fails without duplicating counts for the servers that did not fail. */


IF EXISTS ( SELECT  1
            FROM    DatabaseDiskStats
            WHERE   StatDate = CONVERT([VARCHAR](10), GETDATE(), (1)) )
   DELETE   FROM DatabaseDiskStats
   WHERE    StatDate = CONVERT([VARCHAR](10), GETDATE(), (1)); 


/* Get a list of the servers to gather data from and their SQL version */
 
DECLARE ServersCursor CURSOR
FOR
        SELECT  ServerName
               ,ServerDisplayName
               ,PK
               ,PersistLink
               ,SQLServerVersion
               ,TrackTotalDiskSpace
        FROM    ServersToCheckDiskStats
        WHERE   IncludeInCheck = 1
        ORDER BY ServerName;
 
OPEN    ServersCursor;
FETCH NEXT FROM ServersCursor INTO @ServerName,
      @ServerDisplayName, @ServerNamePK, @PersistLink,
      @SQLServerVersion, @TrackTotalDiskSpace;


PRINT '@@SERVERNAME system variable is: '
      + ISNULL(@@servername, 'NULL') + '.';
PRINT 'If this is NULL, procedure will fail. Use sp_addserver ''&amp;amp;lt;LocalServerName&amp;amp;gt;'', local to correct';
PRINT 'Restart of SQL is required for fix. If NULL, something executed sp_dropserver &amp;amp;lt;servername&amp;amp;gt; using';
PRINT 'the name of the server and then the server was restarted.';
SET NOCOUNT ON;
SELECT  '';
SET NOCOUNT OFF;

WHILE @@FETCH_STATUS = 0
      BEGIN
 
/* create linked server to get disk stats. Check PersistLink value to see if linked server should remain or be deleted */
            SET NOCOUNT ON;
            PRINT 'Attempting to connect to ' + @ServerName;
            SET NOCOUNT OFF;
            SET @TempServerName = @ServerName; 

            IF EXISTS ( SELECT  *
                        FROM    master.sys.servers
                        WHERE   @ServerName = name )
               AND @PersistLink = 0
               AND @@SERVERNAME &amp;amp;lt;&amp;amp;gt; @ServerName
               BEGIN
                     PRINT 'Dropping existing linked server...';
                     EXEC sp_dropserver
                        @ServerName
                       ,droplogins;
               END;

            IF @PersistLink = 0
               AND @@SERVERNAME &amp;amp;lt;&amp;amp;gt; @ServerName
               BEGIN
                     PRINT 'Adding linked server...';
                     EXEC sp_addlinkedserver
                        @server = @TempServerName;
                     PRINT 'Adding linked server login...';
                     EXEC sp_addlinkedsrvlogin
                        @ServerName
                       ,'false'
                       ,NULL
                       ,@LinkedServerLogin
                       ,@LinkedServerLoginpwd;
 
               END;

            IF @PersistLink = 1	/* Server link is already present, but it might not
											be using a login with permissions we need, so
											make our own linked server using a different name */
               AND @@SERVERNAME &amp;amp;lt;&amp;amp;gt; @ServerName
               BEGIN
                     SET @TempServerName = @ServerName
                         + 'Temp';
                     PRINT 'Adding linked server...';
                     EXEC sp_addlinkedserver
                        @server = @TempServerName
                       ,@srvproduct = ''
                       ,@provider = 'SQLNCLI'
                       ,@datasrc = @ServerName;

                     PRINT 'Adding linked server login...';
                     EXEC sp_addlinkedsrvlogin
                        @TempServerName
                       ,'false'
                       ,NULL
                       ,@LinkedServerLogin
                       ,@LinkedServerLoginpwd;
 
               END;


            SET NOCOUNT ON;
            PRINT 'Connection to ' + @ServerName
                  + ' was successful. Now gathering data...';
            SET NOCOUNT OFF;

/* pull disk usage data for each database file using the SQL version-appropriate system table */
 
            SELECT  @SQLCmd = CASE @SQLServerVersion
                                WHEN '2016'
                                THEN	/* same as 2014 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        sdbs.name AS database_name ,
                        smf.physical_name,
                        UPPER(LEFT(smf.physical_name, 2)) AS drive_letter ,
                        (smf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name &amp;amp;lt;&amp;amp;gt; ''tempdb''
                ORDER BY    smf.database_id'
                                WHEN '2014'
                                THEN	/* same as 2012 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        sdbs.name AS database_name ,
                        smf.physical_name,
                        UPPER(LEFT(smf.physical_name, 2)) AS drive_letter ,
                        (smf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name &amp;amp;lt;&amp;amp;gt; ''tempdb''
                ORDER BY    smf.database_id'
                                WHEN '2012'
                                THEN	/* same as 2008 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        sdbs.name AS database_name ,
                        smf.physical_name,
                        UPPER(LEFT(smf.physical_name, 2)) AS drive_letter ,
                        (smf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name &amp;amp;lt;&amp;amp;gt; ''tempdb''
                ORDER BY    smf.database_id'
                                WHEN '2008'
                                THEN 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        sdbs.name AS database_name ,
                        smf.physical_name,
                        UPPER(LEFT(smf.physical_name, 2)) AS drive_letter ,
                        (smf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name &amp;amp;lt;&amp;amp;gt; ''tempdb''
                ORDER BY    smf.database_id'
                                WHEN '2005'
                                THEN        /*same as 2008 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        sdbs.name AS database_name ,
                        smf.physical_name,
                        UPPER(LEFT(smf.physical_name, 2)) AS drive_letter ,
                        (smf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name &amp;amp;lt;&amp;amp;gt; ''tempdb''
                ORDER BY    smf.database_id'
                                ELSE        /* not a SQL version this procedure handles */ 'INSERT    DatabaseDiskStats
                        (ServersToCheckPK,
                         Server_name,
                         Database_name,
                         [Filename],
                         Drive_letter)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + '''' + ',' + ''''
                                     + 'undefined SQL version in table ServersToCheckDiskStats or version &amp;amp;lt; 2005'
                                     + ''''
                                     + ' AS database_name ,'
                                     + '''' + 'ERROR' + ''''
                                     + ',' + '''' + '*'
                                     + ''''
                              END;
 
            EXEC (@SQLCmd);
 
    /* Tempdb has to be handled differently. In 2005+, the size for tempdb stored in sys.master_files is not the
       current size of the file on disk. It is the initial size it will be created with when SQL Server restarts.
       The current size of tempdb on disk is stored in tempdb.sys.database_files. */
 
            SELECT  @SQLCmd = CASE @SQLServerVersion
                                WHEN '2016'
                                THEN	/* same as 2014 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''tempdb'' AS database_name ,
                        sdf.physical_name,
                        UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter ,
                        (sdf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                WHEN '2014'
                                THEN	/* same as 2012 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''tempdb'' AS database_name ,
                        sdf.physical_name,
                        UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter ,
                        (sdf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                WHEN '2012'
                                THEN	/* same as 2008 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''tempdb'' AS database_name ,
                        sdf.physical_name,
                        UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter ,
                        (sdf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                WHEN '2008'
                                THEN 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''tempdb'' AS database_name ,
                        sdf.physical_name,
                        UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter ,
                        (sdf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                WHEN '2005'
                                THEN        /*same as 2008 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''tempdb'' AS database_name ,
                        sdf.physical_name,
                        UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter ,
                        (sdf.size * 8 ) / 1024 AS size_in_mb
                FROM    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                ELSE        /* not a SQL version this procedure handles */ 'INSERT    DatabaseDiskStats
                        (ServersToCheckPK,
                         Server_name,
                         Database_name,
                         [Filename],
                         Drive_letter)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + '''' + ',' + ''''
                                     + 'undefined SQL version in table ServersToCheckDiskStats or version &amp;amp;lt; 2005'
                                     + ''''
                                     + ' AS database_name ,'
                                     + '''' + 'ERROR' + ''''
                                     + ',' + '''' + '*'
                                     + ''''
                              END;
 
            EXEC (@SQLCmd);

/* This next bit gets which databases are using Enterprise / Developer edition-only features */
/* This IS ONLY valid post-SQL 2005 */

            IF @SQLServerVersion &amp;amp;gt; '2005'
               BEGIN


		/* ##LinkedServerDBsTable contains a list of the databases on the linked server */
		/* Due to linked server, we cannot use a cursor without using dynamic SQL everywhere */


                     IF OBJECT_ID('tempdb..##LinkedServerDBsTable',
                                  'U') IS NOT NULL
                        BEGIN
                              DROP TABLE ##LinkedServerDBsTable;
                        END;


                     CREATE TABLE ##LinkedServerDBsTable
                            (
                             ServerName VARCHAR(128)
                            ,DBName VARCHAR(128)
                            );


                     SET @SQLCmd = 'INSERT INTO ##LinkedServerDBsTable
		        SELECT  ''' + @ServerDisplayName + ''',
				name
				FROM    [' + @TempServerName
				             + '].master.sys.databases
				WHERE   name NOT IN (''master'', ''tempdb'', ''model'',
                             ''msdb'')
                AND state = 0
				ORDER BY name;';
                     
					 EXEC(@SQLCmd);

                     DECLARE @DBName VARCHAR(128);

		/* ##TempTable1 holds Enterprise / Developer edition options in use
			for current db in cursor - one row per option */

                     IF OBJECT_ID('tempdb..##TempTable1',
                                  'U') IS NOT NULL
                        BEGIN
                              DROP TABLE ##TempTable1;
                        END;


                     CREATE TABLE ##TempTable1
                            (
                             ServerName VARCHAR(128)
                            ,DBName VARCHAR(128)
                            ,feature_name VARCHAR(400)
                            );


		/* ##TempTable2 holds Enterprise / Developer options in use for all dbs on
			server with options concatenated into comma separated string */

                     IF OBJECT_ID('tempdb..##TempTable2',
                                  'U') IS NOT NULL
                        BEGIN
                              DROP TABLE ##TempTable2;
                        END;


                     CREATE TABLE ##TempTable2
                            (
                             ServerName VARCHAR(128)
                            ,DBName VARCHAR(128)
                            ,feature_name VARCHAR(400)
                            );

                     DECLARE @LoopCounter INT;
                     SELECT @LoopCounter = COUNT(*)
                     FROM   ##LinkedServerDBsTable;
                     WHILE @LoopCounter &amp;amp;gt; 0
                           BEGIN
                                 SELECT TOP 1
                                        @DBName = DBName
                                 FROM   ##LinkedServerDBsTable;

                                 SET @SQLCmd = '
			       INSERT  INTO ##TempTable1
					        (ServerName
							,DBName
		                    ,feature_name)
				            SELECT  ''' + @ServerDisplayName
						                     + ''' AS [ServerName]
								   ,''' + @DBName + ''' AS [DBName]
		                           ,feature_name
				            FROM    [' + @TempServerName + '].['
						                     + @DBName
								             + '].sys.dm_db_persisted_sku_features;';
								--PRINT @SQLCmd;
                                 EXEC (@SQLCmd);

                                 INSERT INTO ##TempTable2
                                        (ServerName
                                        ,DBName
                                        ,feature_name)
                                        SELECT DISTINCT
                                            ServerName
                                           ,DBName
                                           ,STUFF(
												(SELECT   N', ' + feature_name
												FROM     ##TempTable1
	                                            FOR
		                                        XML
			                                    PATH('')
				                               ,TYPE)
												.value('text()[1]', 'nvarchar(max)'), 1, 2, N'')
                                        FROM
                                            ##TempTable1;

                                 DELETE FROM ##TempTable1;

                                 DELETE FROM ##LinkedServerDBsTable
                                 WHERE  DBName = @DBName;

                                 SELECT @LoopCounter = COUNT(*)
                                 FROM   ##LinkedServerDBsTable;

                           END;

		/* Now update DatabaseDiskStats table with info we just gathered */

                     UPDATE dds
                     SET    dds.EntDevFeaturesEnabled = ISNULL(tt2.feature_name,'')
                     FROM   ##TempTable2 tt2
                            INNER JOIN DatabaseDiskStats dds ON tt2.ServerName = dds.Server_name
                                            AND tt2.DBName = dds.Database_name
                                            AND dds.StatDate = (CONVERT([VARCHAR](10), GETDATE(), (1)));

					/* Clean up */
                     DROP TABLE ##LinkedServerDBsTable;
                     DROP TABLE ##TempTable1;
                     DROP TABLE ##TempTable2;

               END;
            ELSE /* Version &amp;amp;lt; 2005 */
               BEGIN
                     UPDATE DatabaseDiskStats
                     SET    EntDevFeaturesEnabled = 'Only valid for versions after SQL 2005'
                     WHERE  Server_name = @ServerDisplayName;
               END;


    /* Set MaxDriveSize_in_MB to zero for drives we are not tracking the size of */
 
            IF @TrackTotalDiskSpace = 0
               BEGIN
                     UPDATE DatabaseDiskStats
                     SET    MaxDriveSize_in_MB = 0
                     WHERE  Server_name = @ServerName
                            AND MaxDriveSize_in_MB IS NULL;
               END;

            SET NOCOUNT ON;
            PRINT 'Data gathered from ' + @ServerName
                  + '. Disconnecting...'; 
            SELECT  '    ';
            SET NOCOUNT OFF;
 
            IF /* @persistLink = 0 AND */ @@SERVERNAME &amp;amp;lt;&amp;amp;gt; @ServerName
               BEGIN
                     EXEC sp_dropserver
                        @TempServerName
                       ,droplogins;
               END;
 

            FETCH NEXT FROM ServersCursor INTO @ServerName,
                  @ServerDisplayName, @ServerNamePK,
                  @PersistLink, @SQLServerVersion,
                  @TrackTotalDiskSpace;
 
      END;
 
CLOSE ServersCursor;
DEALLOCATE ServersCursor;


Share