Unallocated Space anomaly

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Philip Yale
    #1

    Unallocated Space anomaly

    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='T RUE' 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?
    Tags: None
    • Simon Hayes
      #2
      Re: Unallocated Space anomaly


      "Philip Yale" <philipyale@bto penworld.com> wrote in message
      news:e9c86dcc.0 404210025.3dcd2 b06@posting.goo gle.com...[color=blue]
      > 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='T RUE' 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?[/color]

      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


        Comment

        Previous template Next
        Working...