4

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

Improve this question
2
  • 4
    >>>i have one index that I have been running for more than an hour in MyDatabase and that database is rapidly increasing in size. <<< What is the size of this index? If you rebuild 300Gb of index even with sort in tempdb, your server will first build a new index of 300Gb and only then drop an old index, so your db will have 300Gb added to its size Commented Oct 28, 2019 at 10:35
  • What is the fill factor for the index? If the index was highly fragmented then defragmenting it after a long period may cause the index to be larger than when it started. A log fill factor could reduce fragmentation at the cost of more disk space. Defragmenting the indexes will cause the engine to make sure that each page has the specified % free. Commented Oct 28, 2019 at 12:06

1 Answer 1

Reset to default
2

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:

  1. Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking) by Paul Randal
  2. Index Operations That Require Additional Disk Space
  3. Transaction Log Disk Space for Index Operations
Improve this answer
1
  • Thanks. These links were useful. Commented Nov 1, 2019 at 1:18

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.