Tuesday, April 24, 2012

Delete large amount of records without interrupting other processes and without creating huge log

Business case

A customer wants to regularly delete older data from a table. There is a date on which to select data, large amounts (more than a million) of records will be deleted at once, the deletion should not interrupt other processes on the same database, the transaction log should not grow too long.

Solution

Truncate table is not an option, because only records older than a certain date need to be deleted.

Split the delete process in chunks with a while loop, smaller sets of records are deleted in every iteration of the loop, at the end of each iteration there is a wait to allow other processes to continue.

SQL Script

DECLARE @DeleteChunk int;
SET @DeleteChunk = 5000;    -- the number of records to delete in one chunk
 
DECLARE @DisposableDate smalldatetime;
SET @DisposableDate ='2012-04-15';  -- the date to compare against for older data
 
-- WHILE EXISTS will return true as soon as a single record is found, so not too slow
WHILE EXISTS(SELECT * FROM dbo.MyTable WHERE DisposableDate >= @DisposableDate) 
    BEGIN
        DELETE 
        FROM dbo.MyTable
        WHERE MyTableID IN 
            (SELECT TOP(@DeleteChunk) MyTableID        -- select TOP 5000 records at a time
             FROM dbo.MyTable WITH (NOLOCK)
             WHERE DisposableDate >= @DisposableDate);
             
        WAITFOR DELAY '00:00:00:25';    -- give other processes time to act
    END

2 comments:

Unknown said...

louis vuitton outlet, sac longchamp, ralph lauren pas cher, replica watches, louboutin outlet, louboutin shoes, christian louboutin outlet, prada outlet, ugg boots, longchamp pas cher, tiffany and co, louis vuitton, louboutin, air jordan pas cher, tory burch outlet, cheap oakley sunglasses, nike outlet, polo ralph lauren outlet, ray ban sunglasses, polo ralph lauren outlet, louis vuitton outlet, nike air max, replica watches, louis vuitton, air max, longchamp outlet, michael kors, oakley sunglasses, chanel handbags, nike free, nike roshe run, oakley sunglasses, burberry, tiffany jewelry, ray ban sunglasses, kate spade outlet, prada handbags, oakley sunglasses, nike air max, louis vuitton, louboutin pas cher, uggs on sale, ray ban sunglasses, oakley sunglasses, longchamp, jordan shoes, gucci outlet, ugg boots, nike free, longchamp outlet

Unknown said...

hollister, louis vuitton, canada goose outlet, moncler, pandora jewelry, swarovski, louis vuitton, bottes ugg, coach outlet, moncler, supra shoes, montre pas cher, moncler, ugg,uggs,uggs canada, ugg,ugg australia,ugg italia, canada goose, links of london, pandora jewelry, karen millen, doudoune canada goose, juicy couture outlet, moncler, pandora charms, marc jacobs, swarovski crystal, moncler, ugg pas cher, thomas sabo, louis vuitton, moncler outlet, moncler, canada goose, canada goose uk, canada goose outlet, ugg boots uk, juicy couture outlet, wedding dresses, moncler, canada goose, toms shoes, louis vuitton, replica watches, sac louis vuitton pas cher, pandora charms, canada goose