A leitura diária dos arquivos de log do SQL Server é uma das tarefas primárias do DBA. Particularmente, tenho o hábito de visualizar o log corrente a cada dez minutos (no máximo), pois, a leitura do log faz parte da minha estratégia de monitoração. Nesse sentido, tenho preferido consultar os log usando T-SQL, ao invés da interface gráfica (Log File Viewer). Dentre os principais motivos para preferir o T-SQL destaco:

• A visualização fica mais fácil e o conteúdo do arquivo é carregado mais rapidamente (tente abrir um arquivo de log de erro muito grande usando o Log File Viewer e você sentirá uma certa demora);
• Usando T-SQL é possível fazer pesquisas customizadas sobre o log do mesmo modo como fazemos na interface gráfica, contudo, a pesquisa com T-SQL é mais rápida;
• E a principal vantagem, no meu ponto de vista, é que via T-SQL podemos ler o log de erro de vários servidores ao mesmo tempo, executando um único comando.

Para fazermos a leitura via T-SQL podemos utilizar as seguintes SP’s:

• Sp_readerrorlog
• Xp_readerrorlog

Os arquivos de Log

Os logs de erro do SQL Server são salvo em arquivos, por padrão, na subpasta “Log” da unidade de disco onde a instância foi instalada. É possível abrir e visualizar o conteúdo desses arquivos utilizando um editor de texto como o Notepad.

Toda vez que a instância é reiniciada, o arquivo corrente é arquivado e renomeado sob o nome ERRORLOG.1, enquanto um novo arquivo de log é criado com o nome ERRORLOG. Por default, o SQL Server mantém até seis arquivos de log arquivados. Caso queira aumentar esse limite, estando no SQL Server Management Studio, faça conforme ilustrado nas figuras abaixo:

O arquivo de log corrente tem índice zero e essa informação é importante para a utilização das SP’s de leitura do log de erro que falaremos mais adiante.

É possível listar via T-SQL os arquivos de log de erro da instância. Veja como:

exec sp_enumerrorlogs

Veja que a stored procedure listou os arquivos de log de erro. Neste caso, os seis logs arquivados e o log corrente (de índice zero). Também mostrou a data e hora da última entrada no log (último registro) e o tamanho em disco do arquivo.

Usando SP_READERRORLOG / XP_READERRORLOG

Em termos de sintaxe e de benefícios, ambas SP’s são idênticas. Contudo, é válido destacar que a XP_READERRORLOG é uma procedure estendida, além de não documentada.

Para visualizar o log de erros atual do SQL Server simplesmente execute:

exec sp_readerrorlog

Essa stored procedure tem os seguintes parâmetros opcionais:

Parâmetro	Valores	Descrição
P1	0,1,2,4,5,6…	Indica o número do arquivo de log a ser lido. O default é 0.
P2	1 ou 2	Indica o tipo de log a ser lido, onde: 1 = sql server; 2 = sql agent. O default é 1, ou seja, o log do sql server
P3		Texto entre aspas simples ou aspas duplas a ser localizado dentro do log (coluna Text). ATENÇÃO: quando usar a XP_READERRORLOG o texto deve estar entre aspas duplas. A pesquisa é parcial, procurando em qualquer parte da palavra. Deixe NULL neste parâmetro para não utilizá-lo ou simplesmente basta omiti-lo.
P4		Trata-se de uma segunda string a ser pesquisada, objetivando refinar a consulta. Na verdade este parâmetro produz a combinação P3 + P4, ou seja, as duas palavras pesquisadas devem existir na mesma entrada (linha) do log. Deixe NULL neste parâmetro para não utilizá-lo ou simplesmente basta omiti-lo.

Vejamos alguns exemplos:

Localizar as entradas que contenham a palavra AdventureWorks no log corrente do SQL Server:

exec sp_readerrorlog 0, 1, 'adventureworks'
 

Localizar as entradas que contenham as palavras CHECKDB e AdventureWorks no log corrente do SQL Server:

exec sp_readerrorlog 0, 1, 'checkdb', 'adventureworks'

Esse tipo de consulta no log é muito útil quando queremos pesquisar por erros ou, por exemplo, filtrar as entradas referentes a backup, etc.

Criando um script para consultas mais refinadas

Como comentei no início do artigo, tenho o hábito de ficar lendo o log em intervalos de no máximo dez minutos. Desta forma, não tenho a necessidade de listar o log por completo, apenas as informações dos últimos dez minutos e, caso se faça necessário, ir ampliando esse range. Fazer esse tipo de pesquisa usando a SP_READERRORLOG ou XP_REAERRORLOG não é possível. Por conta disso, estou deixando o script de uma stored procedure “personalizada”, que possibilita a realização de consultas bem flexíveis. Recomendo criar essa SP na database Master ou em um banco de dados criado para fins administrativos. Caso prefira, você pode baixar o script com o código fonte da SP aqui.

USE Master
GO

IF OBJECT_ID('dbo.uspErrorLog') IS NOT NULL
DROP PROCEDURE dbo.uspErrorLog;
GO

CREATE PROCEDURE dbo.uspErrorLog
(
-- Quantidade de minutos a retroagir na pesquisa.
-- O Default são 30 minutos
-- Informe NULL para desconsiderar e não usar este parâmetro
@MinutosRetroagir INT = 30,

-- Data inicial para a pesquisa no log.
-- Registros com datas menores serão desconsiderados
-- O Default é NULL
-- Informe NULL para desconsiderar e não usar este parâmetro
@DataInicial DATETIME = NULL,

-- Data final para a pesquisa no log.
-- Registros com datas maiores serão desconsiderados
-- O Default é NULL
-- Informe NULL para desconsiderar e não usar este parâmetro
@DataFinal DATETIME = NULL,

-- Texto a ser pesquisado dentro da coluna ProcessInfo do log
-- Exemplo: Server, Backup, SPID, etc.
-- A pesquisa pelo texto é parcial (em qualquer parte)
-- O Default é NULL
-- Informe NULL para desconsiderar e não usar este parâmetro
@Processo VARCHAR(50) = NULL,

-- Texto a ser pesquisado dentro da coluna Text do log
-- Exemplo: error, starting, etc.
-- A pesquisa pelo texto é parcial (em qualquer parte)
-- O Default é NULL
-- Informe NULL para desconsiderar e não usar este parâmetro
@Texto VARCHAR(100) = NULL,

-- Filtra a pesquisa para exibir apenas o log do nome do servidor informado
-- Use este parâmetro quando estiver pesquisando o log de vários servidores ao mesmo tempo
-- O Default é NULL
-- Informe NULL para desconsiderar e não usar este parâmetro
@NomeServidor VARCHAR(128) = NULL
)
AS

DECLARE @Tmp TABLE
(    ID INT IDENTITY,
Data DATETIME,
Processo VARCHAR(50),
Texto VARCHAR(4000)
);
INSERT INTO @Tmp (Data, Processo, Texto) exec sp_readerrorlog;

SELECT * FROM @Tmp t
WHERE t.Data >=
CASE WHEN @MinutosRetroagir IS NOT NULL THEN DATEADD(MINUTE, -@MinutosRetroagir, GETDATE())
ELSE t.Data END

AND t.Data >= ISNULL(@DataInicial, t.Data)

AND t.Data <= ISNULL(@DataFinal, t.Data)

AND t.Processo LIKE
CASE WHEN @Processo IS NOT NULL THEN '%' + @Processo + '%'
ELSE t.Processo END

AND t.Texto LIKE
CASE WHEN @Texto IS NOT NULL THEN '%' + @Texto + '%'
ELSE t.Texto END

AND SERVERPROPERTY('ServerName') =
ISNULL(@NomeServidor, CONVERT(VARCHAR(128), SERVERPROPERTY('ServerName')))

ORDER BY t.ID DESC;

GO

Reciclando o log de erro

Como já foi mencionado, o log de erro é reciclado (reiniciado) automaticamente toda vez que a instância sofre um restart. Considerando que um servidor de banco não é resetado com frequência – normalmente permanece vários meses online, a tendência é o log ficar demasiado grande, dificultando a análise e leitura. Assim, quero deixar uma sugestão: recicle manualmente, de tempos em tempos, o log de erro do SQL Server. Particularmente, faço essa reciclagem a cada sete dias – normalmente crio um job que é executado aos domingos. Contudo, essa frequência de reciclagem varia de ambiente para ambiente, além de preferências e políticas.

O comando para reciclar manualmente o log de erros é o seguinte:

DBCC ERRORLOG

Conclusão

Inclua em sua estratégia diária de monitoramento a leitura dos log’s de erro do SQL Server. Em ambientes com várias instâncias essa tarefa pode ser facilitada, e muito, através do T-SQL, permitindo analisar o log de várias instâncias ao mesmo.

Até o próximo artigo!