May 31, 2004 at 11:41 am
Hi all,
Just managed to re-run my month end procedures but still frustrated that such error occured in the first place!
Alright... here's what happened:
1. I have a proc that execute sub-proc.
2. First step, I put the database in single mode (cos I found that the whole proc takes much lesser time!!)
3. After all the sub-proc have been executed, the database is set to multi user once again
But....
it failed in between... because the log file was full!!! In fact, administrator set the log file to grow up to 500Mb... works fine for normal day end but at month end, there's simply too many inserts/deletes being done.
... and the database was still in single mode. At first I panicked because I could not even have access to the database from EM... but somehow, after clicking here and there, manage to reset it to multi user.
So...
1. Was it possible to use transaction/rollback for such errors?
2. If the main proc contains 20 sub-proc, do I need to check for errors after each sub-proc? and then issue a rollback on error? (though for my case, the error - log file full - happened in the midst of an insert sub-proc)
3. Any general suggestion to prevent this from happening.... THis is haunting me because I have always been "claiming" that with SQL, we can detect the errors before they occur!!
One more issue I faced
1. I did not use transaction/rollback etc because I was calling 20 sub-proc which themselve could be calling other proc.... Would roll back really roll back ALL transaction done all the way until the last sub sub sub proc? (if ya know what I mean)
Thanks in advance for all replies. Time for me to zzzzzz and come back in the morning to find out whether I missed something!
June 3, 2004 at 8:00 am
This was removed by the editor as SPAM
June 3, 2004 at 11:21 am
I think that you mighte be having some locking issue. When you put your database in single user mode, you are the only user and hence the procedure runs faster. But when it is in multi-user mode, your updates might be waiting by locks held by other. You might want to take a closer look at your procs.
June 3, 2004 at 2:11 pm
bbron,
I'm assuming that your db is in full recovery mode. I would suggest adding steps in between your sub-procs to do a transaction log backup. That would help to keep the log relatively clean.
A couple other options -
You could change the db to allow the log to grow enough to allow your month end processing (or simply manually expand it), then after the processing, shrink the log and set the restriction back to 500 mb ( or simply leave the log at the larger size, since it needs it every month end).
You could set the db to simple recovery mode for the month end processing, then change it back after the processing is complete. If you do this, make sure to take a full backup before processing starts, so you can recover if your processing fails.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply