February 17, 2013 at 1:00 am
An archiving process that moves data from my prod db to archive an db consumed my entire log drive. Both dbs logs are on the same drive. This is a 2000/SP4 cluster active/passive.
I moved the the active cluster node over, probably a bad idea in hindsight.
Prod is in full recovery mode. Archive in simple.
Right now now archive is in recovery (slow). I attempted to take prod offline to move its log to another drive. This is in progress but taking a long time.
Suggestions welcome...
February 17, 2013 at 1:34 am
I managed to shrink my prod logs to create some space. The archive db is still recovering, I'll let it go.
February 17, 2013 at 3:26 am
Restarting SQL while a process is rolling back (as it would have been after running out of log space) is indeed a bad idea as the rollback then has to finish with the DB unavailable
Wait. There's little else you can do here.
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 17, 2013 at 5:50 am
Mrs 500
Now i am going to explain why it is taking more time to recovery database?
First Question:It’s not just the amount of transaction logs you need to consider, you also need to take into account the space the transaction log management system will “reserve” to allow for proper transaction rollback. If a transaction generates 100MB of transaction log records, the system will reserve approximately 100MB of empty space in the transaction log to guarantee it can abort the transaction and correctly roll back. It’s a safety mechanism to prevent a database becoming inconsistent. This is why you may have seen the transaction log grow, even though you think you’ve given it enough space for the largest transaction.
So please check the transaction log disk space .otherwise drop the database using master single user mode then resorte the database
February 19, 2013 at 8:33 am
The archive db did recover. I then shrunk its log. I expanded the prod log to a reasonable size given it normal processing/backup schedule.
Pretty resilent system despite my somewhat misguided actions.
The vednor provided archive process (rbar) clearly needs to be run with smaller date increments.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply