April 11, 2010 at 2:00 pm
Just a curisoity question .... If you have a DB (or morethen 1) that you have set to FULL for the Recovery Model, what you do you do if anything when you neddto perform some non-logged operation like a large bulk import? If you havethe space then of course you can leave the thing as is and let the log file grow till its hearts content and then after your done with your import you can manage the log file.
I'm curiouss if anyone has this kind of scneario, a FULL DB that has non-logged opertaions (like a bulk import or any other intense operation that doesn't require everythingto be logged) that you'd rather not log; a DB where you wish it could act like it was set to SIMPLE during these bulk operations and setto FULL the rest of the time.
We do and we manage this fine but I gotta believe that there may be a better, more effecient way to manage this that one of the gurus on these boards has come up with.
Comments? Feedback.
Kindest Regards,
Just say No to Facebook!April 11, 2010 at 7:28 pm
From what I understand when people want to perform bulk operations on a database using the Full Recovery model and minimize the impact on the t-log, they switch to bulk_logged model, run the process (bulk insert or rebuild index), then switch back to Full Recovery model, then run a t-log backup.
April 12, 2010 at 6:06 am
Though i would imagine that wouldnt be desirable if you used mirroring, like we do.
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 12, 2010 at 7:52 am
Hears mirror shatter.
April 12, 2010 at 8:05 am
jasonmorris (4/12/2010)
Hears mirror shatter.
Sorry, what do you mean?
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 12, 2010 at 8:40 am
Schadenfreude-Mei (4/12/2010)
jasonmorris (4/12/2010)
Hears mirror shatter.Sorry, what do you mean?
It's a joke. Changing the recovery model would either fail, or break the mirror (hears mirror shatter).
April 12, 2010 at 8:43 am
Thanks Lynn,
We sit 4 feet from each other. Just playin silly-buggers 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 12, 2010 at 8:45 am
Schadenfreude-Mei (4/12/2010)
Thanks Lynn,We sit 4 feet from each other. Just playin silly-buggers 😉
Hard to tell from here. Thanks. 😉
April 12, 2010 at 2:01 pm
We don't mirror so we also do the 'switch' thing but I was hoping there might be somtheing better.
Thanks
Kindest Regards,
Just say No to Facebook!April 12, 2010 at 2:07 pm
There is no way to turn off logging. Even in the SIMPLE recovery model updates, deletes, and inserts are still logged.
April 12, 2010 at 2:14 pm
The thing that we've done in the past is schedule a bunch of log backups during the import time. Then make sure that your imports are in smaller batches, not one large insert. That has been better than bulk insert at times.
Or break up your bulk inserts into a series of loads.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 12, 2010 at 3:37 pm
Lynn Pettis (4/12/2010)
There is no way to turn off logging. Even in the SIMPLE recovery model updates, deletes, and inserts are still logged.
Yeah, I'm not trying to stop all logging, just minimize it as much as possible.
While I gave BULK IMPORT as an example, what we are doing now is not Bulk Imports (although we are going to be doing that soon to which is why I threw it out as the example) but significant Index Rebuilds. By significant I mean that a lot gets logged even in the BULK_LOGGED mode; not that a lot of Indexes are being rebuilt at once.
I realize that you can never have a truly automated 'never need to check on' Log file if you use FULL or BULK_LOGGED but you can minimize how much hands on work you gotta do by setting the Log file to the largest size you expect it to grow to assuming you have that kind of space and we do at this point.
Thanks
Kindest Regards,
Just say No to Facebook!April 12, 2010 at 6:17 pm
I've posted about this before, but it's relevant to this discussion too...
We have a mirrored database in production which is reindexed once a week - the reindexing causes the log file to grow from 2GB to about 21GB. The reindexing is done by a vendor-provided stored proc and can't be changed (warranties etc).
There is insufficient disk space to just let the log file remain at 21GB, so the morning after the reindexing I execute a DBCC Shrinkfile against it and shrink it back down to 2GB - this is usually run manually which is a pain. Running the shrink as a sch. job has never been successful as it usually requires one or more log backups to be taken before the log will shrink. Changing recovery models is not an option because of the mirroring...
April 13, 2010 at 3:27 am
Ivanna Noh (4/12/2010)
I've posted about this before, but it's relevant to this discussion too...We have a mirrored database in production which is reindexed once a week - the reindexing causes the log file to grow from 2GB to about 21GB. The reindexing is done by a vendor-provided stored proc and can't be changed (warranties etc).
There is insufficient disk space to just let the log file remain at 21GB, so the morning after the reindexing I execute a DBCC Shrinkfile against it and shrink it back down to 2GB - this is usually run manually which is a pain. Running the shrink as a sch. job has never been successful as it usually requires one or more log backups to be taken before the log will shrink. Changing recovery models is not an option because of the mirroring...
How long have you been following this routine? Apart fromm the fact that you are most likely fragmenting your disk (which in time will cause io slows) but what happens if someone is late, or ill or forgets?
Surely your company must realise that if they 'choose' not to have the re-index sp optimised, they need to accept the requirement for additional storage.
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 13, 2010 at 7:15 am
Ivanna Noh (4/12/2010)
I've posted about this before, but it's relevant to this discussion too...We have a mirrored database in production which is reindexed once a week - the reindexing causes the log file to grow from 2GB to about 21GB. The reindexing is done by a vendor-provided stored proc and can't be changed (warranties etc).
There is insufficient disk space to just let the log file remain at 21GB, so the morning after the reindexing I execute a DBCC Shrinkfile against it and shrink it back down to 2GB - this is usually run manually which is a pain. Running the shrink as a sch. job has never been successful as it usually requires one or more log backups to be taken before the log will shrink. Changing recovery models is not an option because of the mirroring...
Push buying more disk space harder?
Make sure the t-log is growing in very large chunks (or do a deliberate resize in perhaps 8GB chunks right before the reindexing)
Alternate: in your scheduled job, do a t-log backup, shrink, t-log backup, shrink, wait, t-log backup, shrink, OS level defrag. If you have to, put some operations in the "wait" step(s) to force the log to roll back to the beginning.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply