July 6, 2005 at 2:28 pm
How much free space is needed in a database for a table reorg using DBCC DBREINDEX. The table has only a clustered index. I thought if you used a drop and create index approach to reorg you needed ~120% of the table size free. Does this hold true if using DBREINDEX? Thanks in advance
July 6, 2005 at 3:39 pm
I finally found my answer to this question with the following white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It states:
All work done by DBCC DBREINDEX occurs as a single, atomic transaction. The new indexes must be completely built and in place before the old index pages are released. Performing the rebuild requires adequate free space in the data file(s). With not enough free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction. For clustered indexes, a good guideline is: Required free space = 1.2 * (average rowsize) * (number of rows).
This white paper is a good read regarding fragmentation.
July 7, 2005 at 9:13 am
The formula might not give you enough space due to database lack of total database free space and database fragmentation. I've had success 99.44% of the time with the following method:
execute sp_spaceused table_name
reserved Kb * 1.4
if your database has that many Kb free then you'll be fine.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 8, 2005 at 1:53 am
Also it depends how your table is actually fragmented (DBCC Showcontig).
If your table/index is very fragmented, then you will need more space for your tran log
Bye
Gabor
July 8, 2005 at 7:57 am
Outback,
Thanks for the reference, that's good information.
Rudy,
Do you know how fragmented things are when 1.4 has worked? Slightly? Severely? I've seen this questions before and it would be good if that worked as a guideline with some idea of xxx% frag in xxx% of the db (space wise)
July 8, 2005 at 2:36 pm
The overall fragmentation of either the table or the database has no bearing on the formula. It's the amount of unallocated (free) extents in the database that really count (I also discount the use of mixed extents since we cannot control them). If your table takes up 50K in data and 20K in index one thinks 70K in total space is used. However SQL allocates in extents so 50K of data actually allocates one 64K extent. Likewise 20K of index allocates another 64K extent (128K reserved, 70K used). The multiplier 1.4 is arrived at old documentation from both SQL and Sybase in referring to rebuilding clustered indexes. Each suggested 1.2 as the multiplier to use times space used for the clustered index to be rebuilt. I added an additional .2 to the multiplier after many failed attempts in medium to large tables where I had used the original 1.2 as a multiplier and experienced faulire. Then upped the multiplier to 1.3 and still experiencing failure. Too many late nights and rerun production 'specials' just made me use 1.4 as the multiplier for the free space calculation and since then, nary another failure. So sorry to disappoint you Steve, no real hard facts on fragmentation severity or percentages to 'trigger' the formula, just experience.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply