Unallocated Space anomaly

[Philip Yale]

At my current workplace, whenever I check table sizes using the
'reserved' column from sysindexes, or sp_spaceused, I get a total for
all user tables which exceeds the physical size of the database.

Running sp_spaceused with no parameteres, I get a NEGATIVE value for
unallocated space in the database, but only see this if I return the
results in GRID format in QA (text format gives less output):

DatabaseName DatabaseSize Unallocated Space
xxxxx_xxxxx 11502.38 MB -4874.80 MB

Reserved Data index_size unused
16602800 KB 7013752 KB 2381904 KB 7207144 KB
If I re-run sp_spaceused with the @updateusage='TRUE' option, this
output gets corrected to:
DatabaseName DatabaseSize Unallocated Space
xxxxx_xxxxx 11502.38 MB 2773.76 MB

Reserved Data index_size unused
8770680 KB 6928168 KB 1808096 KB 34416 KB

which shows a substantial difference in the Reserved/Data/Index/Unused
sizes.

This happens every day - any ideas about:

1) Why this might be happening on such a large scale, and
2) Is it conceivable that these discrepancies in space allocation
information could be causing performance problems? I can imagine that
if the database is trying to locate free pages on extents yet its
internal view of these doesn't match reality then this could impede
data insertion.

Offers anybody?

[Simon Hayes]

"Philip Yale" <ph********@btopenworld.com> wrote in message
news:e9**************************@posting.google.c om...

At my current workplace, whenever I check table sizes using the
'reserved' column from sysindexes, or sp_spaceused, I get a total for
all user tables which exceeds the physical size of the database.

Running sp_spaceused with no parameteres, I get a NEGATIVE value for
unallocated space in the database, but only see this if I return the
results in GRID format in QA (text format gives less output):

DatabaseName DatabaseSize Unallocated Space
xxxxx_xxxxx 11502.38 MB -4874.80 MB

Reserved Data index_size unused
16602800 KB 7013752 KB 2381904 KB 7207144 KB
If I re-run sp_spaceused with the @updateusage='TRUE' option, this
output gets corrected to:
DatabaseName DatabaseSize Unallocated Space
xxxxx_xxxxx 11502.38 MB 2773.76 MB

Reserved Data index_size unused
8770680 KB 6928168 KB 1808096 KB 34416 KB

which shows a substantial difference in the Reserved/Data/Index/Unused
sizes.

This happens every day - any ideas about:

1) Why this might be happening on such a large scale, and
2) Is it conceivable that these discrepancies in space allocation
information could be causing performance problems? I can imagine that
if the database is trying to locate free pages on extents yet its
internal view of these doesn't match reality then this could impede
data insertion.

Offers anybody?

1. If the database is busy, then the data in sysindexes can get out of date.
I guess that perhaps maintaining correct space data is 'low priority'
compared to all the other things the database engine has to do.

2. I doubt it - the information returned by sp_spaceused and corrected by
DBCC UPDATEUSAGE is only about the amount of space used on disk, and not
about the GAM/SGAM data which is held internally. See "Managing Extent
Allocations and Free Space" in Books Online.

Simon

[slime]

Use the @updateusage parameter of sp_spaceused and see if that helps. See
Books Online for more info.
--