SQL DB is in redo/undo phase while restoring for more than 3 HRs on SQL 2016
-
Hello, I am trying to restore a SQL DB where it's in redo/undo phase for more than 3 - 4 HRs on SQL Server 2016 (13.0.1601). Is there anyway I can check the status and find out the estimation completion time?
Please help as I am not sure if its stuck or should I wait for it complete or not.
Thanks!
-
You can check the status of your restore operation with the dynamic managment view sys.dm_exec_requests
To view only RESTORE/BACKUP operations you can include a where clause:
SELECT *
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )
One of the columns gives you percent_complete
-
Terje Hermanseter (8/2/2016)
You can check the status of your restore operation with the dynamic managment view sys.dm_exec_requestsTo view only RESTORE/BACKUP operations you can include a where clause:
SELECT *
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )
One of the columns gives you percent_complete
I thought that that didn't work for the redo/undo portions of a restore?
There's an article on the MSDN blog about tracking database recovery using various DMVs[/url] - makes use of the sys.dm_exec_requests for tracking the main recovery, and sys.dm_tran_database_transactions for the undo/redo bits.
Thomas Rushton
blog: https://thelonedba.wordpress.com -
ThomasRushton (8/2/2016)
I thought that that didn't work for the redo/undo portions of a restore?I was not aware of this. You are right.
There's an article on the MSDN blog about tracking database recovery using various DMVs[/url] - makes use of the sys.dm_exec_requests for tracking the main recovery, and sys.dm_tran_database_transactions for the undo/redo bits.
Great article. Bookmarked!
-
ThomasRushton (8/2/2016)
Terje Hermanseter (8/2/2016)
You can check the status of your restore operation with the dynamic managment view sys.dm_exec_requestsTo view only RESTORE/BACKUP operations you can include a where clause:
SELECT *
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )
One of the columns gives you percent_complete
I thought that that didn't work for the redo/undo portions of a restore?
There's an article on the MSDN blog about tracking database recovery using various DMVs[/url] - makes use of the sys.dm_exec_requests for tracking the main recovery, and sys.dm_tran_database_transactions for the undo/redo bits.
No, but you'll still be able to see the overall percent complete and runtime, etc for the parent task, it's better than nowt
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply