Table size extremely excessive

[roccorocks]

I have several tables that have excessive amounts of free space within the pages. I ran the DBCC SHOWCONTIG and on table that has a mear 65,000 records is about 300 MB. Shows about 8KB free per page (35,000 pages). This is on about a 40 column field (has mostely Varchar(50), and no I did not create this table).

The database does a rebuild and reorganize of the indexes once a week. The default fill factor on the DB is 0 (under DB properties) and for the maintenance plan the amount of free space to allocate after the "R&R" is using the default as well (which is suppose to be a minimal amount per page).

This is for SQL Server 2005, it is not on service pack 2.

I have ready a couple articles that have explained a known issue with giving an amount of free space within an "R&R" maintenance plan. Will service pack 2 fix this?

Tim

 

[mrdenny]

If you have about 8K free per page it would appear that the fill factor for the indexes is set to 1 or 2%. This would cause the pages to remain almost entirly empty.

Recreate the indexes will a more filled fill factor of say 80%. This "should" reduce your problem.

How many bytes per record do you have?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[roccorocks]

Hey MRDenny, it is apparently due to a know bug with < SQL Server SP2. When creating a maintenance plan, I used a 10% free space (within the maintenance plan). Well, when you view the TSQL it shows a 10 Fill Factor (I did not bother checking the TSQL when I created the plans, so I would have caught a long time ago)!

I was unaware of this issue (found some articles out there stating the bug). I tried on a SQL Server SP2 server and BAM, it shows the correct 90 Fill Factor. Pretty lame, huh.

Thanks,

TR

 

[mrdenny]

Very lame. Good to know though.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com