Deleting Large Amounts of Records From A Table Without Truncating on Windows Azure SQL Database

There are times when you need to delete large amounts of records from a table. Deleting a few million records from Windows Azure SQL Database using a single statement will probably result in a 40552 The session has been terminated because of excessive transaction log space usage error. This is how I go about cleaning my database.

I execute the following T-SQL from a Query in Visual Studio 2012. You could also run it from a stored procedure, but it may timeout because of the amount of data that needs to be deleted.

DECLARE @count AS INT = 1
WHILE(@count > 0)
BEGIN 
 BEGIN TRANSACTION TDelete
    
    ;WITH CTE AS
    (
    SELECT TOP(10000) *
    FROM [YOUR TABLE] 
    WHERE [YOUR CRITERIA]
    ORDER BY Id
    )
    DELETE FROM CTE

 COMMIT TRANSACTION TDelete
 
 SELECT @count = COUNT(1) 
 FROM [YOUR TABLE] 
 WHERE [YOUR CRITERIA]
 
 PRINT 'DELETED 10 000 -> '+ CONVERT(VARCHAR(10),@count) + ' REMAINING'
END;

Replace[YOUR TABLE]  with your table name and [YOUR CRITERIA]  with your WHERE clause. You can also play with the batch size. Depending on the current load on your SQL Database, you may be able to use a bigger batch size or you may have to reduce it in order not to get timed out.

Example Output from the T-SQL above

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 10204 ms,  elapsed time = 10324 ms.
DELETED 10 000 -> 3467290 REMAINING

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 1703 ms,  elapsed time = 25537 ms.

    (10000 row(s) affected)

Or from an application, find the number of records that need to be deleted. Calculate the number of batches required to delete all the data. Then for each batch, create a new SQL Command which executes the delete Stored Procedure. Deleting too many records at once will force Windows Azure SQL Database to raise a 40552 : The session has been terminated because of excessive transaction log space usage.

References

• You can find more information about Common Table Expressions in my previous post about Using Common Table Expressions (CTE) to Remove Duplicates
• Using Common Table Expressions
• WITH common_table_expression (Transact-SQL)
• Common Table Expressions (by John Papa)
• CTEs (Common Table Expressions) (by Craig Freedman)
• Optimize Recursive CTE Query