Reason that might cause Database became In Recovery in SQL Server 2008

Question

Reason that might cause Database became In Recovery in SQL Server 2008

ratna_ain

Say Hey Kid

Points: 698
More actions
May 28, 2011 at 7:56 pm

#389612

Hi All,
I have a problem in SQL Server 2008 R2.
I was running a query that updated huge data in database, therefore it takes a lot of time to finish.
And before the query has been finished (after 1 hour processing), I got error message "Cannot open database "XXX" requested by the login. The login failed". And then I tried to connect to that database again, and the Database became XXX(In Recovery). After 5 hours, the database has recovered on its own.
Does anybody know what is the problem that might cause this issues(database became In Recovery)?
Thanks in advance

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL was restarted and a large transaction had to either roll back or roll forward.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 2

Hi Gail,

I was also stuck in similar scenario, and my database went into DBName(Recovery) for a short time and then in backgorund it took 16 hour for the rollback part to complete.

I just wanted to discuss Fast Recovery Concept of SQL Server. As long as the database is showing (in Recovery) in Management Studio it is Roll-Forward going on. And after that Rollback happens in background.

Moral of the Story:- Never Restart your server while a heavy transaction is going on. And if you did make sure you are watching Error Log to give some reason about slow performance.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

RakeshRSingh (6/1/2011)
I just wanted to discuss Fast Recovery Concept of SQL Server. As long as the database is showing (in Recovery) in Management Studio it is Roll-Forward going on. And after that Rollback happens in background.

Enterprise edition only. All other editions the rollback is offline too.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Just FYI, we had a guy who had autoshrink and autoclose to on which also cause a similar issue (db went in and out recovery without any obvious reason and it caused a lot of troubles).

ratna_ain Say Hey Kid Points: 698 More actions · Answer 5

Thanks all.

That's because I used virtual Machine and the database is very huge.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

ratna_ain (7/8/2011)
Thanks all.
That's because I used virtual Machine and the database is very huge.

I agree with the huge db part but virtualized machine, when well configured shouldn't be a massive factor here...

ratna_ain Say Hey Kid Points: 698 More actions · Answer 7

Yes SSCoach, may be there is a problem with the configuration of the virtual machine.

Because when I run same query to the same database in the physical server, it takes less time to finish than in that virtual machine.

Thank you

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 8

ratna_ain (7/13/2011)
Yes SSCoach, may be there is a problem with the configuration of the virtual machine.
Because when I run same query to the same database in the physical server, it takes less time to finish than in that virtual machine.
Thank you

I also use virtual machines and so far I can't complain about the performance. Did you make sure all the resources are being comnitted to the virtual machine and that your host is not overcommitting?

Check this article by Brent Ozar.

My blog

suneet.tg SSC Journeyman Points: 77 More actions · Answer 9

Hi,

While updating the data in the table you have lost the connectivity.You can check the Logs,there was a connection lost error.

To overcome the recovery problem always shrink you log files.

Gaurav and Suneet.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

suneet.tg (6/18/2013)
To overcome the recovery problem always shrink you log files.

No you can't. If the DB is in recovery the log can't be shrunk. Once the DB comes online the log shouldn't be shrunk. Shrinking is not going to prevent a DB going into recovery and is generally a poor thing to do to the log.

p.s. 2 year old thread.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rajesh M S SSChasing Mays Points: 638 More actions · Answer 11

I was facing same problem once after shrinking log files(reducing VLFs) recovery is fast.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 12

yes correct as Gail told enterprise edition took online recovery while restart the server..

Standard Edition few months back i noticed that recovery on offline mode on Transaction log file 35 GB.. and tooks online 4 hours to become a online database.. so after online I did shrunk Log file upto 1 GB also configured Tlog backup every 30 mints then Tlog never grown more size.