Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2005-09-18 : 23:23:14
|
Hi everyone, i'm not sure if you've responded to this post last friday at the Yak Corral, unfortunately the post was not recovered  , is this an epidemic perhaps?  can you re-post your comments, i'd appreciate your effort.this is what happened.i set the database log to autogrow coz i thought i needed to because the transactions are so big and i did not batch them, this didn't solve my problem coz the job hanged (does not progress but does not fail). So I killed the process. Big mistake on my part! It totally blocked all transactions to this database, so my last choice was to restart the services. Another mistake (inevitable perhaps) coz the server tried to recover and checkpoint it and this ran for 2 hours plus. This is a reports server so reports got delayed.To MS members here, is there a way to force the server not to checkpoint the log file? Like starting fresh and not recovering whatever transactions were being made?TIA--------------------keeping it simple... |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-19 : 03:13:57
|
| RESTORE and roll forwards (to just before the problem)?Kristen |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-19 : 04:38:18
|
unfortunately, you can't touch the database until it has fully recovered during start up  , that was one of the problems i've encountered, all i can do is to monitor the server activity (checking if it has fully recovered) through the logsi would have restored in on another server but it was faster to wait for it to recover than restoringquote:
Originally posted by Kristen
RESTORE and roll forwards (to just before the problem)?Kristen
--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-19 : 05:05:42
|
| "you can't touch the database until it has fully recovered during start up"I was thinking along the lines of marking it OFFLINE, or just renaming the MDF/LDF temporarily to get it SUSPECT.But maybe that's a rubbish idea ...Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-19 : 22:49:28
|
| hmm.. i'll need to play around with that idea, see what happens to the database, i think it'll be a blast though --------------------keeping it simple... |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-09-20 : 12:42:10
|
quote:
Originally posted by jen[To MS members here, is there a way to force the server not to checkpoint the log file? Like starting fresh and not recovering whatever transactions were being made?
No - there's no supported way to prevent recovery (and any unsupported way will leave your database in an inconsistent state)Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-20 : 23:41:45
|
| thanks Paul,haven't replicated the incident yet, guess i'll just take your word for it and try to avoid this type of scenario like the plague in the future--------------------keeping it simple... |
|
|
|
|
|
|
|
|
database recovery