Troubleshooting Error 40552 in SQL Azure

SQL Azure has mechanism in place to prevent monopolizing of various system resources. One of the safeguards in place watches active portion of the log space utilized by a transaction. Applications running transactions that use large amount of log space may receive following error message: Msg 40552, Level 20, State 1, Line 1 The session […]

SQL Azure has mechanism in place to prevent monopolizing of various system resources. One of the safeguards in place watches active portion of the log space utilized by a transaction. Applications running transactions that use large amount of log space may receive following error message: Msg 40552, Level 20, State 1, Line 1
The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction
. To fix “Creating, rebuilding and dropping indexes could generate a lot of transaction log records and may hit this error msg on larger tables. You may be able to work around the issue by creating a new table with desired index layout, and then move the data in smaller chunks over to new table. However in most cases, you can minimize transaction log space usage with index operations by using ONLINE option with your statement. Specifying ONLINE=ON with CREATE, ALTER and DROP INDEX operations change the characteristics of execution. Instead of a single large transaction, operation’s performed in multiple shorter transactions in the background without holding exclusive locks for extended periods of time on rows. This both improves concurrency during execution of the operation, and it eases transaction log space requirement for the operation. This can help you avoid the 40552 exception,” explain Microsoft.

[Source]