%used: sp_spaceused vs. sys.sysindexes.used?

• skayser

  SSC Rookie

  Points: 33

  More actions

  June 6, 2011 at 5:32 am

  #389660

  We've got a SQL 2005 (express) installation with the usual 4GB data limit. I am trying to monitor this threshold and have troubles finding a definitve metric to do so.

  sp_spaceused gives me: reserved 3722 MB (3812096 KB) while a Nagios plugin that was suggested to me[/url] monitors sys.sysindexes and returns 3105 MB. The exact query is this one:

  SELECT

  SUM(CAST(used AS BIGINT)) / 128

  FROM

  [VIM_VCDB].sys.sysindexes

  WHERE

  indid IN (0,1,255)

  So from what I understand sp_spaceused's "reserved" equals the .mdb file size. What does the value from sysindexes refer to? And which is the one that you guys would monitor?

• Eddie Wuerch

  SSChampion

  Points: 12542

  More actions

  June 6, 2011 at 12:08 pm

  #1334598

  Get rid of the WHERE clause and you'll see the sys.partition_stats query return data similar to sp_spaceused.

  -Eddie

  Eddie Wuerch
  MCM: SQL

• Latheesh NK

  SSCertifiable

  Points: 6559

  More actions

  June 6, 2011 at 10:37 pm

  #1334782

  Hi,Am not able to co-relate with the data between the two. Could you please explain little bit.