May 7, 2008 at 5:34 pm
I'm working on a script to do a pretty major reconfigure of our database and watched the first operation expand the log file from 118mb to 32gb before I stopped the operation. The database is 27gb.
The reconfigure is a one time thing and will be done in single user mode so I'm really not worried about a log file - is there anyway to turn logging OFF while I'm doing the reconfigure, then back on when I'm done?
Thanks,
Mega
May 7, 2008 at 5:44 pm
You cannot turn off logging. without logging you would not have a proper DBMS.
you can set to a different recovery mode which will minimize logging. simple recovery mode, for example, will truncate the tlog after every transaction.
---------------------------------------
elsasoft.org
May 7, 2008 at 5:58 pm
jezemine (5/7/2008)
You cannot turn off logging. without logging you would not have a proper DBMS.you can set to a different recovery mode which will minimize logging. simple recovery mode, for example, will truncate the tlog after every transaction.
Yikes - I might be in trouble. The first operation expands the log to at least 32gb - I may actually run out of disk space.
May 7, 2008 at 6:12 pm
what is the first transaction? is it a big delete or update? if so you can always do this in batches to minimize the size of a transaction.
for example, if you are deleting 100m rows, delete them in batches of 10k each or something. you can do this in a while loop or similar.
---------------------------------------
elsasoft.org
May 7, 2008 at 6:54 pm
Batching is good, but you'll need frequent log backups to keep the size down.
The other thing is to set the database into simple mode after it's in single user mode and then if you batch the transactions will be thrown away. When you're done you can set it back to full mode and take a full backup immediately.
I would recommend batching and log backups if this is production data.
May 8, 2008 at 12:16 am
jezemine (5/7/2008)
what is the first transaction? is it a big delete or update? if so you can always do this in batches to minimize the size of a transaction.for example, if you are deleting 100m rows, delete them in batches of 10k each or something. you can do this in a while loop or similar.
This might be the way to go, I'll give it a try.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply