We have a customer who is seeing large amounts of unused space in a data
table.  sp_spaceused reports data=1900408 KB  index=749744 KB  unused=2921024
KB
The table schema/indexes are
CREATE TABLE Table1 (ColD INT, ColE INT, ColF INT, ColG INT, ColC INT, ColA
INT, ColB IMAGE);
CREATE UNIQUE INDEX Table1_Index2 ON Table1 (ColD,ColF,ColE);
CREATE INDEX Table1_ColF_ColG ON Table1 (ColF, ColG);

Our application is adding on average roughly 12,000 records per day to this
table, making about 2,500 updates, and 25,000 queries.  Records are never
deleted.

We are using isolation mode “read uncommitted” when we do this.  For nearly
all other database operations we execute in “read committed” isolation mode.

We recommended they recreate the table with BCP and that recovered the
space.  However, the database immediately began to grow again, with unused
space in this table still the culprit.

They are using SQL 2k.

Any ideas on why so much unused space, and what we can do about it?

Is autogrow set to on and what is the percentage?
Also you can shrink the DB by using DBCC SHRINKDATABASE or DBCC
SHRINKFILE

http://sqlservercode.blogspot.com/

DBCC SHRINKDATABASE and SHRINKFILE do not help.  Unused space in the table
appears to be unaffected by those commands.

I am investigating what the autogrow setting is.  However, I believe the
problem is with reserved space for data, not the transaction log.

you need to check your fill factors for page and indexes as well - the
autogrow settings are in the properties for the database (right click
the DB name and select properties - you set it up on the tabs for
database and transaction logs), you can also select the autoshrink
property on the 2nd to last tab

Cheers

Steve L
Dominicus Data Systems

1. Autoshrink should be avoided. Please see the following article by Tibor
Karasazi:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

2. Run the DBCC UPDATEUSAGE against your table to be sure that sp_spaceused
is giving you correct values. After that, run the sp_spaceused for your table.

This example reports information about the authors table of Pubs DB.
DBCC UPDATEUSAGE ('pubs','authors')

Let us know if this helped you.

Show quoteHide quote

We already asked the customer to run DBCC UPDATEUSAGE.  It did not affect the
output of sp_spaceused.
Autogrow is set to 10% for both Data Tables and Transaction Logs.  I believe
that's the default.  Other tables in the database do not show such a large
percentage of unused space.  Could it have something to do with the high rate
of inserts?

We also have the same issue as described by Debby.

I have tried all sorts of commands (sp_spaceused @updateusage = 'true',
dbcc updateusage(0), dbcc shrinkdatabase

but nothing seems to solve the issue. We have database where the USED space
is apporx 2.0 GB but allocated space is 10.0 GB (by the way this was 30 GB at
one point) I brought down to 10 GB by running Multiple (more than 20 times)
the DBCC SHRINKFILE (data file, size).

I would appreciate if any once can provide any help/ideas.

Debby, did you find an answer yet?

Regards,

Kamal.




Show quoteHide quote

Performance difference between SQL 6.5 and SQL 2000 server
SQL Server named instance problem with Windows Server 2003
Scheduled Task to restore Database ?
Dynamic memory setting
Restore mdf file