Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save colegatron/894f9bc320edff9070b4 to your computer and use it in GitHub Desktop.
Save colegatron/894f9bc320edff9070b4 to your computer and use it in GitHub Desktop.
Bulk delete millions of old rows
-- Only procced with deletion if the there are at least @MINIMUM_IN_DB records for the last @NDAYS
DECLARE @NDAYS int = 3 -- Number of days to preserve
DECLARE @MININUM_IN_TABLE int = 2100000 -- Records needed in the DB for the last @NDAYS to proceed to deletion
DECLARE @TODELETE int = 1000 -- Delete un chunks of @TODELETE records
DECLARE @TOTALRECS int = ( select count(*) from intermediate where datediff(day, insertdate,getdate()) < @NDAYS )
IF ( @TOTALRECS >= @MININUM_IN_TABLE )
BEGIN
DECLARE @DELETED int = 0
SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
BEGIN
DELETE TOP (@TODELETE)
from intermediate
where datediff(day, insertdate,getdate()) > @NDAYS
SET @DELETED = @DELETED + @TODELETE
SELECT @DELETED
END
END
ELSE
SELECT 'Only ' + CONVERT(VARCHAR,@TOTALRECS) + ' or the last ' + CONVERT(nvarchar, @NDAYS) + 'days' AS 'INSUFICIENT RECORDS TO PROCEED'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment