Shaun J Stuart

Just another SQL Server weblog

I'm pleased to announce my new online course is now up and available: SQL Server Maintenance Plans. This course is almost 2 hours long and covers every aspect of SQL Server's maintenance plans. You'll learn what each and every task does and you'll see examples of how to create plans using both the Maintenance Plan Wizard and the Maintenance Plan Designer. The course also briefly covers setting up SQL Agent, Database Mail, and alerts.

sql server maintenance plans

From now until the end of April, the first 100 people to sign up using this link (or coupon code PLAN50) will get a 50% discount. Starting May 1, all readers of this blog can use the coupon code BLOG15OFF for a 15% discount.

Don't forget - my other courses are still available:

Microsoft SQL Server 101. Use this link for a 15% discount.

Understanding Data Compression in SQL Server. Use this link for a 15% discount.

Share

Thanks for commenter Srini for catching another problem in how tables with spaces in their names were being handled. This version fixes that.

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
 
*/
 
      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
               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
      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'';'

											 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'';'
                                             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
                                    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
                           PRINT 'Max runtime reached. No compression performed. Compression estimate still processing. Exiting...';
                           CLOSE cCompress
                           DEALLOCATE cCompress
                           DROP TABLE ##ObjEst
                           RETURN
                     END
            END -- end while loop
 
      CLOSE cCompress
      DEALLOCATE cCompress
 
      PRINT 'Initial scan complete.'
         --END
  /* End evaluating compression savings. Now do the actual compressing. */
 
      PRINT 'Beginning compression.';
 
  /* Do not process objects that do not meet our compression criteria */
 
      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;
 
                                             PRINT 'Compressing table/index: '
                                                   + @Schema + '.' + @object
                                                   + ' in database '
                                                   + @DatabaseName;
                                             EXEC sp_executesql
                                                @SQL;
 
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    AlreadyProcessed = 1
                                                   ,StatusText = 'Compression complete'
                                             WHERE  PK = @PK;
 
                                             SET @CompressedCount = @CompressedCount
                                                 + 1;
                                       END
                                    ELSE
                                       BEGIN
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    AlreadyProcessed = 1
                                                   ,StatusText = 'Object no longer exists at compression stage'
                                             WHERE  PK = @PK;
                                       END
                              END
                           ELSE
                              BEGIN
                                    PRINT 'Command execution not enabled. Command is:'
                                          + @SQL;
                              END
                     END
                  ELSE
                     BEGIN
                           PRINT 'Max runtime reached. Some compression performed. Exiting...';
                           CLOSE cCompress
                           DEALLOCATE cCompress
                           DROP TABLE ##ObjEst
                           BREAK
                     END
 
                  FETCH cCompress INTO @DatabaseName, @Schema, @object,
                        @partNO, @ixName, @ixType, @Recommended_Compression,
                        @PK;
            END
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      PRINT 'Objects compressed: ' + CONVERT(VARCHAR(10), @CompressedCount);
      DROP TABLE ##ObjEst
      RETURN

Share

There is a lot of information in the world that people just assume is true because it has been repeated so much. Twinkies have an unlimited shelf life.  McDonalds hamburgers have so many preservatives, they will never get moldy. The problem is this information is frequently wrong. But the incorrect information is so widespread and so ingrained in our heads, it's sometimes hard to correct your thinking.

My thinking was stuck in a rut

My thinking was stuck in a rut

The use of TRUNCATE TABLE and how it affects the transaction log is one such piece of information in the world of SQL Server. The common perception is that a TRUNCATE TABLE statement is a non-logged operation and that once it has been issued, the transaction log backup containing that statement cannot be recovered to a point in time after that. A new log backup must be made to be able to recover to a point in time. I think most DBAs today realize TRUNCATE is not a non-logged operation -  it is a minimally logged operation. It can still be rolled back and Paul Randal gives a couple of nice demonstrations showing this. But many people with years of experience still think that once a TRUNCATE statement has been issued, you can no longer restore a log backup containing that statement to a point in time after that command. I will admit, I was one of these people.

A little while ago, I was working with a developer on a stored procedure and noticed that he had used a TRUNCATE TABLE statement. Out of habit, I mentioned that, because the table was only ever going to have a small amount of data, he should use a DELETE statement instead. (TRUNCATE TABLE statements are usually used when clearing out a  table that contains thousands or millions of rows of data because it is much faster than a DELETE statement for deleting large numbers of rows.) He stated he used TRUNCATE because he didn't care if the data could not be recovered in a disaster. It was transient data that could be recreated. I repeated to him the commonly held belief that if he used the TRUNCATE statement, then we would lose the ability to perform point in time restore until the next log backup happened. I decided to perform an experiment to illustrate this. It was then that I realized this was not true. A careful internet search revealed that yes, this is in fact, not true, even though many people think it is. (It doesn't help that even some published works perpetuate this misconception.)

So in an attempt to provide yet another voice in the wilderness trying to kill this myth, here is a sample showing that you can, in fact, recover a transaction log to a point in time after a TRUNCATE TABLE statement has been issued.

First, let's make a database and set it to full recovery mode.

CREATE DATABASE TruncateTest;
GO
ALTER DATABASE [TruncateTest] SET RECOVERY FULL ;
GO

Now, let's make two tables to work with. Once they are created, we'll take a full backup so we are not operating in pseudo-simple recovery mode and we can establish the log backup chain.

USE TruncateTest;

CREATE TABLE [dbo].[TableA] ([ColA] [int] NULL)
ON     [PRIMARY];

CREATE TABLE [dbo].[TableB] ([ColA] [int] NULL)
ON     [PRIMARY];

GO

-- Create full backup

BACKUP DATABASE [TruncateTest]
TO  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTest.bak'
GO

Now we'll insert 10 rows into each table so we have something to work with.

DECLARE @loop TINYINT;
SET @loop = 1;
WHILE @loop <= 10
      BEGIN
            INSERT  INTO TableA
                    (ColA)
            VALUES  (@loop);

            INSERT  INTO TableB
                    (ColA)
            VALUES  (@loop);

            SET @loop = @loop + 1;
      END

Now we'll insert an additional 5 rows into TableA. We'll do this with a named and marked transaction so we can explicitly specify this point during a transaction log restore. Then, let's truncate the table.


BEGIN TRANSACTION Transaction1 WITH MARK 'BeforeTruncateOfTableA'

DECLARE @loop TINYINT;
SET @loop = 11;
WHILE @loop <= 15
      BEGIN
            INSERT  INTO TableA
                    (ColA)
            VALUES  (@loop);

            SET @loop = @loop + 1;
      END

COMMIT TRANSACTION Transaction1

/* Now truncate TableA */

TRUNCATE TABLE TABLEA

Now we'll insert an additional 5 rows into TableB, also using a named transaction.

BEGIN TRANSACTION Transaction2 WITH MARK 'AfterTruncateOfTableA'

DECLARE @loop TINYINT;
SET @loop = 11;
WHILE @loop <= 15
      BEGIN
            INSERT  INTO TableB
                    (ColA)
            VALUES  (@loop);

            SET @loop = @loop + 1;
      END

COMMIT TRANSACTION Transaction2

And finally, we'll take a log backup.

BACKUP LOG [TruncateTest]
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTestLog.bak'
WITH INIT
GO

At this point, we have a log backup that contains two explicitly marked points - one before a table was truncated and one after. Let's see if we can restore to either one. First, restore to before the truncation.

/* First, restore the full backup */

USE master;

RESTORE DATABASE [TruncateTest]
FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTest.bak'
WITH  NORECOVERY, REPLACE;

/* Now, let's restore to the point just before the truncate table, just to show
it can be done. Note: STOPAT uses the transaction NAME, not the text specified
in the WITH MARK clause. That is only a description. */

RESTORE LOG [TruncateTest]
FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTestLog.bak'
WITH  STOPATMARK = 'Transaction1',
RECOVERY;

GO

At this point, TableA should have 15 rows and TableB should have 10 rows. Do they?

RestoreTest1

Now let's do it again, but stop at a point in time after the TRUNCATE statement.


USE master;

RESTORE DATABASE [TruncateTest]
FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTest.bak'
WITH  NORECOVERY, REPLACE;
GO

RESTORE LOG [TruncateTest]
FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTestLog.bak'
WITH  STOPATMARK = 'Transaction2',
RECOVERY;

The RESTORE completes successfully. That's our first indication this will work. What about the data? At this point, TableA should have zero records and TableB should have 15. Do they?

RestoreTest2

Yup.

So clearly, using a TRUNCATE TABLE statement does not prevent you from performing a point in time recovery!

Share

Blog reader cp spotted a pretty big bug in the last version of this routine. Namely, non-clustered indexes were being flagged as no longer present and were therefore not being compressed. This was because the existence check I used, supplying an ID to OBJECT_ID() and checking for a NULL value, will always return a null for non-clustered indexes. This is because OBJECT_ID can only find objects that are stored in the sys.objects system table and non-clustered indexes are not stored there. So I modified the routine to check for the existence of non-clustered indexes by using the sys.indexes table. Corrected code is below. Thanks again to cp for pointing this out in the comments here.

Note this script will generate an error if you have an index containing a timestamp column. The issue is a bug with Microsoft's sp_estimate_data_compression_savings procedure, as detailed here. This has been fixed in SQL 2012, but the bug is still present in SQL 2008 and SQL 2008 R2.  I've decided not to modify the code to look for this case, as it seems to be fairly rare and it has been fixed in the latest version. Additionally, the index with the timestamp can still be compressed, it's just the estimating procedure that fails. If you are running into this problem, I suggest either modifying the code below to exclude those indexes. If you don't want to get into coding that (and I'll admit that I don't), I suggest manually compressing those indexes. Once they are compressed, this code will no longer pick them up and the errors will no longer be generated. Thanks once again to cp for finding this issue as well.

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)

*/
 
      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
               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
      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'';'
                                             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'';'
                                             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
                                    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
                           PRINT 'Max runtime reached. No compression performed. Compression estimate still processing. Exiting...';
                           CLOSE cCompress
                           DEALLOCATE cCompress
                           DROP TABLE ##ObjEst
                           RETURN
                     END
            END -- end while loop
 
      CLOSE cCompress
      DEALLOCATE cCompress
 
      PRINT 'Initial scan complete.'
         --END
  /* End evaluating compression savings. Now do the actual compressing. */
 
      PRINT 'Beginning compression.';
 
  /* Do not process objects that do not meet our compression criteria */
 
      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;
 
                                             PRINT 'Compressing table/index: '
                                                   + @Schema + '.' + @object
                                                   + ' in database '
                                                   + @DatabaseName;
                                             EXEC sp_executesql
                                                @SQL;
 
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    AlreadyProcessed = 1
                                                   ,StatusText = 'Compression complete'
                                             WHERE  PK = @PK;
 
                                             SET @CompressedCount = @CompressedCount
                                                 + 1;
                                       END
                                    ELSE
                                       BEGIN
                                             UPDATE dbo.dbCompressionEstimates
                                             SET    AlreadyProcessed = 1
                                                   ,StatusText = 'Object no longer exists at compression stage'
                                             WHERE  PK = @PK;
                                       END
                              END
                           ELSE
                              BEGIN
                                    PRINT 'Command execution not enabled. Command is:'
                                          + @SQL;
                              END
                     END
                  ELSE
                     BEGIN
                           PRINT 'Max runtime reached. Some compression performed. Exiting...';
                           CLOSE cCompress
                           DEALLOCATE cCompress
                           DROP TABLE ##ObjEst
                           BREAK
                     END
 
                  FETCH cCompress INTO @DatabaseName, @Schema, @object,
                        @partNO, @ixName, @ixType, @Recommended_Compression,
                        @PK;
            END
 
      CLOSE cCompress;
      DEALLOCATE cCompress;
 
      PRINT 'Objects compressed: ' + CONVERT(VARCHAR(10), @CompressedCount);
      DROP TABLE ##ObjEst
      RETURN
Share

Brent Ozar Unlimited had a post recently noting that it appears Microsoft has seemed to move away from releasing service packs for SQL Server and instead is only releasing cumulative updates. Given that Microsoft itself warns against installing cumulative updates unless it solves a specific problem you are encountering, most DBAs, including myself, are somewhat reluctant to install them, preferring to wait for the (supposedly) more thoroughly tested service packs.

A recent Steve Jones editorial has picked up the call for service packs and includes links to Microsoft Connect items to vote for releasing service packs for SQL 2012, 2008 R2, and 2008. Please take a moment and vote for these.

Release SQL Server 2012 Service Pack 2

Release SQL Server 2008 R2 Service Pack 3

Release Final SQL Server 2008 and 2008 R2 Service Packs

 

Share