Tempo de Leitura: 5 minutos
Olá pessoal,
Como vocês estão ?
Neste post, vou demonstrar como identificar e monitorar o espaço em disco total, livre e utilizado dos databases da sua instância. Esse post é um complemento do meu post SQL Server – Como identificar e monitorar os discos, espaço em disco total, livre e utilizado, onde demonstrei como identificar, analisar e monitorar o espaço em disco das unidades de discos.
Como identificar o espaço total, livre e utilizado dos databases
Para essa necessidade, vou utilizar as views:
- sys.master_files: Informações dos datafiles de seus databases
- sys.databases: Informações dos databases
- sys.dm_os_volume_stats: DMV que mostra as informações dos discos físicos de uma datafile
Para criar o script abaixo, eu decidi fazer algumas validações sobre as informações da view, uma vez que algumas situações acabando gerando informações imprecisas e incorretas no seu monitoramento. Essas validações são:
- Se o tamanho máximo do datafile for maior que o tamanho máximo do disco, o tamanho máximo real do datafile será o tamanho máximo do disco e não do datafile
- Se o tamanho máximo do datafile estiver configurado como “Unlimited”, o tamanho máximo real do datafile será o tamanho máximo do disco e o espaço livre do datafile será o espaço livre do disco em que está o datafile
- Se o crescimento do datafile estiver configurado como percentual, algumas colunas não serão calculadas, ficando com o valor NULL (growth_times)
Visualizar código-fonte
| 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 | IF (OBJECT_ID('tempdb..#Datafile_Size ') IS NOT NULL) DROP TABLE #Datafile_Size SELECT B.database_id AS database_id, B.[name] AS [database_name], 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 INTO #Datafile_Size 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 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 #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 #Datafile_Size A SELECT * FROM #Datafile_Size |
Resultado do script:
Colunas retornadas:
| Coluna | Descrição |
|---|---|
| database_id | ID do database gerado pelo motor do SQL Server |
| database_name | Nome do database |
| state_desc | Situação do database. Valores possíveis: 0 = ONLINE 1 = RESTORING 2 = RECOVERING | SQL Server 2008 até a versão atual 3 = RECOVERY_PENDING | SQL Server 2008 até a versão atual 4 = SUSPECT 5 = EMERGENCY | SQL Server 2008 até a versão atual 6 = OFFLINE | SQL Server 2008 até a versão atual 7 = COPYING | Azure SQL Database 10 = OFFLINE_SECONDARY | Azure SQL Database |
| type_desc | Tipo do datafile: ROWS = Datafile de dados LOG = Datafile dos dados da transaction log |
| file_id | ID do datafile gerado pelo motor do SQL Server |
| name | Nome do datafile |
| physical_name | Caminho físico do datafile no disco |
| disk_total_size_GB | Tamanho total em GB do disco físico onde está o datafile |
| disk_free_size_GB | Espaço livre em GB do disco físico onde está o datafile |
| size_GB | Tamanho atual (utilizado) em GB do database |
| max_size_GB | Tamanho máximo em GB do datafile, conforme configurado no Autogrowth. Se o Autogrowth estiver desativado, vai mostrar 0.00. Caso o limite do Autogrowth esteja configurado como "Unlimited", essa coluna vai exibir valores negativos ou 2048 GB, dependendo da versão do seu SQL Server. |
| max_real_size_GB | Tamanho máximo REAL em GB do datafile. Conforme já explicado acima, são feitas algumas validações para obter o tamanho real do datafile, pois em algumas situações, o tamanho máximo do datafile que a view retorna não é o real. |
| free_space_GB | Tamanho livre em GB do datafile |
| growth_MB | Tamanho em MB que o datafile vai crescer devido a eventos de Autogrowth. Se o Autogrowth estiver desativado, vai mostrar 0.00. |
| is_percent_growth | Coluna booleana que informa se a forma de crescimento do datafile está configurado para um tamanho em MB (valor 0) ou um valor em percentual (valor 1) |
| is_autogrowth_enabled | Coluna booleana que informa se o Autogrowth do datafile está ativado (valor 1) ou desativado (valor 0) |
| percent_used | Retorna o valor percentual de utilização de espaço do datafile |
| growth_times | Retorna a quantidade de vezes que o datafile pode crescer através de eventos de autogrowth. Essa coluna é calculada apenas se o tipo do autogrowth estiver configurado para crescer por tamanho. Se o autogrowth estiver configurado para crescer por porcentagem, essa coluna terá o valor preenchido com NULL. |
Como monitorar o espaço total, livre e utilizado dos databases
Pessoal, agora que demonstrei como verificar o espaço em disco dos seus datafiles (existem várias outras na internet), vou mostrar como monitorar o espaço em disco dos datafiles de suas instâncias e enviar alertas por e-mail.
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:
- SQL Server – Como enviar o resultado de uma query por e-mail no formato HTML utilizando o CLR (C#)
- Como exportar dados de uma tabela do SQL Server para HTML
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
| 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 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 | IF (OBJECT_ID('dbo.stpMonitoramento_Tamanho_Datafiles') IS NULL) EXEC('CREATE PROCEDURE dbo.stpMonitoramento_Tamanho_Datafiles AS SELECT 1') GO ALTER PROCEDURE dbo.stpMonitoramento_Tamanho_Datafiles ( @Vl_Limite FLOAT = 80 ) AS BEGIN ------------------------------------------------------------------------------------------------ -- IDENTIFICAÇÃO DO ESPAÇO UTILIZADO PELOS DATAFILES ------------------------------------------------------------------------------------------------ IF (OBJECT_ID('tempdb..#Monitor_Datafile_Size') IS NOT NULL) DROP TABLE #Monitor_Datafile_Size SELECT B.database_id AS database_id, B.[name] AS [database_name], 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 INTO #Monitor_Datafile_Size 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 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 #Monitor_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 #Monitor_Datafile_Size A IF (OBJECT_ID('tempdb..##Monitoramento_Datafile_Size') IS NOT NULL) DROP TABLE ##Monitoramento_Datafile_Size SELECT A.[database_name], A.[name], A.[type_desc], A.size_GB, A.max_real_size_GB , A.free_space_GB, A.growth_MB, A.growth_times, A.percent_used INTO ##Monitoramento_Datafile_Size FROM #Monitor_Datafile_Size A WHERE percent_used >= @Vl_Limite ------------------------------------------------------------------------------------------------ -- GERAÇÃO DOS ALERTAS ------------------------------------------------------------------------------------------------ IF (OBJECT_ID('dbo.Historico_Tamanho_Datafile') IS NULL) BEGIN CREATE TABLE dbo.Historico_Tamanho_Datafile ( Id_Evento INT IDENTITY(1, 1) NOT NULL, Dt_Evento DATETIME DEFAULT GETDATE() NOT NULL, Nm_Database VARCHAR(256) NOT NULL, Nm_Datafile VARCHAR(256) NOT NULL, Ds_Tipo VARCHAR(10) NOT NULL, Qt_Tamanho NUMERIC(18, 2) NOT NULL, Qt_Tamanho_Maximo NUMERIC(18, 2) NOT NULL, Qt_Espaco_Livre NUMERIC(18, 2) NOT NULL, Qt_Aumento_Autogrowth INT NOT NULL, Qt_Vezes_Autogrowth INT NULL, Pr_Utilizacao NUMERIC(5, 2) ) END IF ((SELECT COUNT(*) FROM ##Monitoramento_Datafile_Size) > 0) BEGIN INSERT INTO dbo.Historico_Tamanho_Datafile ( Nm_Database, Nm_Datafile, Ds_Tipo, Qt_Tamanho, Qt_Tamanho_Maximo, Qt_Espaco_Livre, Qt_Aumento_Autogrowth, Qt_Vezes_Autogrowth, Pr_Utilizacao ) SELECT A.[database_name], A.[name], A.[type_desc], A.size_GB, A.max_real_size_GB , A.free_space_GB, A.growth_MB, A.growth_times, A.percent_used FROM ##Monitoramento_Datafile_Size A DECLARE @Ds_Saida VARCHAR(MAX), @Assunto VARCHAR(200) = @@SERVERNAME + ' - Monitoramento de Espaço dos Datafiles', @Mensagem VARCHAR(MAX) EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Monitoramento_Datafile_Size', -- varchar(max) @Fl_Aplica_Estilo_Padrao = 1 , -- bit @Ds_Saida = @Ds_Saida OUTPUT -- varchar(max) SET @Mensagem = ' Prezado DBA,<br/> Foi identificado um ou mais problemas de espaço em disco nos datafiles da instância ' + @@SERVICENAME + ' no 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:
| 1 2 | EXEC dbo.stpMonitoramento_Tamanho_Datafiles @Vl_Limite = 40 -- float |
Resultado:
Espero que esse post tenha sido útil para vocês.
Abraço!
