40552 : The session has been terminated because of excessive transaction log space usage.

If you are not familiar with the following Windows Azure SQL Database error, you will rapidly come to understand that SQL Database is very good at protecting itself  from abuse or anything it considers abuse. I regularly come across this specific error when I ask too much of SQL in a single transaction.

40552 : The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

Windows Azure SQL Database prevents clients from monopolizing system resources. Index manipulation can lead to consuming too many resources. Merge statements can also lead to the same type of errors.

Transaction Logs are limited to 2 GB per transaction

To overcome this problem, execute your transaction in batches. Each batch must have its own transaction.

Merging 20 Millions rows will definitely raise this error, the following example illustrates how you can partition a merge and execute it in batches. Find the appropriate batch sizes by testing and observing how SQL Database will react.

CREATE PROCEDURE [dbo].[MergeCustomerTables]
        @offsetIndex int = 0,
        @batchSize int = 10000
AS

DECLARE @offset as bigint

SELECT @offset = @offsetIndex *  @batchSize

MERGE Customers AS Target
USING (SELECT a.[Name], a.[LastName], a.[Email] from CustomerInsertTable as a
       ORDER BY a.[Email]
       OFFSET  @offset ROWS 
       FETCH NEXT @batchSize ROWS ONLY 
) AS Source
ON (Target.[Email] = Source.[Email])
WHEN MATCHED THEN
    UPDATE SET Target.[Name] = Source.[Name]
             , Target.[LastName] = Source.[LastName]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Name], [LastName], [Email])
    VALUES (
     Source.[Name] 
     , Source.[LastName]
     , Source.[Email]
    );

The Stored Procedure takes an offset and a batch size. It uses OFFSET and FETCH NEXT introduced in  
SQL Server 2012 to page through the source table data.

From an application, find the number of records that need to be imported. Based on the batch size calculate the number of batches that need to be executed. Then for each batch, create a new SQL Command which executes the merge Stored Procedure with the right offset Index. Be sure to insert a short delay between each SQL Command, because if they run too fast Windows Azure SQL Database might raise a 40501 due to an excessive number of requests.

To create Indexes on large tables without getting errors use the WITH(ONLINE=ON) option.

CREATE NONCLUSTERED INDEX [IX_Customer_Index]
    ON [dbo].[Customer]([Name] ASC, [LastNamel] ASC)
    WITH(ONLINE=ON);

This option will release row locks faster and prevent most errors from happening.

Other possible errors when working with Windows Azure SQL Database

 

• 40501 – Retry to connect to SQL Database in intervals of 10 seconds until the resources are available and your connection is established again.
• 40550 – The session has been terminated because it has acquired too many locks. Try reading or modifying fewer rows in a single transaction.
• 40549 – Session is terminated because you have a long running transaction. Try shortening your transaction.
• 40551 – The session has been terminated because of excessive TEMPDB usage. Try modifying your query to reduce temporary table space usage.
• 40552 – The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.
• 40553 – The session has been terminated because of excessive memory usage. Try modifying your query to process fewer rows.
• 40545- The service is experiencing a problem that is currently under investigation.
• 40544 – The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.
• 40174 – The partition is in transition and transactions are being terminated.

References

• Windows Azure SQL Database Performance and Elasticity Guide