Skip to content

Dirceu Resende

DBA SQL Server e Analista de BI (PowerBI, SSAS, SSIS, SSRS)

  • Consultoria
    • Consultoria de BI
    • Consultoria de Power BI
    • Consultoria SQL Server
  • Arquivo
  • Séries
    • Certificação
    • Segurança e Auditoria
    • Performance Tuning
    • O que mudou no T-SQL ?
    • Proteção de Dados
  • Vagas de Emprego
  • Eventos Data Platform
  • Sobre
  • Contato

Other Languages

Assinar blog por e-mail

Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.

Junte-se a 547 outros assinantes

Visualizações do Blog

1.789.813 views

Categorias

  • Apache / .htaccess (9)
  • Banco de Dados (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Auditoria (15)
      • Azure (2)
      • CLR (53)
      • Desenvolvimento de Query (83)
      • DMVs e Views de Catálogo (31)
      • Erros (22)
      • Ferramentas (12)
      • Formatação e Validação de Dados (23)
      • Funcionalidades pouco conhecidas (19)
      • Hacks (17)
      • Integrações (30)
      • Manipulação de Arquivos (13)
      • Manutenção (80)
      • Monitoramento (35)
      • O que não fazer (7)
      • OLE Automation (19)
      • Performance Tuning (22)
      • Python (1)
      • Segurança (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Carreira e Cursos (13)
  • Carreira, Cursos e Certificações (28)
  • Celulares (1)
  • Eventos e Palestras (63)
  • Programação (57)
    • C# (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • Javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Sem categoria (10)
  • SEO (4)
  • Virtualização (5)

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

  • Descontos da “Black Friday” nos Treinamentos de SQL Server (Comprem meu curso kkkkk) 27 de novembro de 2020
  • SQL Server – As “novas” funções GREATEST e LEAST 27 de novembro de 2020
  • SQL Server – Como saber a data do último login de um usuário 9 de novembro de 2020
  • Azure na Prática Gratuito #07 – Administrando Banco de Dados no Azure 5 de novembro de 2020
  • Analysis Services – An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file ‘tmcachemanager.cpp’, function ‘TMCacheManager::CreateEmptyCollectionsForAllParents’) 5 de novembro de 2020
  • 4 de março de 2017
  • 0
  • Banco de Dados DMVs e Views de Catálogo Manutenção Monitoramento SQL Server

SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases

Visualizações: 6.636
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

Transact-SQL
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:

ColunaDescrição
database_idID do database gerado pelo motor do SQL Server
database_nameNome do database
state_descSituaçã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_descTipo do datafile:
ROWS = Datafile de dados
LOG = Datafile dos dados da transaction log
file_idID do datafile gerado pelo motor do SQL Server
nameNome do datafile
physical_nameCaminho físico do datafile no disco
disk_total_size_GBTamanho total em GB do disco físico onde está o datafile
disk_free_size_GBEspaço livre em GB do disco físico onde está o datafile
size_GBTamanho atual (utilizado) em GB do database
max_size_GBTamanho 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_GBTamanho 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_GBTamanho livre em GB do datafile
growth_MBTamanho em MB que o datafile vai crescer devido a eventos de Autogrowth. Se o Autogrowth estiver desativado, vai mostrar 0.00.
is_percent_growthColuna 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_enabledColuna booleana que informa se o Autogrowth do datafile está ativado (valor 1) ou desativado (valor 0)
percent_usedRetorna o valor percentual de utilização de espaço do datafile
growth_timesRetorna 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

Transact-SQL
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',
            @Destinatario VARCHAR(MAX) = '[email protected]',
            @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:

Transact-SQL
1
2
EXEC dbo.stpMonitoramento_Tamanho_Datafiles
    @Vl_Limite = 40 -- float

Resultado:

Espero que esse post tenha sido útil para vocês.
Abraço!

Tags: sqlsql server

You may also like...

  • SQL Server Configuration Manager sumiu? Saiba como recuperar o atalho

  • 2º Encontro do Chapter SQL Server ES – 10/06/2017

  • Operações com arquivos utilizando OLE Automation no SQL Server

  • Next Analysis Services – Como utilizar o XLMA para backup e restore de cubos via linha de comando T-SQL
  • Previous SQL Server 2016 – Como consultar informações de um CEP utilizando a API Bemean e a função JSON_VALUE

Deixe uma resposta Cancelar resposta

Dirceu Resende © 2021. All Rights Reserved.