Purging table script

chitranjanrana, 2018-09-26

Description of script is self explanatory and has been tested many times in production

    DECLARE @intMthsToKeep int
DECLARE @time_cutoff datetime
–declare in months how much data you want to keep
SET @intMthsToKeep = 1
SET @time_cutoff = DATEADD( Month, -@intMthsToKeep, getdate())
— check for timestamp details before running the actual delete statement
print @time_cutoff
–deleting in batches of 500
WHILE 1=1
BEGIN
DELETE TOP (500) FROM [schemaname].[tablename1] WHERE [Sch_Datetime] < @time_cutoff
DELETE TOP (500) FROM [schemaname].[tablename2] WHERE [Sch_Datetime] < @time_cutoff
—Check for exit condition
IF @@rowcount <500 break
END

Rate

Share

Share

Rate