Measure the space and log usage of each base

A database approaches the volume limit or the log file grows frequently. We need to separate allocated size, used space and still available space within the current files.

What we need to distinguish

Allocated size is space already assigned to a file; used space is the occupied portion, and available space is the internal capacity still free. The log records changes so transactions can be committed and recovered.

Free space within a file is not the same as free disk space. The query measures the current database: data files using mapped pages and the log as a set, even if multiple log files exist.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
WITH EspacioDatos AS
(
    SELECT
        SUM(CONVERT(BIGINT, df.size)) * 8.0 / 1024 AS DatosAsignadosMb,
        SUM
        (
            CONVERT(BIGINT, FILEPROPERTY(df.name, 'SpaceUsed'))
        ) * 8.0 / 1024 AS DatosUtilizadosMb
    FROM sys.database_files AS df
    WHERE df.type_desc = N'ROWS'
),
EspacioLog AS
(
    SELECT
        lsu.total_log_size_in_bytes / 1048576.0 AS LogAsignadoMb,
        lsu.used_log_space_in_bytes / 1048576.0 AS LogUtilizadoMb,
        lsu.used_log_space_in_percent AS LogUtilizadoPorcentaje,
        lsu.log_space_in_bytes_since_last_backup / 1048576.0
            AS LogDesdeUltimoRespaldoMb
    FROM sys.dm_db_log_space_usage AS lsu
)
SELECT
    DB_NAME() AS BaseDatos,
    CONVERT(DECIMAL(18, 2), d.DatosAsignadosMb) AS DatosAsignadosMb,
    CONVERT(DECIMAL(18, 2), d.DatosUtilizadosMb) AS DatosUtilizadosMb,
    CONVERT
    (
        DECIMAL(18, 2),
        d.DatosAsignadosMb - d.DatosUtilizadosMb
    ) AS DatosDisponiblesDentroArchivoMb,
    CONVERT(DECIMAL(18, 2), l.LogAsignadoMb) AS LogAsignadoMb,
    CONVERT(DECIMAL(18, 2), l.LogUtilizadoMb) AS LogUtilizadoMb,
    CONVERT
    (
        DECIMAL(18, 2),
        l.LogAsignadoMb - l.LogUtilizadoMb
    ) AS LogDisponibleDentroArchivoMb,
    CONVERT(DECIMAL(6, 2), l.LogUtilizadoPorcentaje)
        AS LogUtilizadoPorcentaje,
    CONVERT(DECIMAL(18, 2), l.LogDesdeUltimoRespaldoMb)
        AS LogDesdeUltimoRespaldoMb
FROM EspacioDatos AS d
CROSS JOIN EspacioLog AS l;
GO

Parameters or permissions

Run the query within each base you want to measure. FILEPROPERTY queries file properties and only recognizes files in the current database. The dynamic management view (DMV) of the log exposes its changing usage and requires VIEW SERVER STATE up to SQL Server 2019, or VIEW SERVER PERFORMANCE STATE from SQL Server 2022. The result expresses MB and combines all data files on one side and all log files on the other. It does not consult the free space of the operating system or the configured growth limits.

How to read the result

DataAvailableInsideFileMb and LogAvailableInFileMb indicate how much can be used before the files need to grow. A high percentage of the log deserves to be related to the reason that prevents it from being reused, not to reduce the file immediately. LogFromLastBackupMb makes sense within the log backup and recovery strategy. An isolated figure does not prove that a backup is missing or that the file is incorrectly sized.

What this does establish

Allows you to measure how much space is allocated and used within the current database, and distinguish pressure from the log pressure data file.

What this still does not establish

It does not show free disk space, historical growth, autogrowth, maximum limits or log retention cause. It also does not indicate that reducing files is convenient.

Next reasonable check

Compare multiple screenshots to see the trend and review your file and volume settings. If the log used remains high, identifies why it cannot be reused.

Search SQL Server