Efficiently Querying SQL Server Database Sizes and Free Space

Introduction

When managing a SQL Server database, understanding its size and available space is crucial for performance tuning, capacity planning, and maintenance. SQL Server provides several methods to retrieve this information, each with varying levels of detail and efficiency. In this tutorial, we will explore how to efficiently query the size of a specific database, including both data files (MDF) and log files (LDF), as well as how to determine unallocated space.

Understanding Database Size Components

A SQL Server database consists primarily of two types of files:

1. Data Files (MDF): These store user data and objects.
2. Log Files (LDF): These store transaction logs, which are essential for recovery processes.

Each file type contributes to the overall size of the database. Additionally, databases have unallocated space that can be reclaimed or expanded as needed.

Querying Database Size Using sys.master_files

The sys.master_files system view provides a comprehensive overview of all files in SQL Server databases. You can use this view to calculate the total size of a database by summing up the sizes of its data and log files.

Basic Query Structure

To retrieve the size of a specific database, you can use the following query:

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    SUM(CASE WHEN type_desc = 'ROWS' THEN (size * 8) / 1024 ELSE 0 END) AS DataSizeMB,
    SUM(CASE WHEN type_desc = 'LOG' THEN (size * 8) / 1024 ELSE 0 END) AS LogSizeMB,
    SUM((size * 8) / 1024) AS TotalSizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MY_DB'
GROUP BY database_id;

Explanation

• DB_NAME(database_id): Retrieves the name of the database.
• type_desc: Differentiates between data files (ROWS) and log files (LOG).
• size * 8 / 1024: Converts the size from pages (where each page is 8 KB) to megabytes.

Using a Function for Reusability

For frequent queries, encapsulating this logic in a user-defined function can be beneficial:

ALTER FUNCTION [dbo].[GetDBSize] 
(
    @db_name NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN (
  SELECT 
        database_name = DB_NAME(database_id)
      , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
      , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
      , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
  FROM sys.master_files WITH(NOWAIT)
  WHERE database_id = DB_ID(@db_name)
      OR @db_name IS NULL
  GROUP BY database_id
);

Using the Function

SELECT * FROM [dbo].[GetDBSize]('MY_DB');

Determining Unallocated Space

To find unallocated space, you can use the sp_spaceused stored procedure. However, for more detailed insights, including free space within each file type, consider using dynamic management views.

Using sp_spaceused

USE MY_DB;
EXEC sp_spaceused;

This returns the total database size and available unallocated space.

Comprehensive Query with sys.master_files

For a more granular view, you can combine data from sys.master_files and sys.database_files:

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL DROP TABLE #space;

CREATE TABLE #space (
    database_id INT PRIMARY KEY,
    data_used_size DECIMAL(18,2),
    log_used_size DECIMAL(18,2)
);

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

EXEC sys.sp_executesql @SQL;

SELECT 
    d.database_id,
    d.name AS DatabaseName,
    t.total_size,
    s.data_used_size,
    t.log_size,
    s.log_used_size
FROM (
    SELECT
        database_id,
        log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2)),
        data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2)),
        total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id;

Explanation

• tempdb Table (#space): Temporarily stores used space data.
• sys.database_files: Provides detailed file-level information, including actual space used (FILEPROPERTY).
• Dynamic SQL Execution: Iterates over each database to collect and insert space usage data.

Conclusion

Understanding the size and space utilization of your SQL Server databases is essential for effective database management. By leveraging system views like sys.master_files and sys.database_files, along with dynamic SQL, you can gain detailed insights into both allocated and unallocated space. This knowledge aids in capacity planning, performance tuning, and maintaining optimal database health.