June 2, 2014 at 3:17 am
Give me please example of using fast recovery on SQL Server 2014 Enterprise instance
Scenario:
-- large database in simple recovery mode
-- long delete transaction was running and sturted rollback
-- during rollback I made full backup of the database
Now I am trying to recover database from that backup but database stuck In Recovery state
Is it possible to avoid rolling back that log transaction?
How I can have database in online state as soon as possible?
June 2, 2014 at 4:54 am
I have read abour Fast Recovery here http://sqlmag.com/database-high-availability/sql-server-2012-high-availability
"
Fast recovery helps a database to be available more quickly following a restore operation. With Fast Recovery, the database is available as soon as the committed transactions are reapplied following a restore operation, with no need to wait for the uncommitted transactions to be rolled back. Like many of the other high availability options, Fast Recovery is available only in the SQL Server 2012 Enterprise edition."
June 4, 2014 at 8:22 am
Fast recovery applies to crash recovery and failovers as part of mirroring. This is not applicable to a restore operation. There is no special setting you get for the restore. You need to wait for the processes to complete.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2014 at 9:10 am
Thank you for your answer!
June 4, 2014 at 9:24 am
Maybe there are some ways to estimate how long database will be in "In Recovery" state?
I used this query for monitoring restoring process
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
but when database is In Recovey state I see percent_complete = 100% in this query for a long time
and cannot understend how much do I need to the end
June 4, 2014 at 10:43 am
Yep, percent complete is an incomplete figure. There's really no options for the position you're in. You need to wait it out in order to run the restore of a database that is in the middle of a giant delete. It has to undo that transaction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply