Date-based table purge
I'm using dbWarden as monitoring solution.The only problem I get is that if you let it run, it'll grow and there is no provided procedure to purge old data.
So I dit one that is generic and reusable.
As usual, it's not given with any insurance about the result, test it first.
Feel free to contact me if you have ideas for improvements.
This procedure takes in charge the clean-up for a given table based on a date field
given as parameter and a retention expressed as a number of days.
If the number of records to delete is more than a configurable limit, a dichotomic
delete will be performed so that there is no matter on big open transaction without
commit.
ARGUMENTS :
@TableOwner name of the owner of the table to clean up
@TableName name of the table to clean up
@DateFieldName name of the date field to use for clean up
@retentionDays number of days to keep track
@maxRowCnt maximal number of rows to consider in an open transaction.
If the cleanup needs more than that number, the query will be cut
into pieces implying maximum @maxRowCnt
@debug if set to 1, this enables the debug mode
/**
Usage example :
==============
exec dbo.Proc4HistoryTableCleanup
@TableOwner = 'dbo',
@TableName = 'CPUStatsHistory',
@DateFieldName = 'DateStamp',
@retentionDays = 700,
@debug = 1
exec dbo.Proc4HistoryTableCleanup
@TableOwner = 'dbo',
@TableName = 'CPUStatsHistory',
@DateFieldName = 'DateStamp',
@retentionDays = 300,
@debug = 1
*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc4HistoryTableCleanup]'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Proc4HistoryTableCleanup] ( ' +
' @TableOwnervarchar(64),' +
' @TableName varchar(512), ' +
' @DateFieldName varchar(50), ' +
' @retentionDays INT = 0,' +
' @Debug TINYINT = 0' +
') ' +
'AS ' +
'BEGIN ' +
' SELECT ''Not implemented'' ' +
'END')
END
GO
ALTER PROCEDURE [dbo].[Proc4HistoryTableCleanup] (
@TableOwnerSYSNAME,
@TableName SYSNAME,
@DateFieldName SYSNAME,
@retentionDaysINT,
@maxRowCntINT = 10000,
@debug TINYINT = 0
)
AS
/*
===================================================================================
DESCRIPTION:
This procedure takes in charge the clean-up for a given table based on a date field
given as parameter and a retention expressed as a number of days.
If the number of records to delete is more than a configurable limit, a dichotomic
delete will be performed so that there is no matter on big open transaction without
commit.
ARGUMENTS :
@TableOwnername of the owner of the table to clean up
@TableName name of the table to clean up
@DateFieldNamename of the date field to use for clean up
@retentionDaysnumber of days to keep track
@maxRowCntmaximal number of rows to consider in an open transaction.
If the cleanup needs more than that number, the query will be cut
into pieces implying maximum @maxRowCnt
@debugif set to 1, this enables the debug mode
REQUIREMENTS:
==================================================================================
BUGS:
BUGID Fixed Description
========== ===== ==========================================================
----------------------------------------------------------------------------------
==================================================================================
NOTES:
AUTHORS:
. VBO Vincent Bouquette (vincent.bouquette@chu.ulg.ac.be)
. BBO Bernard Bozert (bernard.bozet@chu.ulg.ac.be)
. JEL Jefferson Elias (jelias@chu.ulg.ac.be)
COMPANY: CHU Liege
==================================================================================
Revision History
Date Nom Description
========== ===== ==========================================================
18/11/2014 JEL Version 0.1.0
----------------------------------------------------------------------------------
16/12/2014JEL Changed VARCHAR to SYSNAME according to dav0id's suggestion
VERSION 0.1.1
----------------------------------------------------------------------------------
===================================================================================
*/
BEGIN
--SET NOCOUNT ON;
DECLARE @versionNb varchar(16) = '0.1.0';
DECLARE @tsql nvarchar(max);-- text to execute via dynamic SQL
DECLARE @tmpRetentionDays INT;-- number of retention days to remove
DECLARE @retentionInc INT;-- increment for tmpRetentionDays
BEGIN TRY
--
-- check parameter :
-- retention days
--
if @retentionDays < 0
BEGIN
RAISERROR ('Parameter "retentionDays" is negative. Avorting execution', 10,1,@retentionDays);
END
if @retentionDays = 0
return
--
-- check parameters
-- table-related parameters are usable ?
--
IF(NOT EXISTS (
SELECT 1
FROM
information_schema.COLUMNS
where
TABLE_CATALOG= DB_NAME(DB_id()) -- in current database
AND TABLE_SCHEMA = @TableOwner
AND TABLE_NAME = @TableName
and COLUMN_NAME = @DateFieldName
))
BEGIN
RAISERROR('Parameters about the table are unusable in current database ', 10,1,@TableOwner, @TableName ,@DateFieldName )
END
if @debug = 1
BEGIN
PRINT '----------------------------------------------------'
PRINT OBJECT_NAME(@@PROCID)
PRINT '===================================================='
PRINT 'Owner = ' + @TableOwner
PRINT 'TableName = ' + @TableName
PRINT 'Column = ' + @DateFieldName
PRINT 'Retention = ' + CONVERT (VARCHAR ,@retentionDays)
PRINT 'Max Row Nb = ' + CONVERT (VARCHAR ,@maxRowCnt)
PRINT '----------------------------------------------------'
PRINT CHAR(10)
END
--
-- Iteration number evaluation
--
DECLARE @dateThresh DATETIME
SET @dateThresh = GETDATE() - @retentionDays
DECLARE @dateThreshStr VARCHAR(100)
SELECT @dateThreshStr = convert(varchar(100),@dateThresh,112)
DECLARE @totalRecordNb BIGINT
DECLARE @MinDateFieldVal DATETIME
SET @tsql = N'SELECT ' + CHAR(10) +
' @totalRecNb = COUNT_BIG(*),' + CHAR(10) +
' @minDate = MIN([' + @DateFieldName + '])' + CHAR(10) +
'FROM' + CHAR(10) +
' [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
'WHERE' + CHAR(10) +
' ' + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'
execute sp_executesql @tsql, N'@totalRecNb BIGINT OUTPUT, @minDate DATETIME OUTPUT,@dateThresh VARCHAR(100)', @minDate = @MinDateFieldVal OUTPUT, @totalRecNb = @totalRecordNb OUTPUT, @dateThresh = @dateThreshStr
if @debug = 1
BEGIN
PRINT 'Min Date in table : ' + CONVERT (VARCHAR ,@MinDateFieldVal,112)
PRINT 'Number of records : ' + CONVERT (VARCHAR ,@totalRecordNb)
END
if(@totalRecordNb > @maxRowCnt)
BEGIN
-- divide by 2 the number of days between min date and the retention period
DECLARE @nbOfDaysToAdd INT
SELECT @nbOfDaysToAdd = (DATEDIFF(day,@MinDateFieldVal,@dateThresh) / 2)
if @debug = 1
BEGIN
PRINT 'Too many records to take care at once !'
PRINT 'Days to play with : ' + CONVERT (VARCHAR ,@nbOfDaysToAdd)
END
-- TODO : if days is not enough => play with hours !
SET @tmpRetentionDays = @retentionDays+@nbOfDaysToAdd
if @debug = 1
BEGIN
PRINT 'New retention : ' + convert(varchar,@tmpRetentionDays)
END
exec [dbo].[Proc4HistoryTableCleanup] @TableOwner=@TableOwner,@TableName=@TableName,@DateFieldName=@DateFieldName,@retentionDays=@tmpRetentionDays,@maxRowCnt=@maxRowCnt,@debug=@debug
if @debug = 1
BEGIN
PRINT 'Restarting procedure with the previous retention :' + CONVERT (varchar,@retentionDays)
END
execute [dbo].[Proc4HistoryTableCleanup] @TableOwner=@TableOwner,@TableName=@TableName,@DateFieldName=@DateFieldName,@retentionDays=@retentionDays,@maxRowCnt=@maxRowCnt,@debug=@debug
END
ELSE
BEGIN
if @debug = 1
BEGIN
PRINT 'Deleting ' + CONVERT (varchar,@totalRecordNb) + ' records'
END
SET @tsql = 'DELETE' + CHAR(10) +
'FROM' + CHAR(10) +
' [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
'WHERE' + CHAR(10) +
' ' + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'
execute sp_executesql @tsql, N'@dateThresh VARCHAR(100)', @dateThresh = @dateThreshStr
if @debug = 1
BEGIN
SET @tsql = N'SELECT ' + CHAR(10) +
' @totalRecNb = COUNT_BIG(*),' + CHAR(10) +
' @minDate = MIN([' + @DateFieldName + '])' + CHAR(10) +
'FROM' + CHAR(10) +
' [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
'WHERE' + CHAR(10) +
' ' + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'
execute sp_executesql @tsql, N'@totalRecNb BIGINT OUTPUT, @minDate DATETIME OUTPUT,@dateThresh VARCHAR(100)', @minDate = @MinDateFieldVal OUTPUT, @totalRecNb = @totalRecordNb OUTPUT, @dateThresh = @dateThreshStr
PRINT 'Nb of records after delete : ' + CONVERT (varchar,@totalRecordNb)
PRINT 'Minimum date after delete : ' + CONVERT(varchar,@MinDateFieldVal,112)
END
END
END TRY
BEGIN CATCH
PRINT 'ErrorNumber : ' + CONVERT (VARCHAR , ERROR_NUMBER())
PRINT 'ErrorSeverity : ' + CONVERT (VARCHAR , ERROR_SEVERITY())
PRINT 'ErrorState : ' + CONVERT (VARCHAR , ERROR_STATE())
PRINT 'ErrorProcedure : ' + CONVERT (VARCHAR , ERROR_PROCEDURE())
PRINT 'ErrorLine : ' + CONVERT (VARCHAR , ERROR_LINE())
PRINT 'ErrorMessage : ' + CONVERT (VARCHAR , ERROR_MESSAGE())
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
RAISERROR ('Unable to proceed !', 10,1,@retentionDays);
END CATCH
END
/**
Usage example :
==============
exec dbo.Proc4HistoryTableCleanup
@TableOwner= 'dbo',
@TableName= 'CPUStatsHistory',
@DateFieldName= 'DateStamp',
@retentionDays = 700,
@debug= 1
exec dbo.Proc4HistoryTableCleanup
@TableOwner= 'dbo',
@TableName= 'CPUStatsHistory',
@DateFieldName= 'DateStamp',
@retentionDays = 300,
@debug= 1
*/
--select * from dbo.CPUStatsHistory