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