Deleting millions of rows from SQL Server
So, you've found large tables and now you want to trim them down to size... Not drop them them, just delete most of the rows, using some conditions.
If you run a:
DELETE FROM SOMETABLE
WHERE "SOME_CONDITION"
...you'll be waiting a long time.
As it alleges in the comments at the source:
Any delete operation will causing locking - on the row level. But once your transaction has more than 5000 row-level locks, SQL Server will do a lock escalation and lock the entire table in exclusive mode
—marc_s
So this uses Delete top(@batchsize) ... in a while loop to do the needful.
DECLARE
@BATCHSIZE INT,
@WAITFORVAL VARCHAR(8),
@ITERATION INT,
@TOTALROWS INT,
@MAXRUNTIME VARCHAR(8),
@BSTOPATMAXTIME BIT,
@MSG VARCHAR(500),
@STARTALL DATETIME,
@STARTBATCH DATETIME,
@ENDBATCH DATETIME
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000;
SET @WAITFORVAL = '00:00:10' -- Delay between deletes -- UPDATE THIS
SET @MAXRUNTIME = '08:00:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS
SET @STARTALL = GetDate()
WHILE @BATCHSIZE>0
BEGIN
-- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
IF @BSTOPATMAXTIME=1 AND CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME
BEGIN
RETURN
END
SET @STARTBATCH = GetDate()
/* vv You need to update this!*/
DELETE TOP(@BATCHSIZE)
FROM SOMETABLE
WHERE SOMECONDITION
/* ^^ You need to update that */
SET @BATCHSIZE=@@ROWCOUNT
SET @ENDBATCH = GETDATE()
SET @ITERATION=@ITERATION+1
SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
RAISERROR (@MSG, 0, 1) WITH NOWAIT
SET @MSG = 'Batch duration: ' +
Cast(DATEDIFF(s, @STARTBATCH, @ENDBATCH) as varchar) +
' seconds. Batch Speed ' + CAST(CAST(@BATCHSIZE as float)/(DATEDIFF(s, @STARTBATCH, @ENDBATCH)) AS VARCHAR) +
' rows per seconds. Subtotal elapsed: ' +
CAST(DATEDIFF(s, @STARTALL, @ENDBATCH) as varchar) +
' seconds. Cumulative Speed ' + CAST(CAST(@TOTALROWS as float)/(DATEDIFF(s, @STARTALL, @ENDBATCH)) AS VARCHAR) +
' rows per second'
RAISERROR (@MSG, 0, 1) WITH NOWAIT
WAITFOR DELAY @WAITFORVAL
END
SET @MSG = 'TOTAL elapsed: ' +
CAST(DATEDIFF(s, @STARTALL, @ENDBATCH) as varchar) +
' seconds. Total rows: ' + CAST(@TOTALROWS AS VARCHAR) +
'. Final Speed: ' + CAST(CAST(@TOTALROWS as float)/(DATEDIFF(s, @STARTALL, @ENDBATCH)) AS VARCHAR) +
' rows per second'
RAISERROR (@MSG, 0, 1) WITH NOWAIT