0

I have a database which is 200 GB in size and I have scheduled a Rebuild Index maintenance plan for all databases every Saturday at 2:00 AM.

The job ran for 8 hours and then it failed. I knew while the rebuild operation was in progress, that the database data file size increases.

In my case, the database size got stuck at 655 GB and the rebuild job failed. I have verified the database data file free space which is currently showing 1.5 GB free space.

Can you please help me to reduce the data file size to the previous state? (200+ GB)

Improve this question
2
  • 1
    What version of sql server & which edition?
    – Stijn Wynants
    Jul 26, 2018 at 10:48
  • Your post says you have scheduled a maintenance plan but you tagged Ola's solution. Which are you using? What did you choose for the fill factor?
    – Jacob H
    Jul 26, 2018 at 13:45

2 Answers 2

Reset to default
3

I think you mean that your TRANSACTION LOG is growing (not your DATA files)? Especially when it's a REBUILD of your indexes it would seem strange that only your DATA files are growing.

You should perform a log backup of your TRANSACTION LOG (when in full recovery mode) and then you can do a shrink files of your LOG files to a desired size (with a minimal size taken into consideration; but don't make it too small, or it will start growing again. Key is to determine a good size of your LOG and then leave it that way).

Do a right-click on your database > Reports > Disk Usage. There you can see how the total size is divided between DATA and LOG.

You should perform scheduled LOG backups (every 15 mins e.g.) to avoid this large growth of LOG files. After every LOG backup, the transaction log frees up space for the next transactions.

I would recommend Ola Hallengren backup maintenance solution for FULL, DIFF and LOG backups as it also cleans up old files)

Improve this answer
1
  • I found the issue why the Data file is grown 3 times bigger than its size. The maintenance plan was wrongly set up. Change free space per page to option set to 90% which is causing the index holding extra space.
    – Sunder Bolla
    Aug 7, 2018 at 15:21
1

When rebuilding indexes it will essentially recreate the index from scratch and therefore require all that space again on a temporary basis. Rudimentary example, if you rebuild an index that is 1GB in size then it will need another 1GB in the data file to accommodate the rebuild.

An absolute classic scenario to avoid is where you have a job that automatically rebuilds indexes (thus potentially growing the data file) and then shrinks the data files back down because they got too big - an almost pointless exercise as the shrink will fragment your index and you're back at square one again. I can't find the exact post but someone mentioned on https://www.brentozar.com/blog/ that an index rebuild was the most expensive way to update statistics.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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