Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Reason that might cause Database became In Recovery in SQL Server 2008 Expand / Collapse
Author
Message
Posted Saturday, May 28, 2011 7:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 3:07 AM
Points: 8, Visits: 25
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
Post #1116704
Posted Sunday, May 29, 2011 3:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
SQL was restarted and a large transaction had to either roll back or roll forward.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1116750
Posted Wednesday, June 1, 2011 5:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:34 AM
Points: 2,819, Visits: 204
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
Post #1117971
Posted Wednesday, June 1, 2011 6:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #1117991
Posted Wednesday, June 1, 2011 6:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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).
Post #1117994
Posted Friday, July 8, 2011 4:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 3:07 AM
Points: 8, Visits: 25
Thanks all.

That's because I used virtual Machine and the database is very huge.
Post #1138762
Posted Friday, July 8, 2011 7:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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...
Post #1138899
Posted Wednesday, July 13, 2011 7:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 3:07 AM
Points: 8, Visits: 25
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
Post #1141451
Posted Friday, July 29, 2011 12:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:46 AM
Points: 3,087, Visits: 1,436
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
Post #1150682
Posted Tuesday, June 18, 2013 12:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 15, 2013 10:36 PM
Points: 1, Visits: 2
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.
Post #1464473
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse