Script: Relatório diário via e-mail de instâncias SQL Server

Monitorar instâncias do SQL Server é uma tarefa que requer o uso de vários recursos, como DMVs, log de erros, contadores de performance, SQL Traces, etc; é um verdadeiro canivete suíço. Na busca por facilitar o meu trabalho,  eu incluí na minha estratégia de monitoramento diário uma stored procedure que é executada todos os dias, no início da manhã, coletando informações que considero vitais. Essa SP coleta os dados e gera um relatório em formato HTML, enviando-o para minha caixa de e-mail. Assim, quando chego na empresa a primeira coisa que faço é conferir os relatórios enviados para o meu e-mail. Além disso, essa SP também pode ser usada para capturar uma imagem de como está a instância naquele momento. A stored procedure tem apenas um parâmetro: @ExibirApenasHtm, cujo valor default é 0. Se você executar a SP passando o valor 1 então, ao invés de enviar um e-mail, será exibida uma string contendo o HTML do relatório. Copie essa string e cole no seu editor HTML preferido. Fique à vontade para adaptar a SP conforme as suas necessidades. Ela é compatível apenas com o SQL Server 2008 e versões superiores. ATENÇÃO: para usar o recurso de e-mail na stored procedure é necessário configurar o Database Mail.

 

Segue alguns trechos que o relatório exibe:

image

image

image

image

Exemplo de como executar a stored procedure enviando o relatório para uma conta de e-mail:

use master

go

EXEC dbo.uspRelatorioDiario;

 

Exemplo de como executar a stored procedure gerando apenas a string contendo o HTML do relatório:

use master

go

EXEC dbo.uspRelatorioDiario 1;

 

Segue nas linhas abaixo o código fonte da procedure. Se você preferir, pode baixá-la aqui.

USE DBAdmin
GO

/* SP para ser executada diariamente. Exibe resumo da situação atual do SGDB. */
if OBJECT_ID(‘dbo.uspRelatorioDiario’) IS NOT NULL
DROP PROCEDURE dbo.uspRelatorioDiario;
GO

CREATE PROCEDURE dbo.uspRelatorioDiario @ExibirApenasHtml BIT = 0
AS
SET LANGUAGE US_ENGLISH;

SET NOCOUNT ON;

— *** Variáveis
DECLARE @vSubject NVARCHAR(255) = ‘Relatório Diário do SQL Server: ‘+@@SERVERNAME;
DECLARE @vBody AS NVARCHAR(MAX) = ”;

— *** Parte 1: Informações de configuração da instância
IF OBJECT_ID(‘tempdb.dbo.#Tabela’) IS NOT NULL
DROP TABLE #Tabela;

DECLARE @vOnline_Since AS NVARCHAR (10) = ”;
DECLARE @vUptime_Days AS INT = 0;

SELECT
@vOnline_Since = CONVERT(NVARCHAR(10), DB.sqlserver_start_time,103)
, @vUptime_Days = DATEDIFF(DAY,DB.sqlserver_start_time,GETDATE())
FROM
sys.dm_os_sys_info DB;

SELECT
SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS netbios_name
,@@SERVERNAME AS server_name
,SERVERPROPERTY(‘EDITION’) AS edition
,SERVERPROPERTY (‘ProductVersion’) AS version
,SERVERPROPERTY (‘ProductLevel’) AS [level]
,@vOnline_Since AS online_since
,@vUptime_Days AS uptime_days
INTO #Tabela;

SET @vBody =

<h3>Informações da Instância</h3>
<table border=”1″ cellpadding=”2″>
<tr>
<th>Nome NetBIOS</th>
<th>Nome da Instância</th>
<th>Edição</th>
<th>Versão</th>
<th>Level</th>
<th>Online desde</th>
<th>Qtde de dias online</th>
</tr>’;
SET @vBody = @vBody +
(
select
‘<tr>’+
‘<td>’+CONVERT(NVARCHAR,t.netbios_name)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,server_name)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,edition)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,version)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,level)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,online_since)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,uptime_days)+'</td>’+
‘</tr>’
from dbo.#Tabela t
)
SET @vBody = @vBody + ‘</table>’;

— *** Parte 2: Log de error do SQL
DECLARE
@Qt INT = 0,
@Loop INT = 1;

DECLARE @LogSQL1 TABLE
(    Seq INT IDENTITY(1,1),
LogDate DATETIME,
ProcessInfo VARCHAR(50),
Text VARCHAR(4000)
);
INSERT INTO @LogSql1 exec sp_readerrorlog;

— Tabela para somente erros
DECLARE @LogSQL2 TABLE
(    Seq INT IDENTITY(1,1),
LogDate DATETIME,
ProcessInfo VARCHAR(50),
Text VARCHAR(4000)
);
INSERT INTO @LogSQL2
SELECT TOP 30
LogDate, ProcessInfo, Text
FROM @LogSQL1 l
WHERE l.Text LIKE ‘%erro%’
ORDER BY 1 DESC;
SET @Qt = @@ROWCOUNT;
SET @vBody = @vBody +

<br><br>
<h3>Últimos 30 registros contendo a palavra “erro” no Log de Erros do SQL Server</h3>
<table border=1 cellpadding=2>
<tr>
<th>Data do Log</th>
<th>Processo</th>
<th>Texto</th>
</tr>
‘;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+CONVERT(VARCHAR,LogDate)+'</td>’+
‘<td>’+CONVERT(VARCHAR,ProcessInfo)+'</td>’+
‘<td>’+CONVERT(NVARCHAR(4000),Text)+'</td>’+
‘</tr>’
FROM @LogSQL2 t
WHERE t.Seq = @Loop
);
SET @Loop = @Loop +1;
END;
SET @vBody = @vBody + ‘</table>’;

— últimos 30 registros
DECLARE @LogSQL3 TABLE
(    Seq INT IDENTITY(1,1),
LogDate DATETIME,
ProcessInfo VARCHAR(50),
Text VARCHAR(4000)
);
INSERT INTO @LogSQL3
SELECT TOP 30
LogDate, ProcessInfo, Text
FROM @LogSQL1 l
ORDER BY 1 DESC;
SET @Qt = @@ROWCOUNT;
SET @Loop = 1;
SET @vBody = @vBody +

<br><br>
<h3>Últimos 30 registros do Log de Erros do SQL Server</h3>
<table border=1 cellpadding=2>
<tr>
<th>Data do Log</th>
<th>Processo</th>
<th>Texto</th>
</tr>
‘;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+CONVERT(VARCHAR,LogDate)+'</td>’+
‘<td>’+CONVERT(VARCHAR,ProcessInfo)+'</td>’+
‘<td>’+CONVERT(VARCHAR(4000),Text)+'</td>’+
‘</tr>’
FROM @LogSQL3 t
WHERE t.Seq = @Loop
);
SET @Loop = @Loop +1;
END;
SET @vBody = @vBody + ‘</table>’;

— *** Parte 3: Tamanho dos discos
DECLARE @vFixed_Drives_Free_Space_Table AS TABLE
(    drive_letter VARCHAR (5),
free_space_mb BIGINT,
Seq INT IDENTITY(1,1));
INSERT INTO @vFixed_Drives_Free_Space_Table
(
drive_letter
,free_space_mb
)
EXEC master.dbo.xp_fixeddrives;

SET @Qt = @@ROWCOUNT;
SET @Loop = 1;

SET @vBody = @vBody +

<br><br>
<h3>Espaço livre nas unidades de disco</h3>
<table border=1 cellpadding=2>
<tr>
<th>Unidade</th>
<th>Espaço Livre (MB)</th>
</tr>
‘;

WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+CONVERT(VARCHAR,drive_letter)+'</td>’+
‘<td>’+REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, free_space_mb), 1)), 4, 15))+'</td>’+
‘</tr>’
FROM @vFixed_Drives_Free_Space_Table t
WHERE t.Seq = @Loop
);
SET @Loop = @Loop +1;
END;

SET @vBody = @vBody + ‘</table>’;

— *** Parte 4: tamanho das databases
CREATE TABLE #Tamanhos
(
Seq INT IDENTITY,
Banco VARCHAR(50),
ArquivoDeDados_EspacoReservadoEmDisco_MB DECIMAL(15,2),
ArquivoDeDados_EspacoUsado_MB DECIMAL(15,2),
ArquivoDeDados_EspacoLivre_MB DECIMAL(15,2)
);

EXEC sp_MSforeachdb ‘USE ?
INSERT INTO #Tamanhos
(
Banco
, ArquivoDeDados_EspacoReservadoEmDisco_MB
, ArquivoDeDados_EspacoUsado_MB
, ArquivoDeDados_EspacoLivre_MB
)
SELECT
DB_NAME() Banco
, CAST(a.EspacoReservadoEmDisco AS DECIMAL(15,2)) EspacoReservadoEmDisco_MB
, CAST(a.EspacoUsado AS DECIMAL(15,2)) EspacoUsado_MB
, CAST(a.EspacoReservadoEmDisco – a.EspacoUsado AS DECIMAL(15,2)) EspacoLivre_MB
FROM
(
select
(select SUM(ps.reserved_page_count)/128.0 from sys.dm_db_partition_stats ps) EspacoUsado
, (select SUM(size/128.0) from sys.database_files where type IN (0,2,4)) EspacoReservadoEmDisco
) a’;

DECLARE @Tamanhos TABLE
(
Seq INT IDENTITY,
Banco VARCHAR(50),
ArquivoDeDados_EspacoReservadoEmDisco_MB DECIMAL(15,2),
ArquivoDeDados_EspacoUsado_MB DECIMAL(15,2),
ArquivoDeDados_EspacoLivre_MB DECIMAL(15,2),
ArquivoDeLog_EspacoReservadoEmDisco_MB DECIMAL(15,2),
ArquivoDeLog_EspacoUsado_MB DECIMAL(15,2),
ArquivoDeLog_EspacoLivre_MB DECIMAL(15,2)
);

INSERT INTO @Tamanhos
SELECT
t.Banco
, t.ArquivoDeDados_EspacoReservadoEmDisco_MB
, t.ArquivoDeDados_EspacoUsado_MB
, t.ArquivoDeDados_EspacoLivre_MB
, l.EspacoReservadoEmDisco_MB AS ArquivoDeLog_EspacoReservadoEmDisco_MB
, l.EspacoUsado_MB AS ArquivoDeLog_EspacoUsado_MB
, CAST(l.EspacoReservadoEmDisco_MB-l.EspacoUsado_MB AS DECIMAL(10,2)) ArquivoDeLog_EspacoLivre_MB
FROM #Tamanhos t
JOIN
(
SELECT a.Banco, a.EspacoReservadoEmDisco_MB, b.EspacoUsado_MB
FROM
(
select
RTRIM(p.instance_name) AS Banco
, CAST(p.cntr_value/1024.0 AS DECIMAL(15,2)) AS EspacoReservadoEmDisco_MB
from sys.dm_os_performance_counters p
WHERE p.counter_name LIKE ‘Log File(s) Size (KB)%’
) a
JOIN
(
select
RTRIM(p.instance_name) AS Banco
, CAST(p.cntr_value/1024.0 AS DECIMAL(15,2)) AS EspacoUsado_MB
from sys.dm_os_performance_counters p
where p.counter_name LIKE ‘Log File(s) Used Size (KB)%’
) b ON a.Banco = b.Banco
WHERE a.Banco NOT IN (‘_Total’, ‘mssqlsystemresource’, ‘tempdb’, ‘master’, ‘model’, ‘msdb’)
) l ON t.Banco = l.Banco
ORDER BY Banco;

DROP TABLE #Tamanhos;

SET @vBody = @vBody +

<br><br>
<h3>Tamanho dos Bancos de Dados de Usuário</h3>
<table border=1 cellpadding=2>
<tr>
<th>Banco</th>
<th>Arquivo de Dados – Espaço Reservado em Disco (MB)</th>
<th>Espaço Usado (MB)</th>
<th>Espaço Livre (MB)</th>
<th>Arquivo de Log – Espaço Reservado em Disco (MB)</th>
<th>Espaço Usado (MB)</th>
<th>Espaço Livre (MB)</th>
</tr>
‘;

SELECT @Qt = COUNT(*) FROM @Tamanhos t;
SET @Loop = 1;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+ Banco +'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeDados_EspacoReservadoEmDisco_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeDados_EspacoUsado_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeDados_EspacoLivre_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeLog_EspacoReservadoEmDisco_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeLog_EspacoUsado_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeLog_EspacoLivre_MB)+'</td>’+
‘</tr>’
FROM @Tamanhos t
WHERE t.Seq = @Loop
);
SET @Loop = @Loop +1;
END;

SET @vBody = @vBody + ‘</table>’;

— *** Parte 5: TempDB Size
SET @vBody = @vBody +

<br><br>
<h3>Espaço no TempDB</h3>
<table border=1 cellpadding=2>
<tr>
<th>Páginas Livres</th>
<th>Espaço Livre em MB</th>
</tr>
‘;
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+CONVERT(VARCHAR(20),SUM(unallocated_extent_page_count))+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),CAST(SUM(unallocated_extent_page_count)/128.0 AS decimal(15,2)))+'</td>’+
‘</tr>’
FROM sys.dm_db_file_space_usage
);
SET @vBody = @vBody + ‘</table>’;

— *** Parte 6: Last backup with success
SET @vBody = @vBody +

<br><br>
<h3>Últimos Backups Realizados</h3>
<table border=1 cellpadding=2>
<tr>
<th>Banco</th>
<th>Descrição Opcional do Backup</th>
<th>Modelo de Recovery</th>
<th>Data e Hora de Início</th>
<th>Idade em dias</th>
<th>Tamanho do Backup em MB</th>
<th>Tipo</th>
<th>Arquivo</th>
</tr>
‘;
DECLARE @Backups TABLE
(
seq INT identity(1,1),
database_name NVARCHAR(128),
server_name NVARCHAR(128),
name  NVARCHAR(128),
recovery_model  NVARCHAR(60),
backup_start_date DATETIME,
days_ago VARCHAR(15),
backup_size_mb VARCHAR(15),
type char(1),
backup_type varchar(21),
physical_device_name nvarchar(260)
);

INSERT INTO @Backups
select
s.database_name
, s.server_name
, ISNULL(s.name,”)
, s.recovery_model
, s.backup_start_date
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, DATEDIFF (DAY, s.backup_start_date, GETDATE ())), 1)), 4, 15)) AS days_ago
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, ROUND (s.backup_size/1048576.0, 0)), 1)), 4, 15)) AS backup_size_mb
, s.type
,(CASE
WHEN s.type = ‘D’ THEN ‘Database’
WHEN s.type = ‘F’ THEN ‘File Or Filegroup’
WHEN s.type = ‘G’ THEN ‘Differential File’
WHEN s.type = ‘I’ THEN ‘Differential Database’
WHEN s.type = ‘L’ THEN ‘Log’
WHEN s.type = ‘P’ THEN ‘Partial’
WHEN s.type = ‘Q’ THEN ‘Differential Partial’
ELSE ‘N/A’
END) AS backup_type
, f.physical_device_name
from msdb.dbo.backupset s
JOIN msdb.dbo.backupmediafamily f ON s.media_set_id = f.media_set_id
WHERE s.backup_set_id =
(    SELECT TOP 1 a.backup_set_id
FROM msdb.dbo.backupset a
WHERE a.database_name = s.database_name
ORDER BY a.backup_set_id DESC    )
ORDER BY s.database_name;

SELECT @Qt = @@ROWCOUNT;
SET @Loop = 1;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
select
‘<tr>’+
‘<td>’+CONVERT(VARCHAR(128),database_name)+'</td>’+
‘<td>’+CONVERT(VARCHAR(128),name)+'</td>’+
‘<td>’+CONVERT(VARCHAR(60),recovery_model)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),backup_start_date, 13)+'</td>’+
‘<td>’+CONVERT(VARCHAR(10),days_ago)+'</td>’+
‘<td>’+CONVERT(VARCHAR(10),backup_size_mb)+'</td>’+
‘<td>’+CONVERT(VARCHAR(25),backup_type)+'</td>’+
‘<td>’+CONVERT(VARCHAR(260),physical_device_name)+'</td>’+
‘</tr>’
from @Backups
WHERE seq = @Loop
);
SET @Loop = @Loop +1;
END;
SET @vBody = @vBody + ‘</table>’;

— *** Parte 7: status execution jobs
SET @vBody = @vBody +

<br><br>
<h3>Status da Última Execução dos Jobs</h3>
<table border=1 cellpadding=2>
<tr>
<th>Nome</th>
<th>Status</th>
<th>Mensagem</th>
<th>Data da Execução</th>
<th>Hora da Execução</th>
</tr>
‘;
DECLARE @Jobs TABLE
(
Seq INT IDENTITY,
name SYSNAME,
status VARCHAR(50),
message NVARCHAR(4000),
run_date varchar(20),
run_time varchar(20)
);
INSERT INTO @Jobs
SELECT
j.name
, (CASE h.run_status
WHEN 0 THEN ‘Falha’
WHEN 1 THEN ‘Sucesso’
WHEN 2 THEN ‘Repetir’
WHEN 3 THEN ‘Cancelado’
WHEN 4 THEN ‘Em Progresso’
END) [status]
, h.message
, h.run_date
, h.run_time
FROM msdb.dbo.sysjobs j
CROSS APPLY
(    SELECT TOP 1 h.run_date, h.run_time, h.run_status, h.message
from msdb.dbo.sysjobhistory h
WHERE h.step_id = 0
AND h.job_id = j.job_id
ORDER BY h.instance_id DESC
) h
ORDER BY name;

SELECT @Qt = @@ROWCOUNT;
SET @Loop = 1;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
select
‘<tr>’+
‘<td>’+CONVERT(VARCHAR(128),j.name)+'</td>’+
‘<td>’+CONVERT(VARCHAR(50),j.status)+'</td>’+
‘<td>’+CONVERT(NVARCHAR(4000),message)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),j.run_date)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),j.run_time)+'</td>’+
‘</tr>’
from @Jobs j
WHERE seq = @Loop
);
SET @Loop = @Loop +1;
END;
SET @vBody = @vBody + ‘</table>’;

— *** Envia
if @ExibirApenasHtml = 0
exec msdb.dbo.sp_send_dbmail
@recipients = ‘adeilsonrbrito@gmail.com’,
@subject=@vSubject,
@body=@vBody,
@body_format = ‘HTML’;
— *** Exibe como HTML ao invés de enviar por e-mail
else
SELECT @vBody;

— *** Final: elimina tabelas temporárias
IF OBJECT_ID(‘tempdb.dbo.#Tamanhos’) IS NOT NULL
DROP TABLE #Tamanhos;
IF OBJECT_ID(‘tempdb.dbo.#Tabela’) IS NOT NULL
DROP TABLE #Tabela;


3 Comentários on “Script: Relatório diário via e-mail de instâncias SQL Server”

  1. Cleber disse:

    Primeiro quero agradecer por compartilhar seu conhecimento! Seu blog me ajudou e ainda ajuda muito!

    Usei se script em meu ambiente porem, em algumas instancias do SQL Serve funcionou sem problemas porem ao executar a SP em uma instancia em que tenho uma base com o nome ‘Share_Point_AdminContent_5fsfagf98a-c641-8b09-gtg4e0-bd7095f31fb09’ retorna o erro
    ‘ Msg 911, Level 16, State 1, Line 1
    Database ‘Share_Point_AdminContent_5fsfagf9’ does not exist. Make sure that the name is entered correctly. ‘ …

    Em outras instalações em que tenho bases com este mesmo padrão de nome tambem tenho o mesmo erro.

    Você sabe o pode estar causando o erro?

    Mais uma vez, obrigado!

    Abraços!

  2. Adeilson Brito disse:

    Valeu Cleber. Este script funciona apenas com sql server versão 2008 ou superior. Se as instâncias forem sql 2005 me avise, pois mando para você a versão do script compatível com 2005.

  3. Cleber disse:

    O erro aconteceu no 2008 R2.
    Gostaria da versão para 2005, vai me ajudar muito no controle do ambiente.

    Abs


Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s