Script for compressing a database

When you have to compress a database you’d better first see the consideration for the compression.

This is a script that I use for compressing databases. Of course you can make changes if you want to add or remove something.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_compress_database]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_compress_database]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_compress_database]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_compress_database] AS' 
END
GO

ALTER PROCEDURE [dbo].[sp_compress_database] (@dbname nvarchar(100), @compression_type varchar(10), @mode bit)
AS
/*

  Description: This stored procedure can compress a database or list out estimations for the savings from possible compression. 
  Create the stored procedure on database level.
  
  Call:        
    USE AdventureWorksDW2014;
    GO

    EXEC dbo.sp_compress_database 
    @dbname = 'AdventureWorksDW2014',           -- Database to compress
    @compression_type = 'PAGE',                 -- Compression type: PAGE, ROW or NONE
    @mode = 1;                                  -- Mode can be: 1 -> compression and 0 -> estimation for compression

  Author: Igor Micev
  Date: 2012-10-25 

*/BEGIN
  SET NOCOUNT ON;
  IF OBJECT_ID('tempdb..#tables_for_compression') IS NOT NULL
    DROP TABLE #tables_for_compression;

  CREATE TABLE #tables_for_compression (
    [id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [object_name] varchar(100),
    [schema_name] varchar(20),
    [index_id] int,
    [partition_number] int,
    [size_before_compression_KB] int,
    [size_after_compression_KB] int,
    [sample_size_before_compression_KB] int,
    [sample_size_after_compression_KB] int
  );

  IF @compression_type NOT IN ('PAGE', 'ROW', 'NONE')
  BEGIN
    RAISERROR ('Compression type is not valid.', 16, 1);
    RETURN;
  END;

  DECLARE @dynamic_cmd nvarchar(1000);
  DECLARE @tbl nvarchar(100);
  DECLARE @schema nvarchar(20);
  DECLARE tbl_cursor CURSOR FOR
  SELECT
    isc.TABLE_NAME,
    isc.TABLE_SCHEMA
  FROM INFORMATION_SCHEMA.TABLES AS isc
  WHERE isc.TABLE_CATALOG = @dbname
  AND isc.TABLE_TYPE = 'BASE TABLE';

  OPEN tbl_cursor;
  FETCH NEXT FROM tbl_cursor INTO @tbl, @schema;

  --Examine the clustered indexes
  WHILE @@fetch_status = 0
  BEGIN
    IF @mode = 1
    BEGIN
      SET @dynamic_cmd = 'USE [' + @dbname + '] ALTER TABLE [' + @schema + '].[' + @tbl + '] 
            REBUILD PARTITION = ALL WITH (ONLINE = ON, DATA_COMPRESSION = ' + @compression_type + ')';
    END;

  BEGIN TRY
    IF @mode = 0
    BEGIN
      SET @dynamic_cmd = 'EXEC sp_estimate_data_compression_savings ''' + @schema + ''', ''' + @tbl + ''', NULL, NULL,''' + @compression_type + '''';
      INSERT INTO #tables_for_compression ([object_name], [schema_name], index_id, partition_number, size_before_compression_KB, size_after_compression_KB, sample_size_before_compression_KB, sample_size_after_compression_KB)
      EXEC sp_executesql @dynamic_cmd;
    END;

    IF @mode = 1
    BEGIN
      EXEC sp_executesql @dynamic_cmd;
      PRINT @schema + '.' + @tbl + ' was compressed.';
    END;
  END TRY
  BEGIN CATCH
    PRINT 'Failed command: ' + @dynamic_cmd;
  END CATCH;
    FETCH NEXT FROM tbl_cursor INTO @tbl, @schema;
  END;

  CLOSE tbl_cursor;
  DEALLOCATE tbl_cursor;

  --Examine the nonclustered indexes. Exclude XML type indexes.
  IF @mode = 1
  BEGIN
    DECLARE @ind_name nvarchar(100);
    DECLARE ncix CURSOR FOR
    SELECT
      ss.name AS [schema],
      OBJECT_NAME(ddips.object_id) AS table_name,
      si.name AS index_name
    FROM sys.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ddips
    JOIN sys.indexes AS si ON ddips.index_id = si.index_id AND ddips.object_id = si.object_id
    JOIN sys.tables AS st ON ddips.object_id = st.object_id
    JOIN sys.schemas AS ss ON st.schema_id = ss.schema_id
    WHERE si.index_id > 1 AND si.[type] = 2 AND ddips.page_count > 64;
    
OPEN ncix;
    FETCH NEXT FROM ncix INTO @schema, @tbl, @ind_name;

    WHILE (@@fetch_status = 0)
    BEGIN
      SET @dynamic_cmd = 'ALTER INDEX ' + @ind_name + ' ON ' + @schema + '.' + @tbl + ' 
            REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = ' + @compression_type + ')';
    BEGIN TRY
      EXEC sp_executesql @dynamic_cmd;
      PRINT 'Index ' + @ind_name + ' was compressed.';
    END TRY
    BEGIN CATCH
      PRINT 'Index ' + @ind_name + ' cannot be compressed. Err.Msg: ' + @@error;
    END CATCH
      FETCH NEXT FROM ncix INTO @schema, @tbl, @ind_name;
    END;

    CLOSE ncix;
    DEALLOCATE ncix;
  END

  IF @mode = 0
    SELECT *
    FROM #tables_for_compression;

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

END
GO