Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
john.burns
Posting Yak Master
100 Posts |
 Posted - 2007-11-29 : 09:13:23
|
| All,I first ran indexdefrag on a table with 1.5 billion rows.logical fragmentation was at 95%. logical frag went down to 3% with no real effect on disk.DBCC reindex had previously been bombing undetected.Now I've run a reindex on this table:Reindex Job with Fillfactor =100Ran in 3:05Free Disk went from ~150GB before operation to 49GBFile4 went from 347GB to 504GBWhy has so much free disk been consumed by this operation and not released ??????????Is my only choice to shrink data file???thanksEnv.Win2k ENT osSQL 2k5 std 64bit |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-29 : 10:01:43
|
| When you re-index, it builds a new copy of the table at a new physical location in the database file. When you re-index a very large table, it needs about as much space as the original table, so the data files will grow as needed, and the space is not automatically released.If you shrink the data file, it will have to move the database pages for your tables, and you will end up with your table fragmented again.CODO ERGO SUM |
 |
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2007-11-29 : 10:07:48
|
| so in this scenarioa final DBCC INDEXdefrag is needed after a shrink?thanks |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-29 : 10:25:31
|
| You could also just not shrink the data files. If you plan on re-indexing in the future, you will still need to have that amount of disk space available, and expanding the data files will just slow it down.Normally, I would recommend not re-indexing a table that size, unless you have to change the fill factor. Just run DBCC INDEXDEFRAG as often as necessary to keep index fragmentation to reasonable levels. You can run DBCC INDEXDEFRAG while the database is in use, so you don’t need a huge downtime window. If the database is in full recovery mode, DBCC INDEXDEFRAG will generate a lot of transaction log usage, so make sure you are running transaction log backups very often, like every 5 minutes.For a table that size, you should consider only re-indexing or defragmenting indexes that have a large amount of fragmentation. Use the DBCC SHOWCONTIG command to find out which indexes really need to be defragmented.CODO ERGO SUM |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-30 : 02:52:15
|
| You can specify sort in tempdb, that will reduce data file growth during reindex. |
|
|
|
|
|
|
|
|
Disk space after Reindex