ReIndexing without Log File Growth

Question

Post reply

ReIndexing without Log File Growth

The_SQL_DBA

SSChampion

Points: 10494
More actions
October 15, 2008 at 11:46 am

#194657

Is there a way to rebuild indexes on a table without the log file growing too big. There is only one drive with 40GB space left, and the log file exists on a drive with 8 Gb free space and when I rebuild indexes with DBCC Rebuild on this table on the test server, the log file is getting full and the statement is being terminated.
Do I need to create an additional log file on the 40GB drive, if so can I reclaim the space back??
Thanks in Advance!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 11 posts - 1 through 10 (of 10 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

Change the recovery model to BULK_LOGGED just before reindexing.

After reindexing, revert to FULL.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 2

I hope this does not break the log chain.This needs to be done on Prod Db, that's the reason I am leery about this. In case I switch the recovery models do I need to take a full backup on I am back to FULL Recovery model from Bulk-Logged??

Thanks Again!!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Not exactly - It doesn't break the chain, but point-in-time restores are not available while in bullk-logged mode.

This is a decent summary of the differences:

http://technet.microsoft.com/en-us/library/ms189275.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 4

Well I wouldn't need to do point in time restore as I am reIndexing while in the maitenance window.Also it would be started after the full backup is taken.Does this Bulk Logged model require me to take T-Log backups ( my usual practice is hourly log backups) while reIndexing??

Thanks

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Require? No, but you can certainly stay on the same schedule.

If you mean do I need to back up the log files to free up the space inside of the t-logs, then yes - during bulk-logged as during Full recovery, transactions are retained in the log until backed up (and replicated if that is set up).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

U J SSCrazy Points: 2423 More actions · Answer 6

Do take a database backup of after changing the recovery model

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 7

Yeah, for some bigger indexes I have got into the routine of setting the DB to simple recovery model, running the reindex, and then reverting to full recovery prior to the daily full backup.

I would assume you have to do this out of normal hours or during scheduled downtime to prevent the tables locking up anyway, so just take a log backup before you change the recovery model and you'll be covered.

Apollo74x Ten Centuries Points: 1250 More actions · Answer 8

Matt Miller (#4) (10/16/2008)
Not exactly - It doesn't break the chain, but point-in-time restores are not available while in bullk-logged mode.
This is a decent summary of the differences:
http://technet.microsoft.com/en-us/library/ms189275.aspx

I was thinking about doing this but our production data is being replicated. Do you see any issues?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

No issues.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Apollo74x Ten Centuries Points: 1250 More actions · Answer 10

I've added this to our defrag process last night and the log file didn't grow much at all.

Thank you.