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;