August 8, 2011 at 9:06 pm
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.
August 9, 2011 at 3:28 am
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
August 9, 2011 at 5:11 am
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.
August 9, 2011 at 5:24 am
srinath.vanama (8/9/2011)
... if Log size is huge simply shrink log files.
I don't agree with this advice.
August 9, 2011 at 5:30 am
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.
August 9, 2011 at 5:44 am
Thanks Srinath for sharing your experience.
Here, I have a question for you. Why does your production SQL Server service restart DAILY?
August 9, 2011 at 5:48 am
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/
August 9, 2011 at 5:51 am
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.
August 9, 2011 at 6:01 am
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.
August 9, 2011 at 6:03 am
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
August 9, 2011 at 6:35 am
Ninja And Gila,
After shrinking the log i didn't face the problem till date, can u ppl please guide me in my case what to do and what not to do, if the same problem repeats?
Please Advice me...
August 9, 2011 at 6:37 am
srinath.vanama (8/9/2011)
Ninja And Gila,After shrinking the log i didn't face the problem till date, can u ppl please guide me in my case what to do and what not to do, if the same problem repeats?
Please Advice me...
What part of the 4 articles we sent you didn't you understand?
English is not my 1st language and I understand them perfectly.
August 9, 2011 at 6:42 am
oopsssss, i found it.
Thanks a lot..
August 9, 2011 at 6:44 am
srinath.vanama (8/9/2011)
oopsssss, i found it.Thanks a lot..
Awesome, let us know if you need further clarification after re-reading the articles.
August 9, 2011 at 6:49 am
srinath.vanama (8/9/2011)
After shrinking the log i didn't face the problem till date, can u ppl please guide me in my case what to do and what not to do, if the same problem repeats?Please Advice me...
Did you read the two blog posts I posted? They are extremely detailed.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply