0

I would like to start running regular index maintenance on a server that has been running for a while without it. The database is running in FULL recover with log shipping. The problem I am having is log space. The partition holding the log does not have the room to hold all of the growth that will occur during the index maintenance.

So far I have come up with two solutions. The first is rebuilding the big ones manually during my weekly maintenance window until I get them under control. This will take a very long time and be time consuming to do. The second is switching the database to SIMPLE during my maintenance window. This is not ideal but seems to be my best bet for getting this done in a short period of time.

Are there any other solutions to this problem?

  • What is the cost of extra disks and how does it compare to the cost of manually doing time consuming tasks for days? – Julien Vavasseur Nov 3 '15 at 15:14
  • How often are you taking log backups? – Erik Darling Nov 4 '15 at 2:46
  • @sqldriver The log backups are scheduled to run every hour on the hour. – Chris Albert Nov 4 '15 at 13:03
1

So your database is in full recovery model then? You can script out the rebuild commands for the index and have a pause command to wait for the next log backup to complete. This would rebuild at most one index at a time. It would be slow but could be done during the evening and would get you caught up faster.

| improve this answer | |
  • Yes the database is in FULL recovery. I like your idea. Will have to do some planning to see how long it will take to do that. – Chris Albert Nov 3 '15 at 15:22
  • 1
    watch out for rebuilding clustered indexes, they are the size of the table and might need to wait for your maintenance window. You can use the number of pages in the sys.dm_db_index_physical_stats to get a good feel for the size of the index in an automated fashion. – Jonathan Fite Nov 3 '15 at 15:30
  • If you find you don't have enough room to rebuild the clustered indexes, try using reorganize periodically - you can kill the reorganize job and all the good work it did will remain. Just kick it off again after the log has been backed-up. – dwjv Nov 3 '15 at 15:53
  • If your tempdb has the capacity for it, using the sort_in_tempdb=on option will offload most of the reindex process to the tempdb and its log file which is in simple mode. Sorting in the tempdb may also prevent the data files from growing in the database being reindexed if they are low on free space. – Aaron Nov 3 '15 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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