Deleting Data The Slow Way

Rare is the time do we have to delete the data row by row. But…. Recently I needed to delete some xml fields in a database as they were no longer required. It was a massive amount of xml; the table itself was 50,000 rows and nearly 300gb, and I needed to delete roughly 9000 rows.

I decided to delete a row at a time. Whilst this contradicts how sql works, I did not want to fill up the log with one massive delete. And as the database has its log backed up every 15 minutes, this will ensure that there is free space in the log for new deletes. So we’ll delete a row every 2 minutes and check that there is space in the log before deleting. If the log is looking full we’ll check again in 5 minutes until there is space to run another delete. So I can run the script and leave it to run knowing that the script won’t kill the database.

The script is below with an example using AdventureWorksDW2014.



SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @id NVARCHAR (20)
DECLARE @pct FLOAT
DECLARE @s VARCHAR(1024)
DECLARE @startTime DATETIME

DECLARE cur CURSOR READ_ONLY FOR
SELECT X.SalesOrderNumber
FROM [dbo].[FactInternetSales] X
WHERE X.CurrencyKey =
(SELECT CurrencyKey
FROM [dbo].[DimCurrency]
WHERE CurrencyAlternateKey = 'FRF')
OPEN cur

FETCH NEXT FROM cur INTO @id
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- Get how much transaction log space there is
SET @startTime = GETDATE()
SELECT @pct = convert(decimal(38,8),round(100*(sf.size- CAST(fileproperty(sf.name,'SpaceUsed') as float))/128.000,8)/(sf.size/128.000))
from dbo.sysfiles sf
where sf.fileid  = 2
SELECT @s = (CONVERT( VARCHAR(24), @startTime, 121)) + ' : ' + CAST(@id AS VARCHAR) + ' : Txn used: ' + CAST(@pct AS VARCHAR)
RAISERROR(@s, 0, 1) WITH NOWAIT

-- if not enough left then wait until there is
WHILE @pct < 25
BEGIN
RAISERROR('Not enough txn log space', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:05:00';
SELECT @pct = convert(decimal(38,8),round(100*(sf.size- CAST(fileproperty(sf.name,'SpaceUsed') as float))/128.000,8)/(sf.size/128.000))
from dbo.sysfiles sf
where sf.fileid  = 2
END

-- do the delete
-- do the delete
DELETE
FROM [dbo].[FactInternetSalesReason]
WHERE [dbo].[FactInternetSalesReason].SalesOrderNumber = @id
DELETE
FROM [dbo].[FactInternetSales]
WHERE [dbo].[FactInternetSales].SalesOrderNumber = @id
SELECT @s = @ID +' deleted'
RAISERROR(@s, 0, 1) WITH NOWAIT;
-- small delay to take it easy
WAITFOR DELAY '00:02:00';

END
FETCH NEXT FROM cur INTO @id
END

CLOSE cur
DEALLOCATE cur
GO

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s