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

February 26, 2013 — 10 Comments

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

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

  1. 

    So, I take it that you can’t you put the recovery model to Simple on Azure? Don’t know much about Azure, but that’d be nice to know…

    Also, nice use of the CTE for the Top expression. I’d do something like this, though:

    SELECT TOP(10000 * @count) *
    FROM [YOUR TABLE]

    You’re a prolific blogger this month! Pretty interesting articles imo, keep it up

    Like

    • 

      Interesting can you explain why you would use

      SELECT TOP(10000 * @count) *
      FROM [YOUR TABLE]

      The reason I limit the number of rows, is because SQL Database has many strategies for protecting itself from abuse. One of these is limiting transaction logs to 2gb, an other way it protects itself is by limiting the number of locks per transaction. It also uses aggressive timeouts.

      I took some time to go back to a book and some documentation, and it doesn’t seem like we can change the recovery model. This is probably because each instance of SQL Database is replicated 2 times giving a total of 3 instances at all times. One active and two Idle.

      When you look at the online documentation, the only available value for “Recovery” is FULL”
      http://msdn.microsoft.com/en-us/library/windowsazure/ee621786.aspx

      Thanks for taking the time to comment =) I’m trying to get everything out of my head right now. I’ve got a lot of posts in mind and I’m trying to pace my self so that I don’t forget about them.

      Windows Azure an adventure in itself.

      Like

      • 

        Yep, bad read on my part for the @count.

        So the trick of temporarily switching the recovery model to simple won’t work on azure… :(

        Thank you for taking the time to confirm it.

        Looking forward to your next post!

        Like

  2. 

    Thank you for sharing this, Alexandre – it is much appreciated.

    Like

  3. 

    Thanks – This is just what I needed and learned something too.

    Like

  4. 

    Thanks for really helpful post. I used as a basis for creating archival / deleting scripts.

    For users trying to stay within DTU tolerances, try adding delay in loop:

    WAITFOR DELAY '00:00:02';
    

    Previously batch would ramp up DTU to 100%, but the small delay (2 seconds – adjust as required) kept it consistently around 70% allowing normal database activity to continue with no / minimal impact.

    Like

  5. 

    How to delete from multiple table ?

    Like

Trackbacks and Pingbacks:

  1. Dew Drop – February 28, 2013 (#1,506) | Alvin Ashcraft's Morning Dew - February 28, 2013

    […] Deleting Large Amounts of Records From A Table Without Truncating on Windows Azure SQL Database (Alexandre Brisebois) […]

    Like

  2. The Top 10 Most-Read #WindowsAzure Posts of 2013 | Alexandre Brisebois - December 29, 2013

    […] Deleting Large Amounts of Records From A Table Without Truncating on Windows Azure SQL Database – 1,893 reads […]

    Like

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