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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.