Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a transaction database and one of nightly job is to rebuild indexes that followed by a log backup and then shrink the log.

Is this safe to do so ?

Thanks Antony

share|improve this question
add comment

2 Answers 2

Why exactly are you choosing the shrink the log routinely? Is it because you need to reclaim volume space? Hard disk space is so cheap that shrinking the log really shouldn't be a go-to maintenance item. Especially since you are seeing one of the main reasons not to shrink the log: Because your logged operations actually do need that additional log space.

Stop shrinking your log. Find the extra space somewhere so that your transaction log can remain that larger size. You haven't given us any numbers (initial size, size the log file eventually grows to, etc.) but the growth operation is expensive and can be time consuming. Log files can't even benefit from a file allocation optimization like Instant File Initialization.

If you are worried about the index rebuild, it can be a minimally logged operation and you could utilize the bulk logged recovery model. In that case, you could take a log backup prior to, then switch to the bulk logged recovery model, do your index rebuilds, then set your recovery model back to full. This could potentially prevent you from doing a point-in-time recovery during that time span that you are in the bulk logged recovery model, so that may be a DR consideration.

There are strategies here. But shrinking your log every night should be on the bottom of that list.

As for the rest of your question, rebuilding indexes (depending on their fragmentation level, your maintenance window, etc.) is a prudent and common maintenance task.

share|improve this answer
add comment

Depending on what you mean when you say "shrink the log", because ideally the log shouldn't be shrunk if that means you keep making the file physically smaller only for the sql server to increase it again during daily usage, which imposes unnecessary strain on the server. (however, I know at times it's actually needed to shrink due to issues with space and uncontrolled log growth, but ideally it shouldn't be needed :) )

Also rebuilding indexes is generally said to be a good thing as it helps keep statistics up to date - but if you (need to) rebuild nightly I'd think there might be other issues you need to address. I'd think a nightly rebuild might be overkill, without knowing the specifics.

Also the rebuilding might be pointless. For example check this video by Brent Ozar: http://www.brentozar.com/archive/2013/09/why-index-fragmentation-doesnt-matter-video/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.