MS SQL Server - Recurring problem of unused space in SQL Table

Author

Recurring problem of unused space in SQL Table

Debby

2006-01-24, 9:23 am

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?

SQL

2006-01-24, 9:23 am

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/

Debby

2006-01-24, 11:23 am

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.

Steve L

2006-01-24, 11:23 am

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

Absar Ahmad

2006-01-26, 4:53 pm

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.

"Steve L" wrote:

> 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
>
>

Debby

2006-01-26, 4:53 pm

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?

Kamal Hassan

2006-02-06, 8:23 pm

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.

"Debby" wrote:

> 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?

Sponsored Links