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 GOFollow @rPh0enix