In absence of a good script for Database compression that includes,
- Error handlings of all and any unforeseen kind
- Avoidance of “Sparse columns”
- Not attempting on already compressed objects
- Attempt Row compression if Page compression fails
- Proceeding in case of errors without human intervention so the large database compression can be left to happen
I have come up with the following,
/* Database compression utility Script runs in current database context and compresses all tables and indexes. Preferred method is Page compression. Features over traditional compression - Error handlings of all and any unforeseen kind - Avoidance of "Sparse column" containing tables and indexes - Not attempting on already compressed objects - Attempt Row compression if Page compression fails - Proceeding in case of errors without human intervention so the large database compression can be left to happen - Progress reporting sz_zaidi@hotmail.com */ --use declare @DatabaseName nvarchar(100) declare @DatabaseSize numeric(20,10) Declare @objectname as nvarchar(1000) declare @objecttype nvarchar(100) Declare @tsqltxt nvarchar(1000) Declare @tsqltxtFinished nvarchar(1000) declare @IndexName nvarchar(100) declare @fetch_outer_cursor int declare @fetch_inner_cursor int Declare @Object2compresscount int -- Total objects to compress Declare @Objectcount int Set @Objectcount = 0 -- Progress reporting counter set @Object2compresscount = (select count(*) FROM [sys].[partitions] (NOLOCK) AS [p] INNER JOIN sys.tables (NOLOCK) AS [t] ON [t].[object_id] = [p].[object_id] INNER JOIN sys.indexes (NOLOCK) AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id] INNER JOIN sys.schemas (NOLOCK) AS s ON t.[schema_id] = s.[schema_id] where p.data_compression<>2 and t.name not in (SELECT distinct t.name from sys.tables (NOLOCK) t left join sys.columns (NOLOCK) c ON c.object_id = t.object_id WHERE c.is_sparse =1 )) Print 'Objects to Compress in the database =' + convert(varchar,@Object2compresscount ) + char(13) Declare curTblsIdxs cursor for SELECT distinct '[' + s.name + '].[' + [t].[name] + ']' AS [Object], '[' + [i].[name] + ']' AS [Index], case when p.index_id > 1 then 'Index' when p.index_id <=1 then 'Table' end ObjectType --, p.data_compression_desc --,p.data_compression FROM [sys].[partitions] (NOLOCK) AS [p] INNER JOIN sys.tables (NOLOCK) AS [t] ON [t].[object_id] = [p].[object_id] INNER JOIN sys.indexes (NOLOCK) AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id] INNER JOIN sys.schemas (NOLOCK) AS s ON t.[schema_id] = s.[schema_id] where p.data_compression<>2 and t.name not in (SELECT distinct t.name from sys.tables (NOLOCK) t left join sys.columns (NOLOCK) c ON c.object_id = t.object_id WHERE c.is_sparse =1 ) open curTblsIdxs Fetch next from curTblsIdxs into @objectname,@Indexname , @objecttype set @fetch_inner_cursor = @@FETCH_STATUS while @fetch_inner_cursor = 0 BEGIN set @Objectcount = @Objectcount + 1 set @tsqltxt = '' set @tsqltxtFinished = '' if @objecttype = 'Table' set @tsqltxt = 'ALTER ' + @objecttype + ' ' + @objectname if @objecttype = 'Index' set @tsqltxt = 'ALTER ' + @objecttype + ' ' + @IndexName + ' On ' + @objectname Begin try --'print char(13) + @Tablename + ' , Trying page compression.'+ char(13) set @tsqltxtFinished = @tsqltxt + ' REBUILD WITH (DATA_COMPRESSION=PAGE);' EXECUTE sp_executesql @tsqltxtFinished --print @tsqltxt+ char(13) print '(@Objectcount:' + convert(varchar,@Objectcount) + ') ' + @objecttype + ': ' + @objectname + ' page compressed.'+ char(13) End try begin catch print char(13) + 'Error: ' + @objecttype + ' ' + @objectname + ' Not compressed with page '+ char(13) + ERROR_MESSAGE() --+ char(13) + @tsqltxtFinished set @tsqltxtFinished = '' Begin try set @tsqltxtFinished = @tsqltxt + ' REBUILD WITH (DATA_COMPRESSION=ROW);' EXECUTE sp_executesql @tsqltxtFinished print '(@Objectcount:' + convert(varchar,@Objectcount) + ') ' + @objecttype + ': ' + @objectname + ' Row compressed.'+ char(13) --print @tsqltxt+ char(13) End try Begin Catch print char(13) + 'Error: (@Objectcount:' + convert(varchar,@Objectcount) + ') ' + @objecttype + ' ' + @objectname + ' Not compressed with page or row.'+ char(13) + ERROR_MESSAGE() + char(13) + @tsqltxtFinished set @tsqltxtFinished = '' End Catch; end catch; Fetch next from curTblsIdxs into @objectname,@Indexname , @objecttype set @fetch_inner_cursor = @@FETCH_STATUS End Close curTblsIdxs Deallocate curTblsIdxs