Migrate to Innovate Summit:
Learn how migrating and modernizing to Azure can boost your business's performance, resilience, and security, enabling you to fully embrace AI.Register now
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
You may see errors 9002 or 40552 when the transaction log is full and cannot accept new transactions. These errors occur when the database transaction log, managed by Azure SQL Database, exceeds thresholds for space and cannot continue to accept transactions. These errors are similar to issues with a full transaction log in SQL Server, but have different resolutions in SQL Server, Azure SQL Database, and Azure SQL Managed Instance.
Note
This article is focused on Azure SQL Database. Azure SQL Database is based on the latest stable version of the Microsoft SQL Server database engine, so much of the content is similar, though troubleshooting options and tools may differ from SQL Server.
In Azure SQL Database, transaction log backups are taken automatically. For frequency, retention, and more information, see Automated backups.
Free disk space, database file growth, and file location are also managed, so the typical causes and resolutions of transaction log issues are different from SQL Server.
Similar to SQL Server, the transaction log for each database is truncated whenever a log backup completes successfully. Truncation leaves empty space in the log file, which can then be used for new transactions. When the log file cannot be truncated by log backups, the log file grows to accommodate new transactions. If the log file grows to its maximum limit in Azure SQL Database, new write transactions fail.
To discover what is preventing log truncation in a given case, refer to log_reuse_wait_desc in sys.databases. The log reuse wait informs you to what conditions or causes are preventing the transaction log from being truncated by a regular log backup. For more information, see sys.databases (Transact-SQL).
SQL
SELECT [name], log_reuse_wait_desc FROM sys.databases;
For Azure SQL Database, it is recommended to connect to a specific user database, rather than the master database, to execute this query.
The following values of log_reuse_wait_desc in sys.databases may indicate the reason why the database's transaction log truncation is being prevented:
log_reuse_wait_desc
Diagnosis
Response required
NOTHING
Typical state. There is nothing blocking the log from truncating.
No.
CHECKPOINT
A checkpoint is needed for log truncation. Rare.
No response required unless sustained. If sustained, file a support request with Azure Support.
LOG BACKUP
A log backup is required.
No response required unless sustained. If sustained, file a support request with Azure Support.
ACTIVE BACKUP OR RESTORE
A database backup is in progress.
No response required unless sustained. If sustained, file a support request with Azure Support.
ACTIVE TRANSACTION
An ongoing transaction is preventing log truncation.
The log file cannot be truncated due to active and/or uncommitted transactions. See next section.
Returns request information (for active requests), from sys.dm_exec_requests. This query can also be used to identify sessions being blocked, look for the request_blocked_by. For more information, see Gather blocking information.
Returns the current request's text or input buffer text, using the sys.dm_exec_sql_text or sys.dm_exec_input_buffer DMVs. If the data returned by the text field of sys.dm_exec_sql_text is NULL, the request is not active but has an outstanding transaction. In that case, the event_info field of sys.dm_exec_input_buffer contains the last statement passed to the database engine.
SQL
SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASEWHEN r.statement_start_offset = 0and r.statement_end_offset= 0THENleft(est.text, 4000)
ELSESUBSTRING ( est.[text], r.statement_start_offset/2 + 1,
CASEWHEN r.statement_end_offset = -1THENLEN (CONVERT(nvarchar(max), est.[text]))
ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1END ) END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state
WHEN0THEN'The transaction has not been completely initialized yet.'WHEN1THEN'The transaction has been initialized but has not started.'WHEN2THEN'The transaction is active - has not been committed or rolled back.'WHEN3THEN'The transaction has ended. This is used for read-only transactions.'WHEN4THEN'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'WHEN5THEN'The transaction is in a prepared state and waiting resolution.'WHEN6THEN'The transaction has been committed.'WHEN7THEN'The transaction is being rolled back.'WHEN8THEN'The transaction has been rolled back.'END
, transaction_name = tat.name
, azure_dtc_state --Applies to: Azure SQL Database only
= CASE tat.dtc_state
WHEN1THEN'ACTIVE'WHEN2THEN'PREPARED'WHEN3THEN'COMMITTED'WHEN4THEN'ABORTED'WHEN5THEN'RECOVERED'END
, transaction_type = CASE tat.transaction_type WHEN1THEN'Read/write transaction'WHEN2THEN'Read-only transaction'WHEN3THEN'System transaction'WHEN4THEN'Distributed transaction'END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN1THEN'Local transaction, not distributed'WHEN0THEN'Distributed transaction or an enlisted bound session transaction.'END
, transaction_uow --for distributed transactions.
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat
INNERJOIN sys.dm_tran_session_transactions AS tst on tat.transaction_id = tst.transaction_id
INNERJOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id
LEFTOUTERJOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSSAPPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
OUTERAPPLY sys.dm_exec_sql_text (r.sql_handle) AS est;
File management to free more space
If the transaction log is prevented from truncating in Azure SQL Database elastic pools, freeing space for the elastic pool may be part of the solution. However, resolving the root the condition blocking transaction log file truncation is key. In some cases, temporarily creating more disk space allows long-running transactions to complete, removing the condition blocking the transaction log file from truncating with a normal transaction log backup. However, freeing up space may provide only temporary relief until the transaction log grows again.
Error 40552: The session has been terminated because of excessive transaction log space usage
40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.
To resolve this issue, try the following methods:
The issue can occur in any DML operation such as insert, update, or delete. Review the transaction to avoid unnecessary writes. Try to reduce the number of rows that are operated on immediately by implementing batching or splitting into multiple smaller transactions. For more information, see How to use batching to improve SQL Database application performance.
The issue can occur because of index rebuild operations. To avoid this issue, ensure the following formula is true: (number of rows that are affected in the table) multiplied by (the average size of field that's updated in bytes + 80) < 2 gigabytes (GB). For large tables, consider creating partitions and performing index maintenance only on some partitions of the table. For more information, see Create Partitioned Tables and Indexes.
If you perform bulk inserts using the bcp.exe utility or the System.Data.SqlClient.SqlBulkCopy class, try using the -b batchsize or BatchSize options to limit the number of rows copied to the server in each transaction. For more information, see bcp Utility.
If you are rebuilding an index with the ALTER INDEX statement, use the SORT_IN_TEMPDB = ON, ONLINE = ON, and RESUMABLE=ON options. With resumable indexes, log truncation is more frequent. For more information, see ALTER INDEX (Transact-SQL).
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.