Query Store transaction preventing restoring database

Hey, sorry for the late response. I had to think about it. Based on the latest results, I would stop at this point and not continue with CHECKPOINT yet.

The new clue is:

blocking_session_id = -3

wait_type = LCK_M_IX

object_id = 213575799

In SQL Server, blocking_session_id = -3 means that the resource is owned by a deferred recovery transaction. So sp_query_store_consistency_check is not being blocked by a normal user session that can simply be killed.

Also, we know that object_id = 213575799 is the Query Store internal table you identified earlier.

So the likely sequence is:

1. Page 1:47416 cannot be accessed.

2. Recovery cannot finish UNDO for a transaction.

3. The transaction becomes DEFERRED.

4. That recovery transaction keeps locks on Query Store internal data.

5. sp_query_store_consistency_check requests an IX lock.

6. It waits on LCK_M_IX with blocker -3.

So at this point I would treat the Query Store problem as a consequence of the deferred recovery transaction, not necessarily as the original cause.

So here is a plan:

1. First check the affected page

Use this query:

USE master;
GO
SELECT
database_id,
file_id,
page_id,
event_type,
error_count,
last_update_date
FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID(N'YourDatabaseName');
GO

Then try to identify page 1:47416

DBCC TRACEON(3604);
GO
 
DBCC PAGE (N'YourDatabaseName', 1, 47416, 3);
GO

DBCC PAGE might fail because the page is already marked RestorePending, but the result is still useful if SQL Server can return any metadata. If you are on a version where sys.dm_db_page_info is available, I would also try:

SELECT *FROM sys.dm_db_page_info
(
DB_ID(N'YourDatabaseName'),
1,
47416,
'DETAILED'
);

2. Check whether this is the only bad page

Also inspect suspect_pages for other entries. If page 1:47416 is only one of several damaged pages, restoring only this page might not be sufficient. It is also worth reviewing the SQL Server error log around the restore/recovery operation for errors such as: 823, 824, 825, 829 or 3414

This is important because the deferred transaction is normally a symptom of SQL Server being unable to access data required during rollback.

3. If you have a known-good backup and transaction log chain

This would be my preferred recovery path. If the database is using FULL or BULK_LOGGED recovery and you have the required backup/log chain, consider a page restore of page 1:47416.

The idea is:

RESTORE DATABASE [YourDatabaseName]
PAGE = '1:47416'
FROM DISK = N'X:\Backups\YourDatabase_full.bak'
WITH NORECOVERY;
GO

Then apply the necessary transaction log backups:

RESTORE LOG [YourDatabaseName]
FROM DISK = N'X:\Backups\YourDatabase_log_1.trn'
WITH NORECOVERY;
GO
 
 
RESTORE LOG [YourDatabaseName]
FROM DISK = N'X:\Backups\YourDatabase_log_2.trn'
WITH NORECOVERY;
GO

And finish the recovery with the last required log:

RESTORE LOG [YourDatabaseName]
FROM DISK = N'X:\Backups\YourDatabase_log_last.trn'
WITH RECOVERY;
GO

The exact restore sequence depends on your backup history, so I would verify the backup chain before executing this. I cannot tell you the exact order because I don't know your backup strategy. After the page is restored and recovery can successfully complete the deferred rollback, check whether the blocker has disappeared- I mean that we have no more blocking_session_id = -3:

SELECT
session_id,
status,
command,
wait_type,
blocking_session_id,
wait_resource
FROM sys.dm_exec_requests
WHERE database_id = DB_ID(N'YourDatabaseName');

4. Only after the deferred transaction is gone

Then return to Query Store:

 
USE [YourDatabaseName];
GO
 
SELECT
actual_state_desc,
desired_state_desc,
readonly_reason,
current_storage_size_mb,
max_storage_size_mb
FROM sys.database_query_store_options;
GO

With Query Store disabled, run:

EXEC sys.sp_query_store_consistency_check;
GO

At this point it should no longer be waiting on the deferred recovery transaction.

And now you can run:

DBCC CHECKDB (N'YourDatabaseName')
WITH NO_INFOMSGS, ALL_ERRORMSGS;
 
GO

If CHECKDB completes cleanly, I would then create a new full backup and use that new backup for the SQL Server 2025 restore.

Hopefully you have a known-good backup available and the page restore path from step 4 resolves the deferred transaction. That would be by far the cleanest outcome here

5. If you do NOT have a usable good backup

That is a different situation. Before doing any destructive repair, I would first make a copy of the MDF/LDF files and preserve the current state, and then run:

DBCC CHECKDB (N'YourDatabaseName')
WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

If it still terminates because of the deferred transaction, the fun begins. The remaining recovery option might eventually involve emergency-mode repair:

ALTER DATABASE [YourDatabaseName] SET EMERGENCY;
GO
 
ALTER DATABASE [YourDatabaseName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
 
DBCC CHECKDB
(
N'YourDatabaseName',
REPAIR_ALLOW_DATA_LOSS
);
GO

But I would consider this a last-resort option only. REPAIR_ALLOW_DATA_LOSS can deallocate damaged pages or rebuild structures in a way that makes the database physically consistent while losing data. It is not equivalent to restoring the original data. So if a clean backup exists, restoring the affected page/database is strongly preferable.

Good luck and fingers crossed this gets you unstuck. Let me know if it resolves the issue, or at least whether we’re making progress and the symptoms change.