SQL Server 7 DBCC DBREINDEX appears to leave excessive free

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server 7 DBCC DBREINDEX appears to leave excessive free

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-18 : 08:06:48

Stuart writes "We run a DBCC DBREINDEX weekly on every table in a SQL Server 7 database with Fill Factor coded at 95% [eg. DBCC DBREINDEX (AccountsManual, '', 95) etc].
Examples of tThe database MDF size stats are below :

Before DBReindex : Total=182613mb , Used=165524mb , Free=17088mb
After DBReindex : Total=190028mb , Used=159715mb , Free=30313mb

I can understand why the Used space can decrease as we do regularly archive data.However I cannot understand why the Free Space is so high. 30313mb is 16% of total MDF, but FillFactor=5% is coded.Should the Free spce correlate to the Fill Factor ?

We have also tried a DBCC SHRINKFILE [DBCC SHRINKFILE (lamda_data2,99)]on the MDF & then a DBCC DBREINDEX on every database table [Fill Factor=95%] in an attempt to reduce the size of the MDF.The MDF size stats were :

Before SHRINKFILE : Total=190028mb ,Used=172474mb, Free=17553mb
After SHRINKFILE : Total=171426 ,Used=171426mb, Free=0mb
After DBReindex : Total=193167 ,Used=162962mb, Free=30205mb

Once again a lot of free space left after the DBReindex (30205mb = 15.6%.

The database contains 922 User tables of which 258 have no primary key , 260 have no Clustered Index and 205 have no index at all (some tables have combinations of the 3).

I have read that DBReindex does not defrag a heap table and that one way of resolving this is to create a clustered index on the heap table and then dropping it.

However none of the large tables in the database are the ones without a Clustered Index.

Are you able to tell me why such a large amount of free space remains after a DBCC SHRINKFILE / DBCC DBREINDEX with a high Fill Factor such as 95% for all tables?

Thanks........Stuart
"

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-10-18 : 15:44:14

In short DBCC DBREINDEX makes a copy of the table/index you are running it on. Only after that copy has been built is the orginal dropped. So, after you run DBCC DBREINDEX, you should have an amount of freespace roughly the same as the object you reindexed.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-18 : 15:58:59

Run sp_spaceused with the following option to make sure you are getting the correct information.

exec sp_spaceused 'MyTableNasme', @updateusage = 'true'

Better yet, run this script to look at your space usage in detail.
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-18 : 16:09:35

The free space in the database after the REINDEX will be about what is needed to reindex the largest table that you are reindexing.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources