How Do You Deal with a FULL Recovery DB when need to do simple or Non-Logged operations

Question

How Do You Deal with a FULL Recovery DB when need to do simple or Non-Logged operations

YSLGuru

SSC-Insane

Points: 21661
More actions
April 11, 2010 at 2:00 pm

#217733

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!

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 2

Though i would imagine that wouldnt be desirable if you used mirroring, like we do.

Adam Zacks-------------------------------------------Be Nice, Or Leave

jasonmorris SSCrazy Points: 2492 More actions · Answer 3

jasonmorris

SSCrazy

Points: 2492

April 12, 2010 at 7:52 am

#1150044

Hears mirror shatter.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 4

jasonmorris (4/12/2010)
Hears mirror shatter.

Sorry, what do you mean?

Adam Zacks-------------------------------------------Be Nice, Or Leave

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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).

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 6

Thanks Lynn,

We sit 4 feet from each other. Just playin silly-buggers 😉

Adam Zacks-------------------------------------------Be Nice, Or Leave

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Schadenfreude-Mei (4/12/2010)
Thanks Lynn,
We sit 4 feet from each other. Just playin silly-buggers 😉

Hard to tell from here. Thanks. 😉

YSLGuru SSC-Insane Points: 21661 More actions · Answer 8

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!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

There is no way to turn off logging. Even in the SIMPLE recovery model updates, deletes, and inserts are still logged.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 10

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.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 11

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!

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 12

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...

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 13

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

Nadrek SSC-Insane Points: 20039 More actions · Answer 14

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.