Transaction log is full in Azure SQL DB

Question

SQLServerException: The transaction log for database '2a7b93ac-ce3d-4bc2-ac40-f3f96e39c7a2' is full due to 'ACTIVE_TRANSACTION' and the holdup lsn is (961437:3829968:1).

Getting this error in Azure SQL DB

Answer 1

Hi, @Vijay Kumar Thanks for the ask and for using the Microsoft Q&A platform.

The 'ACTIVE_TRANSACTION' part explains that there is an active transaction in the database and because of this, the transaction log file for the database cannot be truncated. To resolve this issue, it is key to find the active transaction and stop the activity. To understand which transaction is holding up the transaction log for tempdb, you can utilize the below query:

SELECT SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time,
er.reads,er.writes, er.logical_reads, er.granted_query_memory,es.host_name , es.login_name , es.program_name
FROM sys.dm_db_task_space_usage tsu
INNER JOIN sys.dm_exec_requests er ON ( tsu.session_id = er.session_id AND tsu.request_id = er.request_id)
INNER JOIN sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id ) CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+ (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC

Once you have found the user activity with the above query, you can use the 'session_id' and the query should be killed/cancelled.

you can also refer to this article

Please let me know if you find this usefull.

Regards
Geetha