SQL Disk Space Usage

SQL Disk Space Usage

How big?

It isn’t very often that you need to understand which tables take up most space within your database. Normally, knowing how much space the entire database consumes is enough.

However, SQL Server is expensive if you use a cloud provider. Even the smallest database is expensive.

Knowing which tables take up the most space can be crucial in those instances. For example, suppose you store infrequently accessed data in the database, such as historical information. In that case, you might decide that this can be moved from the database to something more appropriate, such as Table Storage.

Determining where those bytes are.

As with much in SQL Server, this information is freely available if you know where to look. The SQL code below shows where your bytes are: –

 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages,
 SUM(a.used_pages) AS UsedPages,
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
 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
 i.OBJECT_ID > 255 AND 
 i.index_id <= 1
 t.NAME, i.object_id, i.index_id, i.name
TotalSpaceMB desc

Hi, my name is Stephen Finchett. I have been a software engineer for over 30 years and worked on complex, business critical, multi-user systems for all of my career. For the last 15 years, I have been concentrating on web based solutions using the Microsoft Stack including ASP.Net, C#, TypeScript, SQL Server and running everything at scale within Kubernetes.