We need to shrink a 600GB database. The vendor has numerous fields defined as Decimal (28,12). They approved changing all Decimal to VarDecimal. This change will save us over 400GB of space. Some of the larger tables do not contain clustered indexes and as a result the shrinkfile could take more then 14 hours. We found that adding a clustered index to the largest tables caused the shrinkfile to take about 1.5 hours in our test environment. Why does a shrinkfile operation perform better with clustered indexes then heaps? I know the data is reorganized to the front of the database during shrinkfile, but I don't know why a clustered index would make a big difference. Thanks, Dave
Only I think of when you create the clustered index data is organized based ont he key and it will be fast to move continuouse data than the random one.
It is nice to hear the safe in storage due to vardecimal format. The Vardecimal was good compression for decimal fields starting from SQL 2005 SP2. but i was surprised, when sql 2008 is released and microsoft say: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ROW and PAGE compression instead. For more information, see Creating Compressed Tables and Indexes. review: http://msdn.microsoft.com/en-us/library/bb326755.aspx It is necessary when using vardecimal, to study the limitations of the Vardecimal Storage Format in http://msdn.microsoft.com/en-us/library/bb326755.aspx
Yes. We noticed it will not be supported after 2008. In this particular case it is vendor code so we don't need to be concerned about anything beyond 2008. We're just happy to get significant disk space back. Dave
Have you tried REORGANIZE or REBUILD that indexes (On tables) then SHRINK the LOG file, as it performs better in any case.