Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
Script to remove empty partitions older than 15 months (except partition 1 because we need at least one empty partition)
*/
DECLARE @mergedate DATETIME2
, @sqlcmd VARCHAR(MAX);
CREATE TABLE #mergedates ( m_date DATETIME2 );
INSERT INTO #mergedates ( m_date )
SELECT CAST(sprv.value AS DATETIME2)
FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id
WHERE spf.name = N'pf_MonthlyWindow'
AND CAST(sprv.value AS DATETIME2) < DATEADD(MONTH, -15, GETDATE())
AND sprv.boundary_id 1;
DECLARE curr_dates CURSOR FOR
SELECT m_date
FROM #mergedates
ORDER BY m_date ASC;
OPEN curr_dates;
FETCH NEXT FROM curr_dates INTO @mergedate;
WHILE @@FETCH_STATUS = 0
BEGIN
/* merge old partitions */
SELECT @sqlcmd = 'ALTER PARTITION FUNCTION pf_MonthlyWindow() MERGE RANGE ('''
+ CONVERT(VARCHAR(30), @mergedate, 120) + ''');';
PRINT @sqlcmd;
EXEC (@sqlcmd);
FETCH NEXT FROM curr_dates INTO @mergedate;
END;
CLOSE curr_dates;
DEALLOCATE curr_dates;
DROP TABLE #mergedates;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment