Getting Database Table Size?

[ihindegit]

Is it possible to create a metric to get the size of a database table? I want to monitor the size over time. I would like to use the query:

 EXEC sp_spaceused 'MyTable'

Is this possible with this exporter?

[burningalchemist]

hey @ihindegit, it's hard to suggest something since there is no context.

Assuming you're talking about MSSQL, you might want to also have a look here: #514

The stored procedure might work as well, I haven't tested it. 👍

[ihindegit]

Yes, it's for SQL Server. The example you gave is for the full database. I just need the size of an individual table.

[burningalchemist]

You can try the following query if the stored procedure doesn't work for some reason:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME = 'MyTable'
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceKB DESC

[ihindegit]

Is it possibly something wrong with the definition? Below is my full metric.

• metric_name: mssql_size
  type: gauge
  help: 'Table size'
  value_label: 'state'
  values: [TotalSpaceKB]
  query: SELECT SUM(a.total_pages) * 8 AS TotalSpaceKB
  FROM sys.tables t
  INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
  INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
  WHERE
  t.NAME = 'MyTable'
  GROUP BY
  t.Name, s.Name, p.Rows
  ORDER BY
  TotalSpaceKB DESC

[ihindegit]

Got it working :)