Index rebuild maintenance eating up file system space

Question

We have a relatively large SQL Server 2008r2 database (1.5tb).  In running the index rebuild this weekend, the process was eating up file system space even though the 3 index files had ample space available in them.  Two had 50% free and the third had 23% free.  The process was eating file system space at the rate of 1gb every few minutes.  We ended up having to kill the process.

Why was the system not using the space available in the files?  We were not hung or stuck, it was just very hungry.  We just upgraded this database and do not have another of this size on this version yet.  Any insight appreciated - maintenance is a must for this large, critical DB.

---

Dianne

PS - there are no logs on this drive or temp space - mdfs only.

Answer 1

Index rebuild or reorganize would generate relatively large amount of transaction logs hence you would see a growth in tlog files (those .ldf files). To let the process reuse the space in tlog file, you should have a job which periodically (say every 15 mins or so) takes tlog backup for the database against which index maintenance is being performed.

Alternatively, if that is a Rebuild (Not reorganize) you may switch the database recovery model to SIMPLE as it won't log anythign into the tlog file. Beware, this would break your tlog backup chain, once Index rebuild is over you immediately need to take a Full backup of the database after you switch back to FULL recovery model...etc..I wont recommend this method but just mentioned for the sake of it..

---

Prashant [MSFT] -- This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

We just upgraded this database and do not have another of this size on this version yet.  Any insight appreciated - maintenance is a must for this large, critical DB.

as you've upgrade the database from an older version, did you update the database statistics, database compatibility level and did you execute dbcc updateusage to update internal settings?

Do improve performance of rebuilding the index you could use sort in tempdb option.

If you're rebuilding CLUSTERED INDEX you sould probably disable NonClustered Index to save space in the database file. after rebuilding all clustered index you can rebuild the NC and use free space in the database files. If you've large&many nonclustered index it is faster to rebuild the NC in a second phase especially if you're using uniqueidentifier datatype for clustered index.

Answer 3

There are no logs on this drive - other ideas?

---

Dianne

Answer 4

yes, we updated stats, compatibility, etc.  I do use sort in temp DB

The indexes in this vendor application are by and large not clustered.

---

Dianne

Answer 5

The indexes in this vendor application are by and large not clustered.

hi Dianne

do I understand you correctly that you're tables don't have a clustered index and therefore you're using heap tables?

how are you rebuilding the index and how many records are in the largest table?

if you're rebuilding a heap table each index is being rebuild as the index does use the physical location of the record and when the record is moved around the index must be updated.

Answer 6

Hi Dianne,

How are you rebuilding the indexes? Online or Offline? What is the largest table you have on that database? Are you using SORT_IN_TEMPDB? How much space is available for tempdb? Where are you seeing the growth rate of 1gb every few mins? Specifically which file (user database transaction log, tempdb growth, user database file growth?) The amount of free space needed by SQL Server depends on several factors including whether its offline/online/sort_in_tempdb/clustered index/non-clustered index etc...  So you need to isolate and figure out alot of the above information. I would encourage you to catch up on the whitepaper listed at http://technet.microsoft.com/en-us/library/cc966402.aspx.

Some key points taken from that wp.

Determining the Amount of Temporary Space Used

As discussed earlier, the amount of temporary space used depends upon various factors. The following list summarizes the important items to note:

• For offline create or rebuild operations, tempdb is used only for sorting when SORT_IN_TEMPDB is ON. By default this option is set to OFF and the sort takes place in the space allocated to the user database.
• For online create or rebuild operations of a clustered index, additional temporary space is used for the mapping index.
• When you rebuild a clustered index, sort runs are not needed because the data is already sorted. Therefore, no temporary space is required. However, when you create a clustered index, sorting is required, and temporary space is used for the sort runs.
• Temporary space for sorting is not required when you rebuild a clustered index offline because the data is already sorted. In addition, the offline operation does not create a temporary mapping index.
• Temporary space for sorting is not required when you rebuild a nonclustered index online because the data is already sorted.
• Partitioning and index uniqueness do not affect the amount of temporary space used.

The following table summarizes when and how space is used for index create and rebuild operations in tempdb or in a user database.

Table 1   S = sort runs, M = mapping index, V = version store

Operation is Online	SORT_IN_ TEMPDB is On	Temporary Space for Clustered Index Create	Temporary Space for Clustered Index Rebuild	Temporary Space for Nonclustered Index Create	Temporary Space for Nonclustered Index Rebuild
Yes	Yes	S, M, V in tempdb	M, V in tempdb S not required	S, V in tempdb M, not required	V in tempdb
Yes	No	S and M in user database V in tempdb	M in user database V in tempdb S not required	S in user database V in tempdb M not required	V in tempdb
No	Yes	S in tempdb M, V not required	None required	S in tempdb M, V not required	S in tempdb M, V not required
No	No	S in user database M, V not required	None required	S in user database M, V not required	S in user database M, V not required

Table 2 illustrates the amount of temporary space used with respect to the size of the index for indexes of different width. That is, it shows the ratio of temporary space to the size of the index (temporary space used / index size). For the purposes of this example, we assume that the total row size is 46 bytes and the indexes are defined as follows:

• A single-column index on an IDENTITY column. The indexed column is 4 bytes, or approximately 8% of the row size. This is an example of a narrow index.
• A composite index of three columns. The total size of the indexed columns is 10 bytes (total 46 bytes in the row), or approximately 25% of the row size. This is an example of a typical index.
• A composite index comprising all columns. The total size of the indexed columns is 46 bytes or 100% of the row size. This is an example of the widest possible index. Such an index is not very common on large tables, but is used here as an example of the worst-case scenario.

The space required for the version store is not included in this table.

---

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 7

As stated previously - yes using sort in tempdb, yes doing online reorgs.  Our tempdb is made up of 5 20gb files, so 100gb total.  Space was being consumed on the drive where only mdfs live.  We have our data segregated by file type on multiple separate drives.

Folks, please read carefully before responding off the cuff.  I too can search and read.  If it was easy, I wouldn't be posting.

---

Dianne

Answer 8

Hi Dianne,

>>Folks, please read carefully before responding off the cuff.  I too can search and read.  If it was easy, I wouldn't be posting.<<

I did read your post more than few times and didn't see the information that's necessary/relevant to give the solution that just works for you.

Thanks for sharing the tempdb size and that you are doing online reorgs in your last post. That's helpful but yet, you haven't shared all the info here like your largest table/index size. That is the key here. While doing the online re-orgs, SQL Server has to maintain the mapping index and that mapping index size depends on the size of the table/index.

You may also need to watch tempdb datafile growth over time [100GB may NOT be enough for tempdb sometimes] and size it appropriately to handle the largest transaction (usually index rebuild for some systems) without any automatic growth. If you haven't set Instant File Initialization correctly then you may be having other issues as well.

---

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 9

Yes - please help - don't understand where you're going.  Each index datafile had much empty space - and were not growing.  File system space was being consumed.  Nothing but mdfs live on this drive.

---

Dianne

Answer 10

Do your tempdb mdf's live on the disk being consumed?

How large is the largest table you are rebuilding?

Are you rebuilding tables one at a time?

Are you rebuilding all, including the clustered index?

Gigabytes are really cheap these days, are you sure there was really any problem?  It can take many hours to rebuild a table with, oh, say 500gb in 50m rows, or thereabouts.

Josh

Answer 11

Hello Dianne,

Assuming your first part of your query is answered about space usage. If not then please let us know.

For later part of your question, I would like to share my experience on sized databases & situation. We drilled into the database & sat with application team to identify major tables. Looking at the size of the db it has been impossible to complete maintenance of whole database on a weekend. For the same reason we distributed the jobs like

1. Weekend Number 1 - We reindexed first subset of transaction tables. Update Statistics for master tables.
2. Weekend Number 2 - We reindexed second subset of transaction tables. Update Statistics for first subset of transaction tables.
3. Weekend Number 3 - We reindexed all master tables. Update Statistics for second subset of transaction tables.
4. Weekend Number 4 - We reindexed third subset of transaction tables.Update Statistics for first & second subset of transaction tables.

Because on 4th Weekend we are left with least number of tables to be reindexed, statistics update on preview 2 subset of transaction table completed on-time for us.

In your case we can try similar multiple permutations & combinations.

Hope that helps.

Answer 12

As others have already pointed, do not rebuild indexes blindly ... I have been using Ola's utility for many years on critical environments without any problems . Take a look at this  http://ola.hallengren.com/

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 13

I have worked on this system for close to 10 years.  Nothing I do with it is done blindly.

JR - Again - no tempdb or log files on this drive.  Only data/index mdfs.  Was there really a problem?  Yes, we were eating file system space at a rate of a gb every couple minutes and were down to less than 10% free on the drive.  We were alerted to the low space issue as is appropriate.  The issue is the index files were more than 50% free.  With free space in the files - why were we eating file system space?  This is the question. 

Sankar - largest table is 445gb, largest index 118gb

Anuragsh - I have done this in the past, but was hoping to be able to do all in a weekend with online capabilities in the new version.  There are 135 indexes I do regardless, as their data is very volatile and they frag quickly.

---

Dianne

Answer 14

I have worked on this system for close to 10 years.  Nothing I do with it is done blindly.

JR - Again - no tempdb or log files on this drive.  Only data/index mdfs.  Was there really a problem?  Yes, we were eating file system space at a rate of a gb every couple minutes and were down to less than 10% free on the drive.  We were alerted to the low space issue as is appropriate.  The issue is the index files were more than 50% free.  With free space in the files - why were we eating file system space?  This is the question. 

Sankar - largest table is 445gb, largest index 118gb

Anuragsh - I have done this in the past, but was hoping to be able to do all in a weekend with online capabilities in the new version.  There are 135 indexes I do regardless, as their data is very volatile and they frag quickly.

---

Dianne

OK, I was still unclear because tempdb has a .mdf file, too, so when you kept saying "mdf's only" it did not exclude tempdb.  I now gather all the files for tempdb are on other devices or LUNs.

But I still don't really know if you have a problem, other than too small a disk drive.  Rebuilding the clustered index of a 445gb table is liable to require on the order of 445gb of temp space either in tempdb or in the main mdfs.  Online building might eat the elephant in smaller bites, but it is going to run slower, which is not a good thing when your table is 445gb.

Can you run this in dev with enough resources and simply observe what it takes to get it done?

Josh

Answer 15

The job ran successfully for several weeks until this last weekend.  Yes, it runs in dev just fine.

Again, and perhaps this is too deep an internals question for this forum, is why when there is 50% free space in the index file (should be able to duplicate all in the file) we were eating file system space.

---

Dianne

Answer 16

Have you tried disabling the non-clustered indexes before rebuilding them?

Seth

---

http://lqqsql.wordpress.com

Answer 17

Again, and perhaps this is too deep an internals question for this forum, is why when there is 50% free space in the index file (should be able to duplicate all in the file) we were eating file system space.

do you've this deep knowledge ?

you would probably get helpful answer if you read all the answers and answer question directed to you - see 5h reply in this thread.

Answer 18

Hi Seth - no - haven't heard of that one.  What does this do for me?

---

Dianne

Answer 19

Daniel, I mean no offense, but I am getting  the same questions over and over.  If I have missed a question that needs answered, please advise, but I seem to keep answering the same things over and over.

Perhaps as JR suggested, I just need more space, however my wish is to understand the workings and the whys.

---

Dianne

Answer 20

ok, lets repeat my two answers/question here again:

If you're rebuilding CLUSTERED INDEX you sould probably disable NonClustered Index to save space in the database file. after rebuilding all clustered index you can rebuild the NC and use free space in the database files. If you've large&many nonclustered index it is faster to rebuild the NC in a second phase especially if you're using uniqueidentifier datatype for clustered index.

and

do I understand you correctly that you're tables don't have a clustered index and therefore you're using heap tables?

how are you rebuilding the index and how many records are in the largest table?

if you're rebuilding a heap table each index is being rebuild as the index does use the physical location of the record and when the record is moved around the index must be updated

probably you understand the proposal to disable NC index and eventually my question about heap table. Especially disabling NC index during rebuilding CI  and rebuilding the NC later would require less space and is usally faster.

Answer 21

The indexes in this vendor application are by and large not clustered.

Yes, heap tables for 98% of the database.  I am using the rebuild index task with sort in tempdb and keep online both checked.  The largest table has 642,714,068 rows.  I do understand the heap tables need space in the physical location.  My index files had 50% free, but the file system space was still being used up by the process.  This is what I can't get clear.  Should it not have been using the available space in the index file for the rebuild? 

Our max dop is set to 1 per the vendor, so I can't blame parallelism either.

As my friend Charlie Brown always says - ARGH!!!!

---

Dianne

Answer 22

Just to add in to this--From SQL 208+ what ever the May be the database recovery model the rebuild index operation is fully logged....

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 23

have a look at this artical

http://msdn.microsoft.com/en-us/library/ms179542.aspx

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 24

Hi,

Disabling the index keeps the meta data (index definition) but deletes the actual index data - this means you have the space for that index when you rebuild it. Rebuilding will also bring the index back on line.

Don't do this for clustered indexes as it will lock out the table and won't save any space becuase it sits over the actual table data.

Seth

---

http://lqqsql.wordpress.com

Answer 25

The index rebuild ran successfully last weekend with no file system space issues.  The weekend before it was the victim of another process.  Thanks everyone for replies and help.

---

Dianne