Recovery of a database take much longer time

Question

Recovery of a database take much longer time

sivaprasad

SSCertifiable

Points: 6183
More actions
January 28, 2006 at 2:39 am

#110715

Recovery of a database take much longer time:
Recovery of database 'database_a'(7) is 3% complete (approximately 1419 more seconds) (Phase 2 of 3).
Database size around 50 GB. Database reside on SAN Disk in Active / Passive cluster on Windows 2003 Enterprise Edition with SQL Server 2000 Enterprise Edition setup.
On restart of the SQL Server service some times the database recovery takes more than 2 hours, the recovery going on snail pace.
What could be causing the database recovery on slow pace?
THA
Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

It seems that before restating the SQL server you had a big transaction running.

Run DBCC OPENTRAN('database_a'), that will give you the oldest transaction in the db before restarting the server. This will give the spid that is has the opened transaction. With DBCC INPUTBUFFER(spid), you can see what the processes is running.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Have you changed the 'recovery interval' database option?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sivaprasad SSCertifiable Points: 6183 More actions · Answer 3

'Reovery interval ' option has not changed , it is set with default value of 0 (Zero).

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Balmukund SSCertifiable Points: 6648 More actions · Answer 4

I agree with Chris.

Here are the steps in recovery:

1. Analysis

2. Roll forward commited transaction

3. Rollback uncommited transaction.

Now, if there was a transaction which was running for long time and SQL shutdown happened, it has to recover the database to bring it in consistent state.

HTH