I am running Ola Hallengren's SQL Maintenance scripts using the following command:
EXECUTE [dbo].[IndexOptimize]
@Databases = 'MyDatabase'
, @FragmentationLow = NULL
, @FragmentationMedium = 'INDEX_REBUILD_OFFLINE'
, @FragmentationHigh = 'INDEX_REBUILD_OFFLINE'
, @FragmentationLevel1 = 10
, @FragmentationLevel2 = 30
, @UpdateStatistics = 'ALL'
, @OnlyModifiedStatistics = 'Y'
, @LogToTable = N'Y'
, @TimeLimit = 23400
, @MinNumberOfPages = 1000
, @Indexes = 'ALL_INDEXES'
, @MaxDOP = 7
, @SortInTempdb = 'Y'
Up until recently a number of the indices were unable to rebuild due to space constraints so I shifted tempDB to its own drive, configured tempDB to use 8 different files, and started sorting in tempDB (each tempDB file is roughly 10GB and growth set to 500MB each). Some indexes that were difficult to rebuild previously were able to complete relatively quickly, however, i have one index that I have been running for more than an hour in MyDatabase and that database is rapidly increasing in size. Nothing else is currently running and TempDB has not increased in size at all.
Can anyone help me figure out why MyDatabase is increasing so rapidly? Does anyone have any suggestions about what it could be causing this?
Running SQL Server 2012 if that helps.
Thanks
Best Answer
I have quoted three sections from different articles and gave the links at the bottom. Bottom line is you need space for the new index to get build plus transaction log space for rollback. If you cannot accommodate the space required you might want to consider INDEX_REORGANIZE.
When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.
Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy. The pages and extents required to do this will always be allocated as needed, as with any other operation in SQL Server.
Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. For more information, see Transaction Log Disk Space for Index Operations.
Reference: