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»»

SQL DATABASE IN RECOVERY MODE Expand / Collapse
Author
Message
Posted Monday, August 8, 2011 9:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 4:36 PM
Points: 11, Visits: 125
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.
Post #1156503
Posted Tuesday, August 9, 2011 3:28 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 @ 6:28 AM
Points: 42,468, Visits: 35,538
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 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 #1156630
Posted Tuesday, August 9, 2011 5:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:15 AM
Points: 345, Visits: 567
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.



Post #1156690
Posted Tuesday, August 9, 2011 5:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:17 AM
Points: 1,101, Visits: 5,279
srinath.vanama (8/9/2011)
... if Log size is huge simply shrink log files.

I don't agree with this advice.
Post #1156705
Posted Tuesday, August 9, 2011 5:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:15 AM
Points: 345, Visits: 567
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.
Post #1156708
Posted Tuesday, August 9, 2011 5:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:17 AM
Points: 1,101, Visits: 5,279
Thanks Srinath for sharing your experience.
Here, I have a question for you. Why does your production SQL Server service restart DAILY?
Post #1156723
Posted Tuesday, August 9, 2011 5:48 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 21,385, Visits: 9,603
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/
Post #1156727
Posted Tuesday, August 9, 2011 5:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:15 AM
Points: 345, Visits: 567
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.
Post #1156731
Posted Tuesday, August 9, 2011 6:01 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 21,385, Visits: 9,603
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.
Post #1156741
Posted Tuesday, August 9, 2011 6:03 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 @ 6:28 AM
Points: 42,468, Visits: 35,538
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 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 #1156743
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse