Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL DATABASE IN RECOVERY MODE


SQL DATABASE IN RECOVERY MODE

Author
Message
gajananhatkar
gajananhatkar
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 206
Database is in recovery mode after restarting the sql server services when services started there was space issue on disk i have the mdf and ldf file . how to go ahead to recover the data.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
Wait.

Recovery is a necessary process to bring the DB up clean. Wait it out, it will finish. Anything you do (like restart SQL) will just start it over again from scratch. SQL will log in the error log how long it estimates the recovery will take.


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


MasterDB
MasterDB
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 574
Gila is absolutely correct,

you unable to do anything on the database which is in IN recovery mode.

you can able to see the status of the database in error logs like how much percent it recovers. some times it may not show the status in error logs but dont worry just wait till it becomes recovered. then find what to do next to avoid this kind of problems in future like.

1. why you restarted SQL Services,
2. Before starting SQL Services, Just check the log size of all databases. if Log size is huge simply shrink log files. and then restart services.
Suresh B.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
srinath.vanama (8/9/2011)
... if Log size is huge simply shrink log files.

I don't agree with this advice.
MasterDB
MasterDB
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 574
i came across similar kind of issue in my production.

After database recovered i checked the log size and it is near about 170 gb. Later i shrinked the log file and my SQL server services restarts daily,till now database never gone in to inrecovery mode.
Suresh B.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
Thanks Srinath for sharing your experience.
Here, I have a question for you. Why does your production SQL Server service restart DAILY?
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
srinath.vanama (8/9/2011)
i came across similar kind of issue in my production.

After database recovered i checked the log size and it is near about 170 gb. Later i shrinked the log file and my SQL server services restarts daily,till now database never gone in to inrecovery mode.



And if you shoot a dog in the head it will probably stop barking.

I wouldn't do any of those things (including daily restart).

You better read both of these before it's too late!

http://www.sqlservercentral.com/articles/Transaction+Log/72488/
http://www.sqlservercentral.com/articles/64582/
MasterDB
MasterDB
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 574
Actually I dont know. this job is automated my Windows team 2 years ago and in the production there are morethan 50 jobs are running on 24*7 basis. Moreover, production Database didn't get any problem with restarting services coz at that time .mdf and .ldf file are in minimal size. when i got this problem i checked the database and log file log size is 170 gb. what i came to know that Database will be in recovery mode still it has to recover 170 GB of data.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
No it doesn't have to recover 170 GB of data. It has to rollforward, or most likely rollback the active transaction you killed mid-process.

You just happened to do it at a really bad that on that day which caused you to wait.


Seriously do everybody a favor and read both articles I pointed out and set up real maintenance on that server. The log won't grow to 170GB and you'll be able to restore in case of a disaster. Which judging by the SQL knowledge of the admins is likely to happen.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
srinath.vanama (8/9/2011)
if Log size is huge simply shrink log files. and then restart services.


Nope. Bad advice and may in fact cause the problem you say it will prevent if the log regrows with inappropriate growth increments. See http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx and http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx


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


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