Auto compression all indexs on all online db

,

To add sheduler for running job.

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dba_indexCompression](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Script] [nvarchar](max) NOT NULL
) ON [PRIMARY]

GO




USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Description:	Auto index compression
-- =============================================
ALTER PROCEDURE [dbo].[Index_Compression]
AS
BEGIN

	SET NOCOUNT ON;
DECLARE @sql nvarchar (max);
DECLARE @db nvarchar(50);
DECLARE @cnt INT;
	IF (object_id('tempdb..#ContrDecomp') IS NOT NULL) DROP TABLE #ContrDecomp
	IF (object_id('tempdb..#Scripts') IS NOT NULL) DROP TABLE #Scripts
CREATE TABLE #ContrDecomp ([DBName] varchar (256),[SchemaName] varchar (256),[TableName] varchar (256), [IndexName] varchar(512), [PartitionNumber] int, [DataCompression] varchar (32), [RowCount] int)
CREATE TABLE #Scripts (Scripts varchar(max))
	
DECLARE db_online INSENSITIVE CURSOR FOR 
		(SELECT [name] FROM sys.database_mirroring M
			inner join sys.databases DB
				ON M.database_id=DB.database_id
		WHERE name not in ('tempdb', 'model') AND db.source_database_id IS NULL
		AND state_desc = 'ONLINE')
	FOR  READ ONLY 
	OPEN db_online
	FETCH NEXT FROM db_online INTO @db
WHILE @@FETCH_STATUS = 0
	BEGIN

SET @sql = 'INSERT INTO #ContrDecomp
SELECT '+''''+@db+''''+',
	   sch.name as SchemaName,
	   tbl.name as TableName,
       i.name as IndexName,
       p.partition_number AS [PartitionNumber],
       p.data_compression_desc AS [DataCompression],
       p.rows  AS [RowCount]
  FROM ['+@db+'].sys.tables AS tbl
  LEFT JOIN ['+@db+'].sys.indexes AS i ON (i.index_id > 0 AND i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
  INNER JOIN ['+@db+'].sys.partitions AS p ON p.object_id = CAST(tbl.object_id AS int) AND p.index_id = CAST(i.index_id AS int)
  INNER JOIN ['+@db+'].sys.schemas sch ON tbl.schema_id=sch.schema_id
  WHERE p.data_compression_desc <> ''PAGE'' AND
        p.rows >= 1000000
  ORDER BY p.rows DESC, 3'
  
  	EXEC (@sql)
		FETCH NEXT FROM db_online INTO @db
		
	END
	CLOSE db_online
	DEALLOCATE db_online
	
	TRUNCATE TABLE [master].[dbo].[dba_indexCompression]
	INSERT INTO [master].[dbo].[dba_indexCompression] (Script)
	SELECT 'ALTER INDEX ['+cd.IndexName+'] ON ['+cd.DBName+'].['+cd.SchemaName+'].['+cd.TableName+'] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )' as [Script] FROM #ContrDecomp Cd
	DROP TABLE #ContrDecomp
	
	
	SET @cnt = (SELECT MAX([Id]) FROM [master].[dbo].[dba_indexCompression])
	IF @cnt = 0 RETURN
	ELSE
	WHILE @cnt <> 0
	BEGIN
	 BEGIN TRY
		 SET @sql = (SELECT [Script] FROM [master].[dbo].[dba_indexCompression] WHERE [id] = @cnt)
		 SET @cnt = @cnt -1
		 EXEC (@sql)
	 END TRY
	 BEGIN CATCH
		 SET @sql = (SELECT  REPLACE(@sql, 'ONLINE = ON', 'ONLINE = OFF' ))
		 SET @cnt = @cnt -1
		 EXEC (@sql)
	 END CATCH
	 END

END

Rate

3 (2)

Share

Share

Rate

3 (2)