SQL Server - Cómo identificar y monitorear el espacio en disco total, libre y usado por los datafiles de las bases de datos

Hola, chicos,
¿Cómo estás?

En esta publicación, demostraré cómo identificar y monitorear el espacio en disco total, libre y utilizado de las bases de datos de su instancia. Este post es un complemento a mi post. SQL Server: cómo identificar y monitorear discos, espacio en disco total, libre y utilizado, donde demostré cómo identificar, analizar y monitorear el espacio en disco en las unidades de disco.

Cómo identificar el espacio total, libre y utilizado de las bases de datos

Para esta necesidad, usaré vistas:

• sys.master_files: información de los archivos de datos de su base de datos
• sys.databases: información de la base de datos
• sys.dm_os_volume_stats: DMV que muestra información sobre los discos físicos de un archivo de datos

Para crear el siguiente script, decidí hacer algunas validaciones sobre la información en la vista, ya que algunas situaciones terminan generando información imprecisa e incorrecta al monitorear. Estas validaciones son:

• Si el tamaño máximo del archivo de datos es mayor que el tamaño máximo del disco, el tamaño máximo real del archivo de datos será el tamaño máximo del disco y no el tamaño del archivo de datos.
• Si el tamaño máximo del archivo de datos se establece en "Ilimitado", el tamaño máximo real del archivo de datos será el tamaño máximo del disco y el espacio libre del archivo de datos será el espacio libre del disco donde se encuentra el archivo de datos.
• Si el crecimiento del archivo de datos se configura como porcentaje, algunas columnas no se calcularán, dejando el valor NULL (growth_times)

Ver código fuente

IF (OBJECT_ID('tempdb..#Datafile_Size ') IS NOT NULL) DROP TABLE #Datafile_Size
SELECT
    B.database_id AS database_id,
    B.[name] AS [database_name],
    A.state_desc,
    A.[type_desc],
    A.[file_id],
    A.[name],
    A.physical_name,
    CAST(C.total_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_total_size_GB,
    CAST(C.available_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_free_size_GB,
    CAST(A.size / 128 / 1024.0 AS NUMERIC(18, 2)) AS size_GB,
    CAST(A.max_size / 128 / 1024.0 AS NUMERIC(18, 2)) AS max_size_GB,
    CAST(
        (CASE
	    WHEN A.growth <= 0 THEN A.size / 128 / 1024.0
            WHEN A.max_size <= 0 THEN C.total_bytes / 1073741824.0
            WHEN A.max_size / 128 / 1024.0 > C.total_bytes / 1073741824.0 THEN C.total_bytes / 1073741824.0
            ELSE A.max_size / 128 / 1024.0 
        END) AS NUMERIC(18, 2)) AS max_real_size_GB,
    CAST(NULL AS NUMERIC(18, 2)) AS free_space_GB,
    (CASE WHEN A.is_percent_growth = 1 THEN A.growth ELSE CAST(A.growth / 128 AS NUMERIC(18, 2)) END) AS growth_MB,
    A.is_percent_growth,
	(CASE WHEN A.growth <= 0 THEN 0 ELSE 1 END) AS is_autogrowth_enabled,
    CAST(NULL AS NUMERIC(18, 2)) AS percent_used,
    CAST(NULL AS INT) AS growth_times
INTO
    #Datafile_Size 
FROM
    sys.master_files        A   WITH(NOLOCK)
    JOIN sys.databases      B   WITH(NOLOCK)    ON  A.database_id = B.database_id
    CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.[file_id]) C

	
UPDATE A
SET
    A.free_space_GB = (
    (CASE 
        WHEN max_size_GB <= 0 THEN A.disk_free_size_GB
        WHEN max_real_size_GB > disk_free_size_GB THEN A.disk_free_size_GB 
        ELSE max_real_size_GB - size_GB
    END)),
    A.percent_used = (size_GB / (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END)) * 100
FROM 
    #Datafile_Size A
	

UPDATE A
SET
    A.growth_times = 
    (CASE 
        WHEN A.growth_MB <= 0 THEN 0 
        WHEN A.is_percent_growth = 0 THEN (A.max_real_size_GB - A.size_GB) / (A.growth_MB / 1024.0) 
        ELSE NULL 
    END)
FROM 
    #Datafile_Size A


SELECT * 
FROM #Datafile_Size

Resultado del guión:

Columnas devueltas:

Cómo monitorear el espacio total, libre y utilizado en bases de datos

Chicos, ahora que les he demostrado cómo verificar el espacio en disco de sus archivos de datos (hay varios más en Internet), les mostraré cómo monitorear el espacio en disco de los archivos de datos de sus instancias y enviar alertas por correo electrónico.

Para ayudar a generar códigos HTML, usaré stpExporta_Table_HTML_Output, pero también podría usar fncExporta_Query_HTML, disponible en:

• SQL Server – Cómo enviar el resultado de una consulta por correo electrónico en formato HTML usando el CLR (C#)
• Cómo exportar datos de una tabla de SQL Server a HTML

Para enviar el correo electrónico, utilizaré el procedimiento almacenado msdb.dbo.sp_send_dbmail, de SQL Server DatabaseMail. Si aún no lo has configurado o no sabes cómo hacerlo, infórmate accediendo al post SQL Server – Cómo activar y configurar el correo de base de datos para enviar y monitorear correos electrónicos a través de la base de datos (sp_send_dbmail).

Monitoreo del código fuente

IF (OBJECT_ID('dbo.stpMonitoramento_Tamanho_Datafiles') IS NULL) EXEC('CREATE PROCEDURE dbo.stpMonitoramento_Tamanho_Datafiles AS SELECT 1')
GO

ALTER PROCEDURE dbo.stpMonitoramento_Tamanho_Datafiles (
    @Vl_Limite FLOAT = 80
)
AS BEGIN

    ------------------------------------------------------------------------------------------------
    -- IDENTIFICAÇÃO DO ESPAÇO UTILIZADO PELOS DATAFILES
    ------------------------------------------------------------------------------------------------

    IF (OBJECT_ID('tempdb..#Monitor_Datafile_Size') IS NOT NULL) DROP TABLE #Monitor_Datafile_Size
    SELECT
        B.database_id AS database_id,
        B.[name] AS [database_name],
        A.state_desc,
        A.[type_desc],
        A.[file_id],
        A.[name],
        A.physical_name,
        CAST(C.total_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_total_size_GB,
        CAST(C.available_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_free_size_GB,
        CAST(A.size / 128 / 1024.0 AS NUMERIC(18, 2)) AS size_GB,
        CAST(A.max_size / 128 / 1024.0 AS NUMERIC(18, 2)) AS max_size_GB,
        CAST(
            (CASE
                WHEN A.growth <= 0 THEN A.size / 128 / 1024.0
                WHEN A.max_size <= 0 THEN C.total_bytes / 1073741824.0
                WHEN A.max_size / 128 / 1024.0 > C.total_bytes / 1073741824.0 THEN C.total_bytes / 1073741824.0
                ELSE A.max_size / 128 / 1024.0 
            END) AS NUMERIC(18, 2)) AS max_real_size_GB,
        CAST(NULL AS NUMERIC(18, 2)) AS free_space_GB,
        (CASE WHEN A.is_percent_growth = 1 THEN A.growth ELSE CAST(A.growth / 128 AS NUMERIC(18, 2)) END) AS growth_MB,
        A.is_percent_growth,
        (CASE WHEN A.growth <= 0 THEN 0 ELSE 1 END) AS is_autogrowth_enabled,
        CAST(NULL AS NUMERIC(18, 2)) AS percent_used,
        CAST(NULL AS INT) AS growth_times
    INTO
        #Monitor_Datafile_Size
    FROM
        sys.master_files        A   WITH(NOLOCK)
        JOIN sys.databases      B   WITH(NOLOCK)    ON  A.database_id = B.database_id
        CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.[file_id]) C

    
    UPDATE A
    SET
        A.free_space_GB = (
        (CASE 
            WHEN max_size_GB <= 0 THEN A.disk_free_size_GB
            WHEN max_real_size_GB > disk_free_size_GB THEN A.disk_free_size_GB 
            ELSE max_real_size_GB - size_GB
        END)),
        A.percent_used = (size_GB / (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END)) * 100
    FROM 
        #Monitor_Datafile_Size A
    

    UPDATE A
    SET
        A.growth_times = 
        (CASE 
            WHEN A.growth_MB <= 0 THEN 0 
            WHEN A.is_percent_growth = 0 THEN (A.max_real_size_GB - A.size_GB) / (A.growth_MB / 1024.0) 
            ELSE NULL 
        END)
    FROM 
        #Monitor_Datafile_Size A

    
    IF (OBJECT_ID('tempdb..##Monitoramento_Datafile_Size') IS NOT NULL) DROP TABLE ##Monitoramento_Datafile_Size
    SELECT
        A.[database_name],
        A.[name],
        A.[type_desc],
        A.size_GB,
        A.max_real_size_GB ,
        A.free_space_GB,
        A.growth_MB,
        A.growth_times,
        A.percent_used
    INTO
        ##Monitoramento_Datafile_Size
    FROM
        #Monitor_Datafile_Size A
    WHERE
        percent_used >= @Vl_Limite

    
    ------------------------------------------------------------------------------------------------
    -- GERAÇÃO DOS ALERTAS
    ------------------------------------------------------------------------------------------------
    
    IF (OBJECT_ID('dbo.Historico_Tamanho_Datafile') IS NULL)
    BEGIN
        
        CREATE TABLE dbo.Historico_Tamanho_Datafile (
            Id_Evento INT IDENTITY(1, 1) NOT NULL,
            Dt_Evento DATETIME DEFAULT GETDATE() NOT NULL,
            Nm_Database VARCHAR(256) NOT NULL,
            Nm_Datafile VARCHAR(256) NOT NULL,
            Ds_Tipo VARCHAR(10) NOT NULL,
            Qt_Tamanho NUMERIC(18, 2) NOT NULL,
            Qt_Tamanho_Maximo NUMERIC(18, 2) NOT NULL,
            Qt_Espaco_Livre NUMERIC(18, 2) NOT NULL,
            Qt_Aumento_Autogrowth INT NOT NULL,
            Qt_Vezes_Autogrowth INT NULL,
            Pr_Utilizacao NUMERIC(5, 2)
        )

    END
 

    IF ((SELECT COUNT(*) FROM ##Monitoramento_Datafile_Size) > 0)
    BEGIN
        

        INSERT INTO dbo.Historico_Tamanho_Datafile (
            Nm_Database, 
            Nm_Datafile, 
            Ds_Tipo, 
            Qt_Tamanho, 
            Qt_Tamanho_Maximo, 
            Qt_Espaco_Livre, 
            Qt_Aumento_Autogrowth,
            Qt_Vezes_Autogrowth,
            Pr_Utilizacao 
        )
        SELECT 
            A.[database_name],
            A.[name],
            A.[type_desc],
            A.size_GB,
            A.max_real_size_GB ,
            A.free_space_GB,
            A.growth_MB,
            A.growth_times,
            A.percent_used
        FROM 
            ##Monitoramento_Datafile_Size A
        

        DECLARE 
            @Ds_Saida VARCHAR(MAX),
            @Assunto VARCHAR(200) = @@SERVERNAME + ' - Monitoramento de Espaço dos Datafiles',
            @Destinatario VARCHAR(MAX) = '[email protected]',
            @Mensagem VARCHAR(MAX)

            
        EXEC dbo.stpExporta_Tabela_HTML_Output
            @Ds_Tabela = '##Monitoramento_Datafile_Size', -- varchar(max)
            @Fl_Aplica_Estilo_Padrao = 1 , -- bit
            @Ds_Saida = @Ds_Saida OUTPUT -- varchar(max)
        

        SET @Mensagem = '
Prezado DBA,<br/>
Foi identificado um ou mais problemas de espaço em disco nos datafiles da instância ' + @@SERVICENAME + ' no servidor ' + @@SERVERNAME + ':<br/><br/>' + ISNULL(@Ds_Saida, '')


        -- Envia o e-mail
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'ProfileEnvioEmail' , -- sysname
            @recipients = @Destinatario , -- varchar(max)
            @subject = @Assunto, -- nvarchar(255)
            @body = @Mensagem, -- nvarchar(max)
            @body_format = 'HTML'

    END


END

Ejemplo de uso:

EXEC dbo.stpMonitoramento_Tamanho_Datafiles
    @Vl_Limite = 40 -- float

Resultado:

Espero que esta publicación te haya sido útil.
¡Abrazo!