Monitoring Database Space Usage

–Space stats for each logical file in a db in a readable format

SELECT
[name]
,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2)) AS [Size]
,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],‘SpaceUsed’)/128.,2)) AS [Used]
,CONVERT(NUMERIC(10,2),ROUND(([size]FILEPROPERTY([name],‘SpaceUsed’))/128.,2)) AS [Unused]
,UPPER(SUBSTRING(physical_name, 1, 3)) AS [Drive]
,UPPER([physical_name]) AS [Physical Location]
FROM [sys].[database_files]

–objects in different filegroups

select tablename = object_name(object_id),Data_located_on_filegroup = d.name
from sys.data_spaces d
join sys.indexes i on d.data_space_id = i.data_space_id
where i.index_id < 2

 
–Full Space report for each table in a database – similar to sp_spaceused but more detail

SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
        From sys.database_principals pr
        Where pr.principal_id = tbl.principal_id)
    , SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
    Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
, Coalesce( (Select Cast(v.low/1024.0 as float) 
    * SUM(a.used_pages CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) 
        FROM sys.indexes as i
         JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
         JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        Where i.object_id = tbl.object_id  )
    , 0.0) AS [IndexKB]
, Coalesce( (Select Cast(v.low/1024.0 as float)
    * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) 
        FROM sys.indexes as i
         JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
         JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        Where i.object_id = tbl.object_id)
    , 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date
 FROM sys.tables AS tbl
  INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
  INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type=‘E’)

–sp_spaceused

CREATE TABLE #sp_spaceused_all
(name nvarchar(255),
rows nvarchar(255),
reserved nvarchar(255),
data nvarchar(255),
index_size nvarchar(255),
unused nvarchar(255))
EXEC sp_msforeachtable ‘insert into #sp_spaceused_all exec sp_spaceused ”?”’
SELECT * FROM #sp_spaceused_all ORDER BY name
DROP TABLE #sp_spaceused_all

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: