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)
Best Answer
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)