Medir el uso de espacio y log de cada base

Una base se acerca al límite del volumen o el archivo de log crece con frecuencia. Necesitamos separar tamaño asignado, espacio utilizado y espacio todavía disponible dentro de los archivos actuales.

Qué necesitamos distinguir

Tamaño asignado es el espacio que ya pertenece al archivo; usado es la parte ocupada y disponible es la capacidad interna todavía libre. El log registra cambios para confirmar y recuperar transacciones.

El espacio libre dentro de un archivo no es lo mismo que el espacio libre del disco. La consulta mide la base actual: archivos de datos mediante páginas asignadas y el log como un conjunto, aunque existan varios archivos de log.

Consulta

/*
    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

Parámetros o permisos

Ejecuta la consulta dentro de cada base que quieras medir. FILEPROPERTY consulta propiedades de archivos y solo reconoce los de la base actual. La vista de administración dinámica (DMV) del log expone su uso cambiante y requiere VIEW SERVER STATE hasta SQL Server 2019, o VIEW SERVER PERFORMANCE STATE desde SQL Server 2022. El resultado expresa MB y combina todos los archivos de datos por un lado y todos los archivos de log por otro. No consulta el espacio libre del sistema operativo ni los límites de crecimiento configurados.

Cómo leer el resultado

DatosDisponiblesDentroArchivoMb y LogDisponibleDentroArchivoMb indican cuánto puede ocuparse antes de que los archivos necesiten crecer. Un porcentaje alto del log merece relacionarse con el motivo que impide reutilizarlo, no reducir el archivo de inmediato. LogDesdeUltimoRespaldoMb tiene sentido dentro de la estrategia de recuperación y respaldos de log. Una cifra aislada no demuestra que falte un respaldo ni que el archivo esté dimensionado incorrectamente.

Qué conclusión sí permite obtener

Permite medir cuánto espacio está asignado y utilizado dentro de la base actual, y distinguir presión del archivo de datos de presión del log.

Qué conclusión todavía no permite obtener

No muestra espacio libre del disco, crecimiento histórico, autogrowth, límites máximos ni causa de retención del log. Tampoco indica que reducir archivos sea conveniente.

Siguiente comprobación razonable

Compara varias capturas para conocer la tendencia y revisa la configuración de archivos y del volumen. Si el log utilizado permanece alto, identifica por qué no puede reutilizarse.

Buscar en SQL Server