SQL Server Operations

How to monitor file size and growth in SQL Server

A full disk stops the database cold. Here you query the current size and the used and free space of each data and log file.

Size and free space per file

sys.database_files shows each file (.mdf, .ndf, .ldf), its size, and the space used internally. Size is in 8 KB pages.

SELECT name AS archivo, type_desc,
       size * 8 / 1024 AS tamano_mb,
       FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS usado_mb,
       (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS libre_mb
FROM sys.database_files;

Server-level usage (all databases)

Loop through all databases to get the full picture of space used on the instance.

EXEC sp_MSforeachdb '
USE [?];
SELECT DB_NAME() AS base, name,
       size * 8 / 1024 AS tamano_mb
FROM sys.database_files;';

Review the autogrowth configuration

Autogrowth by percentage or in small increments causes fragmentation and pauses. Prefer a reasonable fixed size (e.g., 512 MB or 1 GB).

SELECT name, type_desc,
       CASE is_percent_growth WHEN 1 THEN CAST(growth AS varchar) + ' %'
            ELSE CAST(growth * 8 / 1024 AS varchar) + ' MB' END AS crecimiento,
       max_size
FROM sys.database_files;

Watch tempdb separately

tempdb is shared by all workloads and can grow out of control. Monitor its usage with the dedicated DMV.

SELECT SUM(unallocated_extent_page_count) * 8 / 1024 AS libre_mb,
       SUM(user_object_reserved_page_count) * 8 / 1024 AS obj_usuario_mb
FROM tempdb.sys.dm_db_file_space_usage;

// common mistake

Shrinking files with DBCC SHRINKFILE fragments indexes and is usually counterproductive: do not use it routinely. A log that keeps growing almost always means missing log backups in the FULL recovery model. Percentage-based autogrowth is a trap: on large files each growth is huge.

// when it's worth an expert

If space gets out of hand or the log grows for no obvious reason, there is usually an underlying configuration problem. At dba.mx we set up space management, autogrowth, and monitoring with alerts, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.