Lesson Learned #421:Understanding and Troubleshooting Transaction Log Truncation in Azure SQL DB
Azure SQL Database, Microsoft's cloud-based database service, manages many administrative functions automatically, such as backups and patching. However, understanding the behavior of the transaction log, especially its truncation, remains crucial. This article delves into potential reasons why a transaction log might not truncate as expected and offers steps to investigate and address these concerns.
Query Store transaction preventing restoring database
I have a database backup that was taken on SQL Server 2019 instance. While trying to restore it to a SQL Server 2025 instance, the files restored without issue, but it remained "In Recovery" for three days before I tried to take it to Emergency, Single User, and back online, though it still remained in recovery. I then took the backup to a SQL Server 2019 instance and was able to restore it and bring it online there. Though the logs on both servers had shown similar errors to these two. The Page and Log record Id were consistant. ```text Database ID 5, Page (1:47416) is marked RestorePending, which may indicate disk corruption. During undoing of logged operation in database (page(1:301040) if any), an error occurred at log record ID (15885:36832:180). ``` Doing some investigation has provided that there is a stuck transaction, and I was able to pin this down to Query Store. OPENTRAN (This spid was from the original server.): ```text ransaction information for database 'BrokenDatabase'. Oldest active transaction: SPID (server process ID): 26s UID (user ID) : -1 Name : DELETE LSN : (15885:19072:1) Start time : Mar 1 2025 7:27:58:633PM SID : 0x01 DBCC execution completed. If DBCC printed error messages, contact your system administrator. ``` I had the Top and bottom 50 of 938 transactions from fn_dblog, but since it contains data this community will not allow it. Makes it harder to get an answer without the data showing that query store is the issue, but to play by the rules you have give lackluster information, because the guidelines are even searching through code blocks, markdown, and tables. With Query Store in the Read_Write mode, I was unable to return anything from sys.query_store_plan and / or sys.query_store_runtime_stats, because they were locked by the ASYNC_LOAD of QDS. All that I have collected show that is internal to the Query Store. sys.internal_tables: | name | object_id | principal_id | schema_id | parent_object_id | type | type_desc | create_date | modify_date | is_ms_shipped | is_published | is_schema_published | internal_type | internal_type_desc | parent_id | parent_minor_id | lob_data_space_id | filestream_data_space_id | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | plan_persist_runtime_stats | 213575799 | NULL | 4 | 0 | IT | INTERNAL_TABLE | 2019-09-24 | 2019-12-28 | 0 | 0 | 0 | 243 | QUERY_DISK_STORE_RUNTIME_STATS | 0 | 0 | 0 | NULL | sys.dm_tran_active_transactions: | transaction_id | database_id | database_transaction_begin_time | database_transaction_type | database_transaction_state | database_transaction_status | database_transaction_status2 | database_transaction_log_record_count | database_transaction_replicate_record_count | database_transaction_log_bytes_used | database_transaction_log_bytes_reserved | database_transaction_log_bytes_used_system | database_transaction_log_bytes_reserved_system | database_transaction_begin_lsn | database_transaction_last_lsn | database_transaction_most_recent_savepoint_lsn | database_transaction_commit_lsn | database_transaction_last_rollback_lsn | database_transaction_next_undo_lsn | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | 255756374 | 5 | NULL | 1 | 4 | 524289 | 1 | 0 | 0 | 0 | 10677320 | 0 | 0 | 15885-00000-19072-00001 | 15885-00000-36952-00023 | NULL | NULL | NULL | 15885-00000-36832-00179 | Last Thursday, I was able to force the database to turn query store off, I tried after that setting it for Read_only and then clearing Query Store, but the clearing went into a lock for ASYNC_LOAD of QDS, after 6 hours of nothing happening and checking requests and seeing the lock, I ended that task. It was not actively doing anything just sleeping. My ultimate goal is to be able to get his database to the SQL Server 2025 instance and have it online. What can I do with the original backup or currently the online version of the database on the SQL Server 2019 instance to be able to make that happen?
