December 30, 2009 at 9:37 am
one of my daily restore jobs which usually works fine is causing the database to go into RECOVERY state and sit in that state for hours.
Im going to just drop and restore the database from scratch, but im wondering why is it going into recovery mode? im not seeing anything out of the ordinary in the logs.
From what i understand, when a database is in recovery mode, sql server is trying to recover the db to a useable state by going through the logs and applying all committed trans etc. is this correct/
If so, as i was restoring from a seperate backup, i dont know why it went into recovery mode.
December 30, 2009 at 12:37 pm
Recovery (restart-recovery) goes through the logs, rolls back transactions that started but never committed and rolls forward transactions that did commit. The latter may not be so relevant for a backup.
My guess, there are long-running transactions on the source DB at the time the backup is taken. SQL has to roll those back as part of the restore to get a consistent database. I'd start by looking at what's happening on the source when the backup is taken.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2009 at 2:14 pm
thats an interesting theory, and il give a look on the source db tomorrow, however, one thing it doesnt explain is that, when the resotre was run in via a job step, it went into recovery status, but when i copied the restore code from the job step and ran it in a query window, it restored without incident.
December 31, 2009 at 12:46 am
Interesting. Same backup? Same time of day?
Are you sure the state is 'recovering' not 'restoring'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply