SQL Server Utilities - Automate Partition Management Sliding Window

Working with partitions at different environments has led me to think that a generic unified code to manage partitions can be very useful and practical. Think about it, if you look at a code that manages a partition sliding window implemented by 10 different DBAs you are going to see 10 different variations of the same thing. It’s a little like reinventing the wheel over and over again.

A generic piece of code can be easier to maintain since you get to be familiar with the code and it cuts of the development time to 0.

No more development time needed but just a few moments of deployment.

This is the link to the GitHub project

Here are the Key features supported by the process that automates a partition sliding window:

1. Generic code for all partitions managed on all databases across all SQL Server instances

a. No development required
b. Quick and simple deployment
c. The process logs all operations to a table so in case of failures the exception is being logged allowing you to quickly understand what went wrong
d. Dynamic code that allows you to run in debug mode and get all the commands printed out while nothing gets executed
e. Managing a new partition function is as easy as adding a new row to the management table

2. Support various time boundaries for partitions:

a. Year
b. Quarter
c. Month
d. Week
e. Day
f. Hour

3. Support for partitioning columns of data types:

a. Datetime
b. Datetime2
c. Smalldatetime
d. Date
e. Int
f. Bigint

4. Switch out and merge old partitions

a. Based on a supplied parameter value of the number of days to keep data
b. An option to export the data to flat files using bcp (Bulk Copy utility) prior to the purge (truncate command) for archiving
c. An option to dynamically create an empty table for the switch out (for cases where an empty table cannot be created due to read only file groups, or to prevent failures due to schema inconsistency between source and target table while Switching Out)
d. Multiple objects on the same partition schema
e. Remove the data files no longer in use
f. Remove the file groups no longer in use

5. Split future partitions

a. Based on a supplied parameter value of number of partitions (the number of partitions to keep ready a head)
b. Add file groups
c. Add data files
d. Dynamically define new partition size based on the last partition size (excluding the current partition)
e. Use a single file group

General:

The main part of the code is the stored procedure sp_partition_managment which is marked as a system stored procedure. This stored procedure uses a few other procedures that are all created in the master database and are also marked as system stored procedures. The only exception is the script that uses UDFs (User Defined Functions) which cannot be created as system stored procedures and therefor needs to be deployed in every user database that has a partition function that you would like to be managed.

Deployment

Execute all sql scripts once in the master database except for the functions.sql script that is required in every database that has a partition function that needs to be managed.

I place the 2 tables used in this project (tables_schema.sql), the management table and the log table in a database called DBA but you can create these 2 tables at any other database.

This is a sample INSERT command to populate the dbo.PartitionsManagment table and this is all that is required to get you going

USE DBA;
--SELECT * FROM dbo.PartitionsManagment

INSERT	dbo.PartitionsManagment
(
  insert_time
 ,[database]
 ,partition_function
 ,days_to_keep_data
 ,num_future_partitions
 ,is_bcp
 ,bcp_path
 ,partition_boundry_unit
 ,create_empty_table
 ,create_file_group
 ,is_active
 ,file_group_physical_path
 ,is_split
 ,is_merge
)
VALUES
( 
	 GETDATE() -- insert_time - datetime
	,'my_db'   -- database - varchar(64)
	,'my_pf'   -- partition_function - varchar(64)
	,180       -- days_to_keep_data - int
	,30        -- num_future_partitions - int
	,0			-- is_bcp - bit
	,NULL      -- bcp_path - varchar(1024)
	,'Day'     -- partition_boundry_unit - varchar(10)
	,1			-- create_empty_table - bit
	,0			-- create_file_group - bit
	,1			-- is_active - bit
	,NULL       -- file_group_physical_path - varchar(1024)
	,1			-- is_split - bit
	,1			-- is_merge - bit
)

This is the bit of code that wraps the partition management process that you can stick in an SQL Server Agent job.

The file is named partition_management_loop.sql at the GitHub project

USE DBA;
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--SELECT * FROM dbo.PartitionsManagment 
--SELECT * FROM PartitionsMaintenanceLog 

DECLARE @debug bit = 1 --<---- Edite here

IF OBJECT_ID('tempdb.dbo.#errors', 'U') IS NOT NULL DROP TABLE dbo.#errors;
CREATE TABLE dbo.#errors(id int IDENTITY(1,1) PRIMARY KEY CLUSTERED, [partition_function] sysname NULL, [error_message] varchar(MAX) NULL );


IF OBJECT_ID('tempdb.dbo.#data', 'U') IS NOT NULL DROP TABLE #data;
CREATE TABLE dbo.#data
(
 id int IDENTITY(1, 1) PRIMARY KEY NOT NULL
,[insert_time] [datetime] NOT NULL
,[database] [varchar](64) NOT NULL
,partition_function sysname NOT NULL
,days_to_keep_data int NOT NULL
,num_future_partitions int NOT NULL
,is_bcp bit NOT NULL
,bcp_path varchar(1024) 
,partition_boundry_unit sysname NOT NULL
,create_empty_table bit NOT NULL
,create_file_group bit NOT NULL
,file_group_physical_path varchar(1024)
,is_merge bit NOT NULL
,is_split bit NOT NULL
);

INSERT dbo.#data
(
 insert_time
,[database]
,partition_function
,days_to_keep_data
,num_future_partitions
,is_bcp
,bcp_path
,partition_boundry_unit
,create_empty_table
,create_file_group
,file_group_physical_path
,is_merge
,is_split
)
SELECT 
 insert_time
,[database]
,partition_function
,days_to_keep_data
,num_future_partitions
,is_bcp
,bcp_path	
,partition_boundry_unit
,create_empty_table
,create_file_group
,file_group_physical_path
,is_merge
,is_split
FROM DBA.dbo.PartitionsManagment WHERE 1=1
AND is_active = 1
--AND partition_function = ''
ORDER BY id; 
--SELECT * FROM #data

DECLARE
	@database sysname
 ,@partition_function sysname
 ,@days_to_keep_data int 
 ,@num_future_partitions int 
 ,@file_group_physical_path varchar(1024)
 ,@is_bcp bit 
 ,@bcp_path varchar(1024) 
 ,@partition_boundry_unit sysname
 ,@create_empty_table bit 
 ,@create_file_group bit
 ,@is_merge bit
 ,@is_split bit;

DECLARE @command varchar(max), @min_id int = 1, @max_id int = (SELECT MAX(id) FROM dbo.#data);


WHILE @min_id <= @max_id
BEGIN;
	-- Parameters assignment
	SELECT
	  @database						= [database]
	 ,@partition_function			= partition_function
	 ,@file_group_physical_path		= ISNULL(file_group_physical_path, 'NONE'	)
	 ,@days_to_keep_data			= days_to_keep_data
	 ,@num_future_partitions		= num_future_partitions
	 ,@partition_boundry_unit		= partition_boundry_unit
	 ,@create_empty_table			= create_empty_table
	 ,@create_file_group			= create_file_group	
	 ,@is_bcp						= is_bcp
	 ,@bcp_path						= ISNULL(bcp_path,'NONE')
	 ,@is_merge						= is_merge
	 ,@is_split						= is_split
 FROM dbo.#data WHERE id = @min_id;
 
 PRINT CHAR(10) + '-- ' + @database + ' | ' + @partition_function --+ ' ' + @empty_table--+ ' ' + cast(@days_to_keep_data as sysname) + ' ' + cast(@num_future_partitions as sysname) + ' ' + @partition_boundry_unit;
 --select @table, @empty_table, @schema

-- Execute
SELECT @command = 'EXEC [' + @database + '].dbo.sp_partition_managment 
		 @partition_function			= ''' + @partition_function + '''
		,@days_to_keep_data				= '		+ CAST(@days_to_keep_data AS varchar(10)) + '
		,@num_future_partitions			= '		+ CAST(@num_future_partitions AS varchar(10))+ '
		,@file_group_physical_path		= '''	+ CAST(@file_group_physical_path AS varchar(150)) + '''
		,@is_bcp						= '		+ CAST(@is_bcp AS char(1))+ '
		,@bcp_path						= ''' + @bcp_path + '''
		,@partition_boundry_unit		= ''' + @partition_boundry_unit + '''
		,@create_empty_table			= '		+ CAST(@create_empty_table AS char(1))	+ '
		,@create_file_group				= '		+ CAST(@create_file_group AS char(1))+ '
		,@is_merge						= '		+ CAST(@is_merge AS char(1))	+ '
		,@is_split						= '		+ CAST(@is_split AS char(1))	+ '
		,@debug							= '		+ CAST(@debug AS char(1))	+ ';';

BEGIN TRY;
	PRINT 'USE [' + @database + ']';
	PRINT @command + CHAR(10);
	
	EXEC (@command);
END TRY
BEGIN CATCH;
	DECLARE @ErrorNumber int, @ErrorLine int, @Msg xml, @ErrorProc nvarchar(126), @CurrentTime datetime, @ErrorMessage nvarchar(2048),@ErrorSeverity INT, @ErrorState int; 
	SELECT  @ErrorMessage = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProc = ERROR_PROCEDURE();  

					IF @@TRANCOUNT > 0 ROLLBACK TRAN;
					
					SELECT @ErrorProc = ISNULL(@ErrorProc, CONVERT(NVARCHAR(126), OBJECT_NAME(@@PROCID)));
					SELECT @CurrentTime = CURRENT_TIMESTAMP;
  
					SELECT @Msg = (SELECT  @CurrentTime		AS 'EventTime'
																,@ErrorProc			AS 'ObjectName'
																,@ErrorNumber		AS 'Error/Number'
																,@ErrorMessage		AS 'Error/Message'
																,@ErrorSeverity		AS 'Error/Severity'
																,@ErrorState		AS 'Error/State'
																,@ErrorLine			AS 'Error/Line'
													FOR XML PATH('Event'));  

				PRINT	'  ***********  ' + CAST(@Msg AS nvarchar(max));

				INSERT #errors ([partition_function] ,[error_message]) SELECT @partition_function, @ErrorMessage;
END CATCH;

	SELECT @min_id += 1;
END;

--IF EXISTS(SELECT * FROM #errors) 	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine);  


-- If there were any failures we raise an error to "fail" the job step
IF EXISTS (SELECT * FROM #errors)
BEGIN;
	DECLARE @failed_partition_function varchar(max) = '';
	
	SELECT @failed_partition_function = @failed_partition_function + partition_function + ', '
	FROM dbo.#errors 
	ORDER BY id;

	RAISERROR (N'Failed on the following partition_functions: %s.', 16, 1, @failed_partition_function ); 
END;