SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ratna_ain
ratna_ain
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216135 Visits: 46276
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
RakeshRSingh
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2955 Visits: 208
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216135 Visits: 46276
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
Ninja's_RGR'us
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65259 Visits: 9671
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
ratna_ain
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 25
Thanks all.

That's because I used virtual Machine and the database is very huge.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65259 Visits: 9671
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
ratna_ain
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 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
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6356 Visits: 1439
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
suneet.tg
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search