SQL Server: Procedimento Armazenado para Monitoramento de Tamanhos de Arquivos de Dados 📊

Introdução

Este é um procedimento armazenado (stored procedure) projetado para monitorar e registrar informações sobre os tamanhos de arquivos de dados de bancos de dados no SQL Server. O procedimento insere os dados coletados na tabela DW.dbo.Datafile_Size, permitindo que administradores acompanhem o consumo de espaço em disco e o crescimento automático dos arquivos de dados.

🔍 Visão Geral do Procedimento Armazenado

📊 Objetivo:

Monitorar os arquivos de dados e armazenar informações detalhadas sobre eles.

📖 Tabela Utilizada: DW.dbo.Datafile_Size

A tabela DW.dbo.Datafile_Size armazena os seguintes dados:

• database_id: Identificador do banco de dados

• database_name: Nome do banco de dados

• Date: Data e hora da coleta

• state_desc: Estado do arquivo

• type_desc: Tipo do arquivo

• file_id: Identificador do arquivo

• name: Nome do arquivo

• physical_name: Caminho físico do arquivo

• disk_total_size_GB: Tamanho total do disco (GB)

• disk_free_size_GB: Espaço livre no disco (GB)

• size_GB: Tamanho atual do arquivo (GB)

• max_size_GB: Tamanho máximo configurado (GB)

• max_real_size_GB: Tamanho real máximo que pode ser atingido (GB)

• free_space_GB: Espaço livre no arquivo (GB)

• growth_MB: Crescimento do arquivo (MB)

• is_percent_growth: Indicador se o crescimento é percentual

• is_autogrowth_enabled: Crescimento automático habilitado

• percent_used: Percentual de espaço utilizado

• growth_times: Número de vezes que o crescimento ocorreu

🎯 Benefícios do Procedimento

• ✨ Monitoramento Contínuo: Atualiza a tabela com informações precisas sobre o uso do disco.

• ⚡ Otimização de Espaço: Ajuda os DBAs a prever quando aumentar o espaço dos arquivos.

• 📈 Análise Histórica: Permite acompanhar o crescimento dos arquivos ao longo do tempo.

🔧 Implementação do Procedimento Armazenado

USE [DW]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Autor: Cleber
-- Data de Criação: 07/10/2019
-- Descrição: Procedimento para monitoramento de tamanhos de arquivos de dados
-- =============================================

ALTER PROCEDURE [dbo].[monitorabancos]
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Data DATETIME;
    SELECT @Data = GETDATE();
    
    INSERT INTO DW.dbo.Datafile_Size
    SELECT
        B.database_id AS database_id,
        B.[name] AS [database_name],
        @Data AS [Date],
        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
    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;
    
    -- Atualiza valores calculados
    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 DW.dbo.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 DW.dbo.Datafile_Size A;
END
GO

🔧 Considerações Finais

📌 Importante: Certifique-se de que a tabela DW.dbo.Datafile_Size existe e está estruturada corretamente antes de executar este procedimento armazenado.

🛠️ Sugestão: Agende a execução periódica deste procedimento para obter uma visão contínua do consumo de espaço e planejamento de expansão dos arquivos de dados.

📑 Conclusão: Com essa solução, administradores de banco de dados podem acompanhar e otimizar o uso de espaço em disco de maneira mais eficiente.