Doubt regarding DB in "recovery" state

Question

Doubt regarding DB in "recovery" state

Joy Smith San

SSC-Insane

Points: 24877
More actions
November 10, 2011 at 3:00 am

#262529

Experts,
I have a doubt.
sometime back One of our database went in "Recovery" state. May be due to rollback/roll forward or due to large VLFs.
Suppose if it was due to VLF issue, Will doing the following solve the issue and get the DB back in normal ?
1. Stop the SQL service.
2. Copy only the .mdf file to another server.
3. Attach it in the new server using the Single DB file option.
4. Copy the newly created LOG file to the actual server.
5. Start the service and attach the .mdf with the new small LOG file.
Just a doubt. May sound foolish. I am going to test it in my personal PC anyway.
Thanks in advance.
Smith.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Joy Smith San (11/10/2011)
Experts,
I have a doubt.
sometime back One of our database went in "Recovery" state. May be due to rollback/roll forward or due to large VLFs.
Suppose if it was due to VLF issue, Will doing the following solve the issue and get the DB back in normal ?
1. Stop the SQL service.
2. Copy only the .mdf file to another server.
3. Attach it in the new server using the Single DB file option.
4. Copy the newly created LOG file to the actual server.
5. Start the service and attach the .mdf with the new small LOG file.

You're thinking of doing that while it's in recovery? If you do, the DB won't attach (because it was not shut down cleanly)

You cannot delete a log file and expect SQL to recover the DB successfully. The log file is not an optional piece of a database, it's a critical file.

Wait until recovery has completed (it's the only thing that you can do there) and then check and fix excessive VLFs during your next downtime period. The fix for excessive VLFs is clearly shown on Kimberly's blog post. It is not deleting the log file

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Thanks Gila.

It was just an idea came to my mind.

Thanks for the clarification.