SQL Index rebuild and logging space issue

I currently have a pretty big 500 GB database that has been running for a couple of years never been indexed.  I want to run an index rebuild on it, but my log drive is only about 120 GB.  From what I've tested the rebuild will consume a lot of space in the log.

The database is currently set to full recovery with log shipping.

What can I do aside from extending the drive or moving the log file? Is there any tricks I can do to keep the space down while it's indexing?
ts11Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have indexes larger than 120GB?
Since you said that the database is running for years without reindex task then I think you can start with indexes with more than 90% of fragmentation, then the next day rebuild only indexes with more that 80% of fragmentation and the day after 70% and so on until you reach the 30%. Indexes with fragmentation level between 5% and 30% should be reindexed instead of being rebuilt. This is only the standard recommendation and you can set these threshold for your case.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
My recommendation would be to get an outage window, switch to the bulk-logged recovery model, perform the necessary maintenance operations and switch back to full recovery.

This is highlighted here: https://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

FYI: CREATE INDEX and INDEX REBUILD are minimally logged if the database is in Bulk-logged recovery mode. (Refer: Operations that can be minimally logged - https://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx)

NOTE: Since you are changing the recovery model of the database and log shipping is configured, please test it out first. I believe log shipping will have to be removed before the index rebuild and then setup again afterwards.
0
 
Anthony PerkinsCommented:
Long term you should look into Partitioning.  This way you only have to rebuild the active (much smaller) portion of your database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.