Hola, chicos,
¿Estás bien?

En esta publicación demostraré una característica muy interesante y común en la vida diaria de un DBA, que es monitorear el espacio en disco en el servidor. Como DBA siempre debes tener control sobre el espacio en disco del servidor, de modo que no dejes que este espacio alcance niveles críticos y tengas que intentar solucionarlo en el último momento, corriendo la base de datos el riesgo de detenerse por falta de espacio en disco.

Este post es un complemento a mi post. SQL Server: cómo identificar y monitorear el espacio total en disco, libre y utilizado por los archivos de datos de la base de datos, donde demostré cómo identificar, analizar y monitorear el espacio en disco de archivos de datos de bases de datos.

Información del disco usando el DMV

En este primer ejemplo, demostraré cómo identificar la información del espacio en disco usando la vista sys.master_files y DMV dm_os_volume_stats (la vista dm_os_volume_stats estuvo disponible a partir de 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:

Información del disco usando xp_fixeddrives

En este ejemplo, demostraré cómo devolver espacio libre en disco en las unidades mediante el sencillo procedimiento xp_fixeddrives.

EXEC xp_fixeddrives;

Resultado:

Información del disco usando OLE Automation

En este tercer ejemplo, demostraré cómo identificar información de espacio en disco usando Scripting.FileSystemObject y el método GetDrive, además del procedimiento 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:

Información del disco usando xp_cmdshell y WMIC

En este ejemplo, demostraré cómo identificar la información del espacio en disco utilizando el binario WMIC (línea de comandos del Instrumental de administración de Windows) y el procedimiento almacenado 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:

Información del disco usando CLR

En este último ejemplo, demostraré cómo identificar información de espacio en disco usando 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:

Monitorear el espacio libre en disco

Chicos, ahora que les he demostrado varias formas de verificar el espacio en disco de su servidor (hay varias otras en Internet), les mostraré cómo monitorear el espacio en disco de su servidor y enviar alertas por correo electrónico. En este ejemplo, usaré la solución del primer ejemplo, ya que es simple y práctica de implementar, pero puedes crear este monitoreo con cualquiera de las soluciones presentadas anteriormente.

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

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).

Código fuente de seguimiento:

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

Ejemplo de uso:

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

Resultado:

¡Eso es todo, amigos!
Hasta la próxima.
Abrazo.

SQL Server: cómo identificar consultas, identificar, ver, monitorear, monitorear discos, total, espacio libre y usado en disco, espacio en disco usado, línea de comando disponible, línea de comando, transact sql, consulta tsql

SQL Server: cómo identificar consultas, identificar, ver, monitorear, monitorear discos, total, espacio libre y usado en disco, espacio en disco usado, línea de comando disponible, línea de comando, transact sql, consulta tsql