SQL Server - Como identificar e monitorar os discos, espaço em disco total, livre e utilizado

Olá pessoal,
Tudo bem com vocês ?

Neste post vou demonstrar um recurso bem interessante e comum no dia a dia de um DBA, que é o monitoramento de espaço em disco no servidor. Como DBA, você deve sempre ter controle sobre o espaço em disco do servidor, de forma que você não deixe que esse espaço atinja níveis críticos e você tenha que tentar resolver de última hora, com o banco de dados correndo risco de parar por falta de espaço em disco.

Esse post é um complemento do meu post SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases, onde demonstrei como identificar, analisar e monitorar o espaço em disco dos datafiles dos databases.

Informações do disco utilizando DMV’s

Neste primeiro exemplo, vou demonstrar como identificar as informações de espaço em disco utilizando a view sys.master_files e a DMV dm_os_volume_stats (A view dm_os_volume_stats foi disponibilizada a partir do SQL Server 2008 R2 SP1).

SELECT DISTINCT
    VS.volume_mount_point [Montagem] ,
    VS.logical_volume_name AS [Volume] ,
    CAST(CAST(VS.total_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Total (GB)] ,
    CAST(CAST(VS.available_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Espaço Disponível (GB)] ,
    CAST(( CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço Disponível ( % )] ,
    CAST(( 100 - CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço em uso ( % )]
FROM
    sys.master_files AS MF
    CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.file_id) AS VS
WHERE
    CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 < 100;

Resultado:

Informações do disco utilizando xp_fixeddrives

Já neste exemplo, vou demonstrar como retornar o espaço em disco livre nas unidades utilizando a simples procedure xp_fixeddrives.

EXEC xp_fixeddrives;

Resultado:

Informações do disco utilizando OLE Automation

Neste terceiro exemplo, vou demonstrar como identificar as informações de espaço em disco utilizando Scripting.FileSystemObject e o método GetDrive, além da procedure xp_fixeddrives.

--------------------------------------------------------------------------------
-- Habilitando o OLE Automation (Se não estiver ativado)
--------------------------------------------------------------------------------

DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures')

IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN

    EXECUTE SP_CONFIGURE 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'Ole Automation Procedures', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


--------------------------------------------------------------------------------
-- Código fonte
--------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#drives') IS NOT NULL) DROP TABLE #drives
CREATE TABLE #drives (
    contador INT IDENTITY(1, 1),
    drive CHAR(1) PRIMARY KEY ,
    FreeSpace INT NULL ,
    TotalSize INT NULL
);

INSERT #drives ( drive, FreeSpace )
EXEC master.dbo.xp_fixeddrives;


DECLARE 
    @handler INT,
    @fso INT,
    @drive CHAR(1),
    @odrive INT,
    @Tamanto_Total BIGINT,
    @Contador INT = 1,
    @Total INT = (SELECT COUNT(*) FROM #drives),
    @MB BIGINT = 1048576


EXEC @handler= sp_OACreate 'Scripting.FileSystemObject', @fso OUT 
IF @handler <> 0
    EXEC sp_OAGetErrorInfo @fso


WHILE(@Contador <= @Total)
BEGIN

    SELECT @drive = drive
    FROM #drives
    WHERE contador = @Contador

    EXEC @handler = sp_OAMethod @fso, 'GetDrive', @odrive OUT, @drive
    IF @handler <> 0
        EXEC sp_OAGetErrorInfo @fso 

    EXEC @handler = sp_OAGetProperty @odrive, 'TotalSize', @Tamanto_Total OUT
    IF @handler <> 0
        EXEC sp_OAGetErrorInfo @odrive
        
    
    UPDATE #drives
    SET
        TotalSize = @Tamanto_Total / @MB
    WHERE   
        drive = @drive
    
    
    SET @Contador += 1


END


EXEC @handler= sp_OADestroy @fso
IF @handler <> 0
    EXEC sp_OAGetErrorInfo @fso


SELECT
    drive,
    FreeSpace AS 'Livre(MB)',
    TotalSize AS 'Total(MB)',
    CAST(( FreeSpace / ( TotalSize * 1.0 ) ) * 100.0 AS NUMERIC(18, 2)) AS 'Livre(%)'
FROM
    #drives
ORDER BY
    drive



--------------------------------------------------------------------------------
-- Desativando o OLE Automation (Se não estava habilitado antes)
--------------------------------------------------------------------------------

IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN

    EXEC sp_configure 'Ole Automation Procedures', 0;
    RECONFIGURE WITH OVERRIDE;

    EXECUTE SP_CONFIGURE 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;

END

Resultado:

Informações do disco utilizando xp_cmdshell e WMIC

Neste exemplo, vou demonstrar como identificar as informações de espaço em disco utilizando o binário do WMIC (Windows Management Instrumentation Command-line) e a Stored Procedure xp_cmdshell.

--------------------------------------------------------------------------------
-- Habilitando o xp_cmdshell (Se não estiver ativado)
--------------------------------------------------------------------------------

DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell')

IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN

    EXECUTE SP_CONFIGURE 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


--------------------------------------------------------------------------------
-- Código fonte
--------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Retorno_CmdShell') IS NOT NULL) DROP TABLE #Retorno_CmdShell
CREATE TABLE #Retorno_CmdShell (
    Id INT IDENTITY(1, 1),
    Descricao VARCHAR(MAX)
)

INSERT INTO #Retorno_CmdShell
EXEC master.dbo.xp_cmdshell 'wmic logicaldisk where drivetype=3 get Caption,FreeSpace,Size,FileSystem,VolumeName /FORMAT:list'


IF (OBJECT_ID('tempdb..#Informacoes_Disco') IS NOT NULL) DROP TABLE #Informacoes_Disco
CREATE TABLE #Informacoes_Disco (
    Ds_Drive NVARCHAR (256) COLLATE Latin1_General_CI_AI NULL,
    Ds_Volume NVARCHAR (256) COLLATE Latin1_General_CI_AI NULL,
    Ds_FileSystem NVARCHAR (128) COLLATE Latin1_General_CI_AI NULL,
    Qt_Tamanho NUMERIC(18, 2) NULL,
    Qt_Utilizado NUMERIC(18, 2) NULL,
    Qt_Livre NUMERIC(18, 2) NULL,
    Perc_Utilizado NUMERIC(18, 2) NULL,
    Perc_Livre NUMERIC(18, 2) NULL
)

DECLARE 
    @Contador INT = 3, 
    @Total INT = (SELECT COUNT(*) FROM #Retorno_CmdShell),
    @Ds_Drive VARCHAR(100),
    @Ds_Volume VARCHAR(100),
    @Ds_Filesystem VARCHAR(100),
    @Qt_Tamanho FLOAT,
    @Qt_Utilizado FLOAT,
    @Qt_Livre FLOAT,
    @Perc_Utilizado FLOAT,
    @Perc_Livre FLOAT
    
    
WHILE(@Contador <= @Total)
BEGIN
    

    SELECT @Ds_Drive = REPLACE(SUBSTRING(Descricao, CHARINDEX('=', Descricao) + 1, 99999999), CHAR(13), '')
    FROM #Retorno_CmdShell
    WHERE Id = @Contador


    -- Se chegou ao final, força sair do WHILE
    IF (NULLIF(@Ds_Drive, '') IS NULL)
        BREAK


    SELECT @Ds_Filesystem = REPLACE(SUBSTRING(Descricao, CHARINDEX('=', Descricao) + 1, 99999999), CHAR(13), '')
    FROM #Retorno_CmdShell
    WHERE Id = @Contador + 1

    SELECT @Qt_Livre = REPLACE(SUBSTRING(Descricao, CHARINDEX('=', Descricao) + 1, 99999999), CHAR(13), '')
    FROM #Retorno_CmdShell
    WHERE Id = @Contador + 2

    SELECT @Qt_Tamanho = REPLACE(SUBSTRING(Descricao, CHARINDEX('=', Descricao) + 1, 99999999), CHAR(13), '')
    FROM #Retorno_CmdShell
    WHERE Id = @Contador + 3

    SELECT @Ds_Volume = REPLACE(SUBSTRING(Descricao, CHARINDEX('=', Descricao) + 1, 99999999), CHAR(13), '')
    FROM #Retorno_CmdShell
    WHERE Id = @Contador + 4

    
    SELECT
        @Qt_Utilizado = @Qt_Tamanho - @Qt_Livre,
        @Perc_Utilizado = @Qt_Utilizado / @Qt_Tamanho,
        @Perc_Livre = @Qt_Livre / @Qt_Tamanho


    INSERT INTO #Informacoes_Disco (
        Ds_Drive ,
        Ds_Volume ,
        Ds_FileSystem ,
        Qt_Tamanho ,
        Qt_Utilizado ,
        Qt_Livre ,
        Perc_Utilizado ,
        Perc_Livre
    )
    VALUES  (
        @Ds_Drive, -- Ds_Drive - nvarchar(256)
        @Ds_Volume, -- Ds_Volume - nvarchar(256)
        @Ds_Filesystem, -- Ds_FileSystem - nvarchar(128)
        @Qt_Tamanho / 1073741824.0, -- Qt_Tamanho - float
        @Qt_Utilizado / 1073741824.0, -- Qt_Utilizado - float
        @Qt_Livre / 1073741824.0, -- Qt_Livre - float
        @Perc_Utilizado, -- Perc_Utilizado - float
        @Perc_Livre -- Perc_Livre - float
    )


    SET @Contador += 7


END


SELECT * FROM #Informacoes_Disco


--------------------------------------------------------------------------------
-- Desativando o xp_cmdshell (Se não estava habilitado antes)
--------------------------------------------------------------------------------

IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN

    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE WITH OVERRIDE;

    EXECUTE SP_CONFIGURE 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;

END

Resultado:

Informações do disco utilizando CLR

Neste último exemplo, vou demonstrar como identificar as informações de espaço em disco utilizando o CLR.

using System;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Linq;
using System.Text;

public partial class UserDefinedFunctions
{

    private class InformacaoDisco
    {

        public SqlString Ds_Drive;
        public SqlString Ds_Volume;
        public SqlString Ds_FileSystem;
        public SqlDouble Qt_Tamanho;
        public SqlDouble Qt_Utilizado;
        public SqlDouble Qt_Livre;
        public SqlDouble Perc_Utilizado;
        public SqlDouble Perc_Livre;

        public InformacaoDisco(SqlString dsDrive, SqlString dsVolume, SqlString dsFileSystem, SqlDouble qtTamanho, SqlDouble qtUtilizado, SqlDouble qtLivre, SqlDouble percUtilizado, SqlDouble percLivre)
        {

            Ds_Drive = dsDrive;
            Ds_Volume = dsVolume;
            Ds_FileSystem = dsFileSystem;
            Qt_Tamanho = qtTamanho;
            Qt_Utilizado = qtUtilizado;
            Qt_Livre = qtLivre;
            Perc_Utilizado = percUtilizado;
            Perc_Livre = percLivre;

        }

    }

    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName = "FillRow_Informacao_Disco",
        TableDefinition = "Ds_Drive NVARCHAR(10), Ds_Volume NVARCHAR(256), Ds_FileSystem NVARCHAR(128), Qt_Tamanho FLOAT, Qt_Utilizado FLOAT, Qt_Livre FLOAT, Perc_Utilizado FLOAT, Perc_Livre FLOAT"
    )]
    public static IEnumerable fncInformacao_Disco(string Ds_Servidor)
    {

        var informacaoDiscoCollection = new ArrayList();

        if (string.IsNullOrEmpty(Ds_Servidor))
            return informacaoDiscoCollection;


        var processStartInfo = new ProcessStartInfo
        {
            FileName = @"wmic",
            Arguments = "/node:\"" + Ds_Servidor + "\" logicaldisk where drivetype=3 get Caption,FreeSpace,Size,FileSystem,VolumeName /FORMAT:list",
            UseShellExecute = false,
            RedirectStandardOutput = true,
            RedirectStandardError = true,
            StandardOutputEncoding = Encoding.GetEncoding(850),
            CreateNoWindow = true
        };

        string output;
        using (var process = Process.Start(processStartInfo))
        {
            output = process?.StandardOutput.ReadToEnd();
        }


        var linhas = output?.Split('\n');
        var qtLinhas = (linhas?.Length - 1);

        for (var i = 1; i <= qtLinhas; i++)
        {

            var linha = linhas[i];

            if (linha.Trim().Length <= 0) continue;

            var parametro = linha.Split('=').First().Trim();
            if (parametro != "Caption") continue;

            var drive = linha.Split('=').Last().Trim();

            var volume = linhas[i + 4].Split('=').Last().Trim();
            var filesystem = linhas[i + 1].Split('=').Last().Trim();
            var espacoTotal = Convert.ToDouble(linhas[i + 3].Split('=').Last().Trim()) / 1073741824;
            var espacoLivre = Convert.ToDouble(linhas[i + 2].Split('=').Last().Trim()) / 1073741824;
            var espacoUtilizado = espacoTotal - espacoLivre;

            informacaoDiscoCollection.Add(new InformacaoDisco(
                drive,
                volume,
                filesystem,
                Math.Round(espacoTotal, 2),
                Math.Round(espacoUtilizado, 2),
                Math.Round(espacoLivre, 2),
                Math.Round((espacoUtilizado / espacoTotal) * 100, 2),
                Math.Round((espacoLivre / espacoTotal) * 100, 2)
            ));
            
        }
        
        
        return informacaoDiscoCollection;

    }

    protected static void FillRow_Informacao_Disco(object objInformacaoDisco, out SqlString dsDrive, out SqlString dsVolume, out SqlString dsFileSystem, out SqlDouble qtTamanho, 
        out SqlDouble qtUtilizado, out SqlDouble qtLivre, out SqlDouble percUtilizado, out SqlDouble percLivre)
    {

        var informacaoDisco = (InformacaoDisco) objInformacaoDisco;

        dsDrive = informacaoDisco.Ds_Drive;
        dsVolume = informacaoDisco.Ds_Volume;
        dsFileSystem = informacaoDisco.Ds_FileSystem;
        qtTamanho = informacaoDisco.Qt_Tamanho;
        qtUtilizado = informacaoDisco.Qt_Utilizado;
        qtLivre = informacaoDisco.Qt_Livre;
        percUtilizado = informacaoDisco.Perc_Utilizado;
        percLivre = informacaoDisco.Perc_Livre;

    }

}

Resultado:

Monitoramento o espaço livre em disco

Pessoal, agora que demonstrei diversas formas de verificar o espaço em disco no seu servidor (existem várias outras na internet), vou mostrar como monitorar o espaço em disco no servidor e enviar alertas por e-mail. Neste exemplo, vou utilizar a solução do primeiro exemplo, por ser simples e prática de implementar, mas você pode criar esse monitoramento com qualquer uma das soluções apresentadas acima.

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:

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_Espaco_Disco') IS NULL) EXEC('CREATE PROCEDURE dbo.stpMonitoramento_Espaco_Disco AS SELECT 1')
GO

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

    ------------------------------------------------------------
    -- GERAÇÃO DOS DADOS
    ------------------------------------------------------------
    
    IF (OBJECT_ID('tempdb..##Monitoramento_Espaco_Disco') IS NOT NULL) DROP TABLE ##Monitoramento_Espaco_Disco
    SELECT DISTINCT
        VS.volume_mount_point [Montagem] ,
        VS.logical_volume_name AS [Volume] ,
        CAST(CAST(VS.total_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Espaço_Total_GB] ,
        CAST(CAST(VS.available_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Tamanho_DisponIvel_GB] ,
        CAST(( CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Perc_Disponivel] ,
        CAST(( 100 - CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Perc_Utilizado]
    INTO
        ##Monitoramento_Espaco_Disco
    FROM
        sys.master_files AS MF
        CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.file_id) AS VS
    WHERE
        CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 < 100;
    
    
    DELETE FROM ##Monitoramento_Espaco_Disco
    WHERE [Perc_Utilizado] < @Vl_Limite
    
    
    ------------------------------------------------------------
    -- ENVIA NOTIFICAÇÃO
    ------------------------------------------------------------

    IF ((SELECT COUNT(*) FROM ##Monitoramento_Espaco_Disco) > 0)
    BEGIN
        
        DECLARE 
            @Ds_Saida VARCHAR(MAX),
            @Assunto VARCHAR(200) = @@SERVERNAME + ' - Monitoramento de Espaço em Disco',
            @Destinatario VARCHAR(MAX) = '[email protected]',
            @Mensagem VARCHAR(MAX)

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

        SET @Mensagem = '
Prezado DBA,<br/>
Foi identificado um problema de espaço em disco na instância ' + @@SERVICENAME + ' do 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:

-- Envia notificação se o % de espaço utilizado atingir 40%
EXEC dbo.stpMonitoramento_Espaco_Disco
    @Vl_Limite = 40 -- float

Resultado:

É isso aí, pessoal!
Até a próxima.
Abraço.

SQL Server – como identificar query identify view monitorar monitor os discos, espaço em disco total, livre e utilizado disk space used available linha de comando command line transact sql query tsql

SQL Server – como identificar query identify view monitorar monitor os discos, espaço em disco total, livre e utilizado disk space used available linha de comando command line transact sql query tsql

Dirceu Resende

Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Veja minhas certificações

Comentários (0)

Carregando comentários…

Informações do disco utilizando DMV’s

Informações do disco utilizando xp_fixeddrives

Informações do disco utilizando OLE Automation

Informações do disco utilizando xp_cmdshell e WMIC

Informações do disco utilizando CLR

Monitoramento o espaço livre em disco

SQL Server – como identificar query identify view monitorar monitor os discos, espaço em disco total, livre e utilizado disk space used available linha de comando command line transact sql query tsql

Dirceu Resende

Posts relacionados

Comentários (0)