February 13, 2009 at 3:49 am
I was doing a large insert into a table say about 400M> i noticed i was running out of space on the log drive. so my inserts were not commiting. So i just killed the job and restarted the server. After the restart the data base is in recovery mode. I need to get this up running say in about 2 hours. Is there any other way, to do this other than waiting for the recovery to complete.
I see these error messages on the error log
'Recovery of database 'MailingTransactions' (5) is 0% complete (approximately 547765 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.'
Looks like i have to wait morethan 4 days to get this up and running. So please help. Anybody else with a similar kind of problem and how did you tackle this.
Thanks in Advance.
Sharmila
February 13, 2009 at 3:59 am
Codezilla (2/13/2009)
I was doing a large insert into a table say about 400M> i noticed i was running out of space on the log drive. so my inserts were not commiting. So i just killed the job and restarted the server. After the restart the data base is in recovery mode. I need to get this up running say in about 2 hours. Is there any other way, to do this other than waiting for the recovery to complete.I see these error messages on the error log
'Recovery of database 'MailingTransactions' (5) is 0% complete (approximately 547765 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.'
Looks like i have to wait morethan 4 days to get this up and running. So please help. Anybody else with a similar kind of problem and how did you tackle this.
Thanks in Advance.
Sharmila
Hi Sharmila,
You will need to wait until recovery is completed to ensure integrity of database. I wouldn't expect recovery to take 4 days, as this is an estimated value and not an actual value. This is the redo phase of recovery process to roll-forward uncommitted transactions and once this phase is completed and the undo phase starts, you will be able access database, if running SQL SVR 2005 ENT version.
Phillip Cox
February 13, 2009 at 4:42 am
Codezilla (2/13/2009)
I I need to get this up running say in about 2 hours. Is there any other way, to do this other than waiting for the recovery to complete.
No. You have to wait for recovery to complete. If you restart SQL during recovery, it'll just start over from the beginning.
The problem is that, while your inserts were getting logged in the tran log, they weren't getting written to the DB. When you restarted SQL, you didn't give it the chance to shutdown cleanly, so it has to redo all of those inserts and get the changes committed in the data pages.
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
February 13, 2009 at 4:54 am
thanks for your replies. It currently is telling me that it has 10 hours to recover. But I am not sure if we could wait untill then. Could it take longer because we are still out of space on the drive where the logs are stored. let me know your thoughts
February 13, 2009 at 4:54 am
Codezilla (2/13/2009)
Gail thanks for your reply. It currently is telling me that it has 10 hours to recover. But I am not sure if we could wait untill then. Could it take longer because we are still out of space on the drive where the logs are stored. let me know your thoughts
Codezilla,
Please remember, this is an estimated recovery time not actual. More importantly, you need to increase the space on your log volume urgently.
Phillip Cox
February 13, 2009 at 4:59 am
Codezilla (2/13/2009)
thanks for your replies. It currently is telling me that it has 10 hours to recover. But I am not sure if we could wait untill then.
You don't have a choice. You have to wait until recovery is complete
Could it take longer because we are still out of space on the drive where the logs are stored. let me know your thoughts
Unlikely. Recovery is mostly reading the logs, not writing to them. If there's lots of other activity on the log drive, that could make the recovery slower.
If you're completely out of space on the log drive, there's a problem. If the log needs a bit of space at the end to mark the final checkpoint and can't, you risk recovery failing and the entire thing having to start over again.
Is this Enterprise edition?
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
February 13, 2009 at 7:05 am
The sql server is a standard 64 bit edition. We have about 21G left of the drive now. The error log specifies that it is about 32 % complete. That is it is going to take atleast 8 hours to recover the database. If I need to keep my job, it has to complete atleast in about 4 hours.
February 13, 2009 at 7:46 am
Codezilla (2/13/2009)
The sql server is a standard 64 bit edition. We have about 21G left of the drive now. The error log specifies that it is about 32 % complete. That is it is going to take atleast 8 hours to recover the database. If I need to keep my job, it has to complete atleast in about 4 hours.
Hi Codezilla,
You should be ok, as I had to recover a 1TB+ DB due to exact same issue and estimated time was way off the mark, as it SQL Server was reporting 5hrs to completion, but it only took 90min.
What is current completion value?
Thanks,
Phillip Cox
February 13, 2009 at 8:05 am
Codezilla (2/13/2009)
If I need to keep my job, it has to complete atleast in about 4 hours.
Then start writing up your CV.
There is nothing that you can do to make SQL come up without doing recovery. There is no advice we can give you on getting it up in 4 hours, except ensure that the drives are not been used by other apps. There is no rtaceflag, undocumented command, secret key combo that is going to make it finish recovery now. It may finish in 4 hours, if it;s estimate is wrong. It often is. Whether it's estimated low or high is another matter.
Best case, the recovery finishes in just a few minutes. Worst case, it runs for over the 8 hours it's estimating. No way to tell which.
That, unfortunately is that. That's why it's not a good idea to restart SQL hard without letting it cleanly shut down it's databases on the way out
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
February 13, 2009 at 8:10 am
Phillip Cox (2/13/2009)
You should be ok, as I had to recover a 1TB+ DB due to exact same issue and estimated time was way off the mark, as it SQL Server was reporting 5hrs to completion, but it only took 90min.
I've seen that and I've seen the opposite, where SQL estimated around 25000 seconds to completion (as it finished phase 1 or recovery), but took another 12 hours to come online.
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
February 13, 2009 at 8:17 am
The current completion value is at 41% after 5 hours. So i am assuming it should be done in less than 61/2 hours. I hopefully dont fall into the other category where the database took another 12 hours to come online.
February 13, 2009 at 8:19 am
A great article on this, albeit a bit late, from Bob Dorr can be found HERE. Might be worthwhile to read so that you can speak intelligently about the recovery time to management.
Hope this helps and I do hope things work out ok for you.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 13, 2009 at 8:46 am
Thanks David. That article was really helpful. Hopefully the db is back in action by 6 hours. Cant wait for this day to be over.
February 13, 2009 at 8:54 am
Another note on this too, at the end of the blog post Bob mentions the potential for too many VLF's in the log file and that may be true and should be something to consider for the future. When / if considering I would read this BLOG and this BLOG from Kimberly Tripp. Very informative!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply