SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases

Olá pessoal,
Como vocês estão ?

Neste post, vou demonstrar como identificar e monitorar o espaço em disco total, livre e utilizado dos databases da sua instância. Esse post é um complemento do meu post SQL Server – Como identificar e monitorar os discos, espaço em disco total, livre e utilizado, onde demonstrei como identificar, analisar e monitorar o espaço em disco das unidades de discos.

Como identificar o espaço total, livre e utilizado dos databases

Para essa necessidade, vou utilizar as views:

• sys.master_files: Informações dos datafiles de seus databases
• sys.databases: Informações dos databases
• sys.dm_os_volume_stats: DMV que mostra as informações dos discos físicos de uma datafile

Para criar o script abaixo, eu decidi fazer algumas validações sobre as informações da view, uma vez que algumas situações acabando gerando informações imprecisas e incorretas no seu monitoramento. Essas validações são:

• Se o tamanho máximo do datafile for maior que o tamanho máximo do disco, o tamanho máximo real do datafile será o tamanho máximo do disco e não do datafile
• Se o tamanho máximo do datafile estiver configurado como “Unlimited”, o tamanho máximo real do datafile será o tamanho máximo do disco e o espaço livre do datafile será o espaço livre do disco em que está o datafile
• Se o crescimento do datafile estiver configurado como percentual, algumas colunas não serão calculadas, ficando com o valor NULL (growth_times)

Visualizar código-fonte

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 do script:

Colunas retornadas:

Como monitorar o espaço total, livre e utilizado dos databases

Pessoal, agora que demonstrei como verificar o espaço em disco dos seus datafiles (existem várias outras na internet), vou mostrar como monitorar o espaço em disco dos datafiles de suas instâncias e enviar alertas por e-mail.

Para auxiliar na geração dos códigos HTML, vou utilizar a stpExporta_Tabela_HTML_Output, mas também poderia utilizar a fncExporta_Query_HTML, disponíveis em:

• SQL Server – Como enviar o resultado de uma query por e-mail no formato HTML utilizando o CLR (C#)
• Como exportar dados de uma tabela do SQL Server para HTML

Para realizar o envio do e-mail, vou utilizar a Stored Procedure msdb.dbo.sp_send_dbmail, do DatabaseMail do SQL Server. Caso você ainda não tenha configurado ou não sabia como fazê-lo, saiba mais acessando o post SQL Server – Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail).

Código-fonte do monitoramento

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

Exemplo de uso:

EXEC dbo.stpMonitoramento_Tamanho_Datafiles
    @Vl_Limite = 40 -- float

Resultado:

Espero que esse post tenha sido útil para vocês.
Abraço!