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

Visualizações: 11.339 views

Neste artigo

Tempo de Leitura: 8 minutos

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;

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;

1	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

100

101

102

103

104

105

106

107

108

109

110

--------------------------------------------------------------------------------

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

--------------------------------------------------------------------------------

-- 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;

    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

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) = 'dirceu.resende@yahoo.com.br',
            @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

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

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

Exemplo de uso:

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

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