Post
Ask Question

Check Disk Space Utilization by Tables in SQL Server Database

In this article, I will demonstrate how to check disk space utilization in SQL Server, focusing on how to determine the disk space used by individual tables. Monitoring disk space usage is crucial for database administrators to ensure the database runs efficiently and avoid storage-related issues. This process will help you understand how much disk space each table occupies, allowing for better planning, optimization, and management of storage resources.

How to Check Disk Space Utilization by Tables?

There are two methods to check disk space utilization by tables in a SQL Server database.

Built-in Feature under Reports (SQL Server Management Studio)

Let's understand, step by step, the built-in feature under reports (There are many reports we can view under the report menu), but here we are going to explore "Disk Usage By Table". Here I have downloaded the "NorthWind" database to demonstrate disk usage by table as an example.

Disk Usage By Table

The report will display the total space allocated for each table in the list. In the example below, the "Orders" table occupies the most space, followed by the "OrderDetails" table, the "Employees" table, and so on.

OrderDetails

Using SQL Stored Procedure/Query

If you want to calculate disk space utilization by tables in an SQL Server database using a stored procedure and customize your report, you can do so with a stored procedure. I’ve created one to calculate disk space utilization by tables. Please see the stored procedure below.

CREATE PROCEDURE USP_GetDiskSpaceUsedByTables
AS
BEGIN
    SELECT 
        t.name AS TableName,
        s.name AS SchemaName,
        p.rows,
        SUM(a.total_pages) * 8 AS TotalReservedSpaceKB, 
        SUM(a.used_pages) * 8 AS UsedSpaceKB, 
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalReservedSpaceMB,
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    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 NOT LIKE 'dt%' 
        AND t.is_ms_shipped = 0
        AND i.object_id > 255 
    GROUP BY 
        t.name, s.name, p.rows
    ORDER BY 
        TotalReservedSpaceMB DESC, t.name
END

Let's execute this stored procedure and review the results, which are calculated in the same way as the built-in SQL report functionality.

SQL report

Why Check Disk Space Utilization by Tables in a SQL Server Database?

Here are the key reasons to check disk space utilization by tables in an SQL Server database.

  1. Performance optimization
  2. Data management
  3. Cost management
  4. Compliance and auditing
  5. Resource allocation
  6. Data Archiving
  7. Post index maintenance

Summary

In this article, I have explained effective methods for monitoring and managing disk space utilization in your SQL Server database. Specifically, I demonstrate two approaches to check disk space utilization at the table level. By using these strategies, you can gain valuable insights into your database's storage usage, which will help you plan your activities more efficiently. This proactive management can lead to improved performance and better resource allocation in your database environment.

Happy reading!

Here are a few additional articles you might find interesting.

SQL

Other Articles


Similar Articles