Troubleshoot transaction log errors with Azure SQL Managed Instance

Άρθρο
09/11/2023

Applies to: Azure SQL Managed Instance

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 Managed Instance, 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 Managed Instance. Azure SQL Managed Instance 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.

For more on troubleshooting a transaction log in Azure SQL Database, see Troubleshoot transaction log errors with Azure SQL Database.

For more on troubleshooting a transaction log in SQL Server, see Troubleshoot a Full Transaction Log (SQL Server Error 9002).

Automated backups and the transaction log

In Azure SQL Managed Instance, transaction log backups are taken automatically. For frequency, retention, and more information, see Automated backups. To track when automated backups have been performed on a SQL managed instance, review Monitor backup activity.

The location and name of database files cannot be managed but administrators can manage database files and file autogrowth settings. The typical causes and resolutions of transaction log issues are similar to SQL Server.

Similar to SQL Server, the transaction log for each database is truncated whenever a log backup completes successfully. Log truncation deletes inactive virtual log files (VLFs) from the transaction log, freeing space inside the file but not changing the size of the file on disk. The empty space in the log file 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 Managed Instance, new write transactions fail.

In Azure SQL Managed Instance, you can purchase add-on storage, independently from compute, up to a limit. For more information, see File management to free more space.

Prevented transaction log truncation

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).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

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.
REPLICATION	In Azure SQL Managed Instance, may occur if either replication or CDC are enabled.	If sustained, investigate agents involved with CDC or replication. For troubleshooting CDC, query jobs in msdb.dbo.cdc_jobs. If not present, add via sys.sp_cdc_add_job. For replication, see Troubleshooting transactional replication. If unresolvable, file a support request with Azure Support.
AVAILABILITY_REPLICA	Synchronization to the secondary replica is in progress.	No response required unless sustained. If sustained, file a support request with Azure Support.

Log truncation prevented by an active transaction

The most common scenario for a transaction log that cannot accept new transactions is a long-running or blocked transaction.

Run this sample query to find uncommitted or active transactions and their properties.

Returns information about transaction properties, from sys.dm_tran_active_transactions.
Returns session connection information, from sys.dm_exec_sessions.
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.

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 = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN '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.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN '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 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY 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 Managed Instance, freeing space 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.

In Azure SQL Managed Instance, you can purchase add-on storage, independently from compute, up to a limit. For example, in the Azure portal, access the Compute + storage page to increase the Storage in GB. For information on transaction log size limits, see resource limits for SQL Managed Instance. For more information, see Manage file space for databases in Azure SQL Managed Instance.

Backup storage is not deducted from your SQL managed instance storage space. The backup storage is independent from the instance storage space and it is not limited in size.

Error 9002: The transaction log for database is full

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Error 9002 occurs in SQL Server and in Azure SQL Managed Instance for the same reasons.

The appropriate response to a full transaction log depends on what conditions caused the log to fill.

To resolve Error 9002, try the following methods:

Transaction log is not being truncated and has grown to fill all available space.
- Since transaction log backups in Azure SQL Managed Instance are automatic, something else must be keeping the transaction log activity from being truncated. Incomplete replication, CDC, or availability group synchronization may be preventing truncation, see Prevented transaction log truncation.
The SQL managed instance reserved storage size is full, and the transaction log cannot grow.
- Add space up to the resource limit, see File management to free more space.
Transaction Log size is set to a fixed maximum value, or autogrow is disabled, and so cannot grow.
- See MAXSIZE and FILEGROWTH properties in ALTER DATABASE File and Filegroups.

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.

While Error 9002 is more common than Error 40552 in Azure SQL Managed Instance, both can occur.

To resolve Error 40552, 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 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).

Κοινή χρήση μέσω