View the size of databases, tables and indexes

- 4 minutes read - 818 words

Tips & tricks …

… and sometimes “things I always have to look up how to do”

Every so often I need to know how big a relational database is, and/or what are the largest objects within a database.

More specifically, for all databases on a server, things like:

  • total size
  • space used by data
  • space used by logs
  • free space

And for tables and indexes within a single database:

  • number of rows
  • total size
  • space used
  • free space
  • size of the different indexes

I most often use SQL Server and Postgres, so I have scripts to do this for both. There will be different ways to do this for each but the scripts below have been refined over a number of years and work well for me.

Some aspects could certainly be extracted out rather than duplicated, but I want them to be standalone and not have side effects.

SQL Server

SQL Server: All databases on a server

WITH DatabaseSizes AS (
    SELECT      
		db.name AS [Name],
		CAST(SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size END)*8/1024.0 AS DECIMAL(8,2)) AS [RowUsedSpaceMB],
		CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size END)*8/1024.0 AS DECIMAL(8,2)) AS [LogUsedSpaceMB],		
		CAST(SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size/128.0 - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS INT)/128.0 END) AS DECIMAL(8,2)) AS [RowFreeSpaceMB],
		CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size/128.0 - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS INT)/128.0 END) AS DECIMAL(8,2)) AS [LogFreeSpaceMB],
		CAST(SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size END)*8/1024.0/1024.0 AS DECIMAL(8,2)) AS [RowUsedSpaceGB],
		CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size END)*8/1024.0/1024.0 AS DECIMAL(8,2)) AS [LogUsedSpaceGB],
		CAST(SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size/128.0 - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS INT)/128.0 END)/1024.0 AS DECIMAL(8,2)) AS [RowFreeSpaceGB],
		CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size/128.0 - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS INT)/128.0 END)/1024.0 AS DECIMAL(8,2)) AS [LogFreeSpaceGB]
	FROM sys.databases AS db
		INNER JOIN sys.master_files AS mf ON db.database_id = mf.database_id  
	GROUP BY db.name 
)
SELECT 
	[Name]
	,[RowUsedSpaceMB] + [LogUsedSpaceMB] + [RowFreeSpaceMB] + [LogFreeSpaceMB] AS [TotalSizeMB]
	,[RowUsedSpaceMB] + [LogUsedSpaceMB] AS [TotalUsedSpaceMB]
	,[RowFreeSpaceMB] + [LogFreeSpaceMB] AS [TotalFreeSpaceMB]
	,[RowUsedSpaceMB]
	,[RowFreeSpaceMB]
	,[LogUsedSpaceMB]
	,[LogFreeSpaceMB]	
	--,[RowUsedSpaceGB] + [LogUsedSpaceGB] + [RowFreeSpaceGB] + [LogFreeSpaceGB] AS [TotalSizeGB]
	--,[RowUsedSpaceGB] + [LogUsedSpaceGB] AS [TotalUsedSpaceGB]
	--,[RowFreeSpaceGB] + [LogFreeSpaceGB] AS [TotalFreeSpaceGB]
	--,[RowUsedSpaceGB]
	--,[RowFreeSpaceGB]
	--,[LogUsedSpaceGB]
	--,[LogFreeSpaceGB]
FROM DatabaseSizes 
ORDER BY [TotalSizeMB] DESC

Example results:

sql server all databases results

The above script is a little more complex than you might imagine but this complexity allows reporting the numbers you are interested in. For example, in MB or GB, row data or log data, etc. Comment in or out the lines that matter to you.

SQL Server: All tables within a database

SELECT 
    t.NAME AS [TableName],
    s.Name AS [SchemaName],
    p.rows AS [RowCount],
    CAST(((SUM(a.total_pages)*8)/1024.0) AS DECIMAL(8, 2)) AS [TotalSpaceMB],
    CAST(((SUM(a.used_pages)*8)/1024.0) AS DECIMAL(8, 2)) AS [UsedSpaceMB], 
    CAST(((SUM(a.total_pages) - SUM(a.used_pages))*8)/1024.0 AS DECIMAL(8, 2)) AS [FreeSpaceMB],
    CAST(((SUM(a.total_pages)*8)/1024.0/1024.0) AS DECIMAL(8, 2)) AS [TotalSpaceGB],
    CAST(((SUM(a.used_pages)*8)/1024.0/1024.0) AS DECIMAL(8, 2)) AS [UsedSpaceGB], 
    CAST(((SUM(a.total_pages) - SUM(a.used_pages))*8)/1024.0/1024.0 AS DECIMAL(8, 2)) AS [FreeSpaceGB]
FROM sys.tables AS t
	INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
	INNER JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
	INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
	LEFT OUTER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    [TotalSpaceMB] DESC, t.Name

Example results:

sql server all tables results

SQL Server: All indexes within a database

SELECT
    t.name AS TableName,
    s.name AS [SchemaName],
    i.name AS IndexName,
    CAST(((SUM(sz.[used_page_count])*8)/1024.0) AS DECIMAL(8, 2)) AS [IndexSizeMB],
    CAST(((SUM(sz.[used_page_count])*8)/1024.0/1024.0) AS DECIMAL(8, 2)) AS [IndexSizeGB],
    i.type_desc AS [Type]
FROM sys.dm_db_partition_stats AS sz
         INNER JOIN sys.indexes AS i ON sz.[object_id] = i.[object_id] AND sz.[index_id] = i.[index_id]
         INNER JOIN sys.tables t ON t.object_id = i.object_id
         LEFT OUTER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
GROUP BY t.name, s.name, i.name, i.type_desc
ORDER BY [IndexSizeMB] DESC, t.name

Example results:

sql server all indexes results

Postgres

Some notes about these scripts for Postgres (vs. the SQL Server versions above):

  • the Postgres pg_size_pretty() function takes care of converting size values into appropriate units of measure, so multiple MB and GB columns are not needed
  • Postgres doesn’t have the concept of “free space” like SQL Server, it is just bound by the amount of available disk space
  • transaction logs (write-ahead logging in Postgres) are server instance wide, not per database

Postgres: All databases on a server

SELECT
    d.datname as name,
    pg_size_pretty(pg_database_size(d.datname)) as size
FROM
    pg_database d
ORDER BY
    pg_database_size(d.datname) DESC;

Example results:

Postgres all databases results

Postgres: All tables within a database

SELECT 
	name, 
	row_count,
	pg_size_pretty(t.total_size) AS total_size, 
	pg_size_pretty(t.table_size) AS table_size, 
	pg_size_pretty(t.indexes_size) AS indexes_size
FROM (
	SELECT 
		oid::regclass AS name, 
		n_live_tup AS row_count,
		pg_total_relation_size(oid) AS total_size, 
		pg_table_size(oid) AS table_size, 
		pg_indexes_size(oid) AS indexes_size
	FROM pg_class AS c
		INNER JOIN pg_stat_user_tables AS sut ON c.oid = sut.relid
	WHERE relkind = 'r'
	) AS t
ORDER BY 
	t.total_size DESC;

Example results:

Postgres all tables results

Postgres: All indexes within a database

SELECT
    table_name,
    index_name,
    pg_size_pretty(i.size) AS index_size
FROM (
         SELECT
             relname AS table_name,
             indexrelname AS index_name,
             pg_relation_size(indexrelid) AS size
         FROM pg_stat_user_indexes
     ) AS i
ORDER BY
    i.size DESC;

Example results:

Postgres all indexes results

Conclusion

So here I presented a set of utility scripts to output the size of SQL Server and Postgres databases, tables and indexes. Hopefully you can find them useful as well.