sp_spaceused reports over 40 GB unused for a table

[spilich]

I have a large table that I recently purged a year of data from.
However, the table size in sp_spaceused hasn't decreased as much as I
would expect. (there are no text or large object columns on this
table, BTW) Running sp_spaceused on the table shows the following.

rows reserved data index size unused
TABLE_NAME 23470880 67790808 KB 18116312 KB 3211616 KB 46462880 KB

I ran a dbcc indexdefrag on all indexes on the table last night,
including the clustered index, and I'm still seeing pretty much the
same amount of space reported as unused.

The one thing that I haven't done yet is to run the sp_spaceused on
this table with the @updateusage flag set to true as I need to do this
afterhours to reduce contention. I will attempt this tonight and
report on the results.

Here's DBCC SHOWCONTIG OUTPUT for the table:
DBCC SHOWCONTIG scanning 'TABLE_NAME' table...
Table: 'TABLE_NAME' (917578307); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........ ............... .........: 2264447
- Extents Scanned........ ............... .......: 285484
- Extent Switches....... ............... ........: 287092
- Avg. Pages per Extent......... ............... : 7.9
- Scan Density [Best Count:Actual Count].......: 98.59%
[283056:287093]
- Logical Scan Fragmentation ............... ...: 0.04%
- Extent Scan Fragmentation ............... ....: 3.19%
- Avg. Bytes Free per Page........... ..........: 2418.9
- Avg. Page Density (full)......... ............: 70.12%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Looking through the history of the group, the one thing that seems to
remedy this problem consistantly is to bcp out all the data, trunc the
table and bcp all the data back in. This is not really a possibility
for me due to the size of the table and the availability expectations
of my customers. Is there anything that I forgot to check? Do you
think that it's just a matter of incorrect statistics in sp_spaceused?

Thanks in advance:
Matt

[Gert-Jan Strik]

Try running sp_spaceused with the true parameter. For example:
sp_spaceused TABLE_NAME, true

Hope this helps,
Gert-Jan
spilich wrote:

I have a large table that I recently purged a year of data from.
However, the table size in sp_spaceused hasn't decreased as much as I
would expect. (there are no text or large object columns on this
table, BTW) Running sp_spaceused on the table shows the following.

rows reserved data index size unused
TABLE_NAME 23470880 67790808 KB 18116312 KB 3211616 KB 46462880 KB

I ran a dbcc indexdefrag on all indexes on the table last night,
including the clustered index, and I'm still seeing pretty much the
same amount of space reported as unused.

The one thing that I haven't done yet is to run the sp_spaceused on
this table with the @updateusage flag set to true as I need to do this
afterhours to reduce contention. I will attempt this tonight and
report on the results.

Here's DBCC SHOWCONTIG OUTPUT for the table:
DBCC SHOWCONTIG scanning 'TABLE_NAME' table...
Table: 'TABLE_NAME' (917578307); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........ ............... .........: 2264447
- Extents Scanned........ ............... .......: 285484
- Extent Switches....... ............... ........: 287092
- Avg. Pages per Extent......... ............... : 7.9
- Scan Density [Best Count:Actual Count].......: 98.59%
[283056:287093]
- Logical Scan Fragmentation ............... ...: 0.04%
- Extent Scan Fragmentation ............... ....: 3.19%
- Avg. Bytes Free per Page........... ..........: 2418.9
- Avg. Page Density (full)......... ............: 70.12%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Looking through the history of the group, the one thing that seems to
remedy this problem consistantly is to bcp out all the data, trunc the
table and bcp all the data back in. This is not really a possibility
for me due to the size of the table and the availability expectations
of my customers. Is there anything that I forgot to check? Do you
think that it's just a matter of incorrect statistics in sp_spaceused?

Thanks in advance:
Matt

--
(Please reply only to the newsgroup)

[louis nguyen]

> I have a large table that I recently purged a year of data from.

However, the table size in sp_spaceused hasn't decreased as much as I
would expect. (there are no text or large object columns on this
table, BTW) Running sp_spaceused on the table shows the following.

Hi Matt,

I had a similar situation. I inherited a database with a lot of
unused space. It turns out the tables had non-clustered indexes but
no clustered index. When I changed one of the NC index to a clustered
index, all of the unused space went away.

[louis nguyen]

Sorry, I missed the part where you mentioned you already had a
clustered index. Did you try DBCC Shrinkfile?

[spilich]

I ran sp_spaceused with the @updateusage flag set to true and that
fixed the output from sp_spaceused to be what I expected. Thanks for
the suggestions. Does anyone run this on a scheduled basis to keep
stats up to date? If so, how often?

Thanks