Views: 9.177 views
Hello people,
All right with you ?
In this post I will demonstrate a very interesting and common feature in the everyday life of a DBA, which is monitoring disk space on the server. As a DBA, you should always have control over server disk space, so you don't let that space reach critical levels and you have to try to resolve at the last minute, with the database at risk of stopping for lack of data. Disk Space.
This post is a complement to my post. SQL Server - How to identify and monitor total free disk space used by database datafiles, where I demonstrated how to identify, analyze and monitor the database space disk space.
Disk information using DMV's
In this first example, I will demonstrate how to identify disk space information using the sys.master_files view and DMV dm_os_volume_stats (The dm_os_volume_stats view was made available from SQL Server 2008 R2 SP1).
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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; |
Result:
Disk Information Using xp_fixeddrives
In this example, I will demonstrate how to return free disk space on drives using the simple xp_fixeddrives procedure.
|
1 |
EXEC xp_fixeddrives; |
Result:
Disk Information Using OLE Automation
In this third example, I will demonstrate how to identify disk space information using Scripting.FileSystemObject and the GetDrive method, as well as the xp_fixeddrives procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
Result:
Disk Information Using xp_cmdshell and WMIC
In this example, I will demonstrate how to identify disk space information using the Windows Management Instrumentation Command-line (WMIC) binary and the Stored Procedure xp_cmdshell.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
Result:
Disk Information Using CLR
In this last example, I will demonstrate how to identify disk space information using the CLR.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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; } } |
Result:
Monitoring free disk space
Guys, now that I've demonstrated several ways to check disk space on your server (there are several others on the internet), I'll show you how to monitor disk space on your server and send alerts via email. In this example, I will use the solution from the first example, as it is simple and practical to implement, but you can create this monitoring with any of the solutions presented above.
To assist in generating the HTML code, I will use stpExporta_Table_HTML_Output, but could also use fncExporta_Query_HTML, available at:
- SQL Server - How to Email a Query Result in HTML Format Using CLR (C #)
- How to export data from a SQL Server table to HTML
To send the email, I will use the Stored Procedure msdb.dbo.sp_send_dbmail, from SQL Server DatabaseMail. If you have not set up or did not know how to do it, learn more by visiting SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail).
Monitoring source code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
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', @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 |
Example of use:
|
1 2 3 |
-- Envia notificação se o % de espaço utilizado atingir 40% EXEC dbo.stpMonitoramento_Espaco_Disco @Vl_Limite = 40 -- float |
Result:
That's it folks!
To the next.
Hug.
SQL Server - how to identify query identify view monitor monitor disks, total disk space, free and used disk space used available command line command line transact sql query tsql
SQL Server - how to identify query identify view monitor monitor disks, total disk space, free and used disk space used available command line command line transact sql query tsql
Sensational!! Really good.
Damn!!! This article is incredible. Excellent, congratulations!!
Good evening! Do you have this same schema for Oracle?
Hello, I'm not a DBA, but I needed some commands to identify disk space. Your post helped me a lot! Congratulations
very good !!!
Thanks, Caio. Thanks for the feedback.
Hug!