Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
Script to identify the oldest partition w/ data and swap it out
</code><code>
1. identify partition
2. script out target table (1 time?)
3. ALTER TABLE audit.ServiceUserAction SWITCH PARTITION # TO
3. truncate table
*/
DECLARE @part_num INT
, @sqlcmd VARCHAR(MAX)
, @rngValue VARCHAR(50);
WHILE EXISTS ( SELECT DISTINCT sp.partition_number
, rows
, CAST(sprv.value AS DATETIME2)
FROM sys.partitions sp
INNER JOIN sys.indexes si ON si.index_id = sp.index_id
AND si.[object_id] = sp.[object_id]
INNER JOIN sys.data_spaces sds ON sds.data_space_id = si.data_space_id
INNER JOIN sys.partition_schemes sps ON sps.data_space_id = sds.data_space_id
INNER JOIN sys.partition_functions spf ON spf.function_id = sps.function_id
/* Join partition range values (RANGE Right means p number - 1 = boundary ID */
LEFT OUTER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id
AND sprv.boundary_id = sp.partition_number - 1
WHERE spf.name = N'pf_MonthlyWindow'
AND sp.rows > 0
AND CAST(sprv.value AS DATETIME2) < DATEADD(MONTH, -13, GETDATE()))
BEGIN
SELECT TOP 1 @part_num = sp.partition_number
, @rngValue = CONVERT(VARCHAR(30)
, CAST(sprv.value AS DATETIME2), 120)
FROM sys.partitions sp
INNER JOIN sys.indexes si ON si.index_id = sp.index_id
AND si.[object_id] = sp.[object_id]
INNER JOIN sys.data_spaces sds ON sds.data_space_id = si.data_space_id
INNER JOIN sys.partition_schemes sps ON sps.data_space_id = sds.data_space_id
INNER JOIN sys.partition_functions spf ON spf.function_id = sps.function_id
/* Join partition range values (RANGE Right means p number - 1 = boundary ID */
LEFT OUTER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id
AND sprv.boundary_id = sp.partition_number - 1
WHERE spf.name = N'pf_MonthlyWindow'
AND sp.rows > 0
AND CAST(sprv.value AS DATETIME2) < DATEADD(MONTH, -13, GETDATE()) -- 1 year was our agreed upon retention date
ORDER BY sp.partition_number;
SELECT @sqlcmd = 'ALTER TABLE dbo.verybusytable SWITCH PARTITION '
+ CAST(@part_num AS VARCHAR(3)) + ' TO dbo.truncatepartition;';
PRINT @sqlcmd;
PRINT 'Merged range value: ' + @rngValue + '.';
EXEC (@sqlcmd);
/* kill swapped out records: */
TRUNCATE TABLE dbo.truncatepartition;
END;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment