MS SQL Server - sp_spaceused reports negative unallocated space

Author

sp_spaceused reports negative unallocated space

Mark Stricker

2006-10-24, 6:37 pm

sp_spaceused is reporting negative unallocated space for about 20 of the 130
databases I am tracking. In other words, the database is using more space
than is allocated. That, of course, must be incorrect. Here's an example:

database_name database_size unallocated space
---------------------------------------------------------------------------
test 2.38 MB -64.27 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
67216 KB 25968 KB 8312 KB 32936 KB

How can a database of 2MB in size have data of 25MB?

BOL mentions that you might run dbcc updateusage to fix the problem, or use
the @updateusage parameter in sp_spaceused. But that doesn't solve my
problem, as I want to track how much space the db are taking over time, and,
I don't want it to scan all 130 databases every time. Beside, BOL says that
the incorrect data should only show up rarely, such as after an index drop.

In addition, when I view the data in taskpad view in EM, it correctly
reports the file sizes (much smaller than the negative unallocated space).

So I've got a few questions:
1) What, exactly, is unallocated space? space in the files, or extents, or
what?
2) Under what conditions does the data get updated in sysindexes? Under
what conditions does it incorrectly report data?
3) This whole issue makes me suspicious of any data returned by
sp_spaceused. Under what conditions is it reliable?

--
Mark Stricker

Kalen Delaney

2006-10-24, 6:37 pm

Hi Mark

sp_spaceused uses values it has previously collected to generate its report;
it does not go out and count pages, normally. There are some operations SQL
Server performs that do not accurately update the internal counters that
sp_spaceused uses. There is a parameter to sp_spaceused that tells it to
first go and update all the values. It means the procedure might take a
while to run on a big database.

EXEC sp_spaceused @updateusage = 'true'

Alternatively, you can run DBCC UPDATEUSAGE before you run sp_spaceused. See
BOL for details.

--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com

"Mark Stricker" < MarkStricker@discuss
ions.microsoft.com> wrote in message
news:2E76B941-427E-4E6D-AFD1- CB112B79FFEE@microso
ft.com...
> sp_spaceused is reporting negative unallocated space for about 20 of the
> 130
> databases I am tracking. In other words, the database is using more space
> than is allocated. That, of course, must be incorrect. Here's an
> example:
>
> database_name database_size unallocated space
> ---------------------------------------------------------------------------
> test 2.38 MB -64.27 MB
>
> reserved data index_size unused
> ------------------ ------------------ ------------------ ------------------
> 67216 KB 25968 KB 8312 KB 32936 KB
>
> How can a database of 2MB in size have data of 25MB?
>
>
> BOL mentions that you might run dbcc updateusage to fix the problem, or
> use
> the @updateusage parameter in sp_spaceused. But that doesn't solve my
> problem, as I want to track how much space the db are taking over time,
> and,
> I don't want it to scan all 130 databases every time. Beside, BOL says
> that
> the incorrect data should only show up rarely, such as after an index
> drop.
>
> In addition, when I view the data in taskpad view in EM, it correctly
> reports the file sizes (much smaller than the negative unallocated space).
>
> So I've got a few questions:
> 1) What, exactly, is unallocated space? space in the files, or extents, or
> what?
> 2) Under what conditions does the data get updated in sysindexes? Under
> what conditions does it incorrectly report data?
> 3) This whole issue makes me suspicious of any data returned by
> sp_spaceused. Under what conditions is it reliable?
>
> --
> Mark Stricker

Mark Stricker

2006-10-24, 6:37 pm

Thanks, Kalen. But here's my problem. I want to track how much space my
data is taking so I can do capacity planning. However, I've got 134
databases to look at. It's impractical to run 'updateusage' against all of
these databases, since most of them are production.

--
Mark Stricker

"Kalen Delaney" wrote:

> Hi Mark
>
> sp_spaceused uses values it has previously collected to generate its report;
> it does not go out and count pages, normally. There are some operations SQL
> Server performs that do not accurately update the internal counters that
> sp_spaceused uses. There is a parameter to sp_spaceused that tells it to
> first go and update all the values. It means the procedure might take a
> while to run on a big database.
>
> EXEC sp_spaceused @updateusage = 'true'
>
> Alternatively, you can run DBCC UPDATEUSAGE before you run sp_spaceused. See
> BOL for details.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
>
> "Mark Stricker" < MarkStricker@discuss
ions.microsoft.com> wrote in message
> news:2E76B941-427E-4E6D-AFD1- CB112B79FFEE@microso
ft.com...
>
>
>

Kalen Delaney

2006-10-24, 6:37 pm

Yes, that seems like a problem, so then you have to make a choice. SQL
Server does not guarantee that it maintains accurate usage info at all
times. So you either have to be satisfied with the potentially invalid
results, or force SQL Server to update the usage info before it returns the
report. You could also consider creating a job to run DBCC UPDATEUSAGE at
off hours.

--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com

"Mark Stricker" < MarkStricker@discuss
ions.microsoft.com> wrote in message
news:0040ED76-12E4-474A-9239- 571BC15AD1A4@microso
ft.com...[color=darkred]
> Thanks, Kalen. But here's my problem. I want to track how much space my
> data is taking so I can do capacity planning. However, I've got 134
> databases to look at. It's impractical to run 'updateusage' against all
> of
> these databases, since most of them are production.
>
>
> --
> Mark Stricker
>
>
> "Kalen Delaney" wrote:
>

Mark Stricker

2006-10-24, 6:37 pm

I was kind of hoping there would be a third alternative. Oh, well. :)

By the way, I finally got around to reading your book on SQL 2000. I had a
lot of fun working through the examples. Gotta love working in hexadecimal!
--
Mark Stricker

"Kalen Delaney" wrote:

> Yes, that seems like a problem, so then you have to make a choice. SQL
> Server does not guarantee that it maintains accurate usage info at all
> times. So you either have to be satisfied with the potentially invalid
> results, or force SQL Server to update the usage info before it returns the
> report. You could also consider creating a job to run DBCC UPDATEUSAGE at
> off hours.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
>
> "Mark Stricker" < MarkStricker@discuss
ions.microsoft.com> wrote in message
> news:0040ED76-12E4-474A-9239- 571BC15AD1A4@microso
ft.com...
>
>
>

Tibor Karaszi

2006-10-24, 6:37 pm

You can get correct information at the data file level using the same undocumented command as EM
uses (run a profiler trace when viewing db in taskpad and you will see). For log, you just use DBCC
SQLPERF(logspace).

Also, 2005 will give you correct information in the successor of sysindexes.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Mark Stricker" < MarkStricker@discuss
ions.microsoft.com> wrote in message
news:B65ACC46-9762-49C7-A819- D91E66185B2C@microso
ft.com...[color=darkred]
>I was kind of hoping there would be a third alternative. Oh, well. :)
>
> By the way, I finally got around to reading your book on SQL 2000. I had a
> lot of fun working through the examples. Gotta love working in hexadecimal!
> --
> Mark Stricker
>
>
> "Kalen Delaney" wrote:
>

Mark Stricker

2006-10-24, 6:37 pm

DBCC showfilestats! Awesome. Thanks, Tibor.
--
Mark Stricker

"Tibor Karaszi" wrote:

> You can get correct information at the data file level using the same undocumented command as EM
> uses (run a profiler trace when viewing db in taskpad and you will see). For log, you just use DBCC
> SQLPERF(logspace).
>
> Also, 2005 will give you correct information in the successor of sysindexes.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Mark Stricker" < MarkStricker@discuss
ions.microsoft.com> wrote in message
> news:B65ACC46-9762-49C7-A819- D91E66185B2C@microso
ft.com...
>
>

Sponsored Links