Olá,
Esse é o último post da série ERRORLOG. Caso tenha interesse:
• Parte 1: O básico
• Parte 2: Dicas e Casos
• Parte 3: Na prática (você está aqui)
O assunto de hoje é relacionado à importância do ERRORLOG no dia a dia e a sugestão de soluções que façam proveito dele.
ERRORLOG na essência
Apesar da palavra ERROR compor o ERRORLOG, já vimos que este log do SQL Server não se limita apenas a registrar erros: registra eventos.
Dito isso, deixo aqui uma frase inteligente que catei no highscalability.com e que tem tudo a ver com o post:
• @rolandkuhn: «the event log is a database of the past, not just of the present» — @jboner at #reactconf
Em bom português: O log de eventos é um banco de dados do passado, não somente do presente. Em parábola: um log de eventos conta uma história do antes e do agora.
Alguns usam o ERRORLOG apenas para respostas pontuais (geralmente reativas), outros para verificação rotineira (pró-ativa), mas é fato de que uma hora ou outra, estes eventos se mostram de grande valor para agregar informações em nosso dia a dia, independente da abordagem que temos com estes arquivos.
Tendo dito que o ERRORLOG é um banco de dados da própria instância, concluo que:
• Se é um “banco de dados”, merece ser armazenado;
• Se é um “banco de dados”, merece ter informação extraída para agregar em análises;
Armazenamento de ERRORLOG
Acredite se quiser, mas caso você nunca tenha precisado do ERRORLOG e venha a precisar, é decepcionante digitar na unha um sp_readerrorlog e descobrir que os eventos que você queria ler já foram reciclados 🙂
Pior ainda: se numa crise brava, o ERRORLOG for sua única opção de evidência e por um motivo e outro os eventos deste se perderam…Não tem pior desculpa que: “não tem porque eu não guardei” quando alguém pergunta sobre esses eventos…
Embora o comportamento padrão do arquivo seja circular, não quer dizer que deva ficar assim. Pensar que aquelas informações podem em algum momento, trazer algum valor (ou resolver algum problema), é razão suficiente para gastar espaço em disco armazenando-os.
Faz sentido armazenar os eventos, seja realizando backup dos arquivos (via Filesystem) ou através de soluções customizadas (uma possível solução foi proposta no decorrer deste post usando T-SQL, mas podia ser via Powershell também…) de modo que se mantenha uma história do servidor e seus eventos em algum momento poderão ser úteis.
Caso não seja identificada a necessidade de armazenar essas informações, uma medida mínima para prevenir “perda de informações” (entre aspas) é de aumentar o limite de errorlogs para um número razoável, conforme citado na parte 2 da série, onde o limite máximo de arquivos (99) é uma sugestão, embora pareça exagero. A ideia aqui é perder o menor número de eventos possíveis antes que o (re)cycle leve informações embora. Como análises de eventos ocorrem em períodos próximos, pode ser que a simples solução de ter mais arquivos de log já ajude na localização de eventos mais recentes e previna alguma falta de informação caso algum evento antigo precise ser consultado.
Informação do ERRORLOG
É possível tirar alguma inteligência a partir de arquivos “brutos” de log? Tecnicamente, muitos olham o errorlog ou em casos pontuais na ocorrência de algum problema ou por possuir postura pró-ativa, mas em geral, quando se olha o errorlog, se busca uma resposta, se busca informação. Se é interessante extrair informação, principalmente de algo que vem de graça no SQL Server, dentre várias opções, não se pode ignorar os errorlogs.
Farei algumas perguntas bem despretensiosas, mas antes de ler, pense em qualquer ambiente que você tenha:
- Quantos KILL’s ocorrem por dia neste servidor?
- Quantas operações de logins com falhas, em média, ocorrem por dia? São do mesmo login?
- Quais são os erros sérios que você descobriu quando um problema aconteceu e uma ou mais evidências estavam no ERRORLOG?
As perguntas possuem uma importância muito relativa: identificar quantidade de KILL’s em ambientes onde a prática é comum mesmo quando não deveria ser (até pelas próprias aplicações) não agrega tanto valor ter ciência desta informação, mas em uma ambiente de BI por exemplo onde o evento é mais raro, é uma informação que possui seu peso…
Quantidade de falhas de login é um número que flutua bastante, mas é interessante saber quais são os mais recorrentes e resolver na fonte o motivo do problema (Datasource com senha errada? Pela faixa de tempo é possuir concluir que seja a possível causa das requisições mal-sucedidas) até pra não sujar muito o errorlog com mensagens repetidas.
Eventos relevantes e/ou sérios: indisponibilidade, corrupções e falhas de backup, por exemplo, podem ser descobertos ou melhor investigados com o auxílio do ERRORLOG, e por isso, é interesante saber a quantidade e frequência que esses tipos de eventos (geralmente não desejados, acredito piamente, rs) acontecem, principalmente em servidores de produção.
Solução Universal para Armazenamento e Extração de Informações?
Sinceramente, não creio que exista algo assim. Cada ambiente possui uma particularidade e cada DBA também, então, pode ser que as informações do errorlog sejam lixo pra determinado ponto de vista pessoal, e luxo para outro, principalmente quando se trata de errorlogs pois não é um dos pontos de conversa mais populares e muito pouco se fala sobre (e isso foi um dos fortes motivos que me levou a escrever essa série de posts).
Mas, podemos elaborar algo que possa atender relativamente bem este propósito de de coleta, armazenagem e extração de informação. Em determinado cenário X, identifiquei uma boa oportunidade de fazer as duas coisas ao mesmo tempo: armazenar as informações do ERRORLOG e extrair informação útil dele.
Aproveitei para formular uma solução e gostaria de apresentar aqui no blog.
Sugestão de Solução: ERRORLOG CAPTURE
Basicamente: realiza a leitura de todos os errorlog de determinada instância com o uso da xp_readerrorlog em determinado período do tempo e joga em um determinado repositório de eventos (que seria uma tabela no SQL Server, que servirá como armazenamento). A ideia aqui é coletar o que realmente for necessário e evitar informação redundante.
A segunda ideia por trás da solução é, através de uma identificação rústica de caracteres e de baixa performance (graças ao operador LIKE) e até por esse motivo trata-se de uma carga que deve ser feita em horários estratégicos, identificar padrões de mensagem e com base nesse padrão classificar determinados eventos. Ao longo do tempo, é necessário identificar eventos que não foram tratados e que não se encaixam em nenhuma categoria e organizá-los manualmente.
Um bom exemplo: Quando a mensagem de um determinado evento conter a entrada ”%LOGIN FAILED% tenho certeza de que posso enquadrar a mensagem na categoria “LOGIN”. A ideia de classificar na mão não é eficiente mas traz a liberdade de criar categorias e criterizar de acordo com a necessidade e criatividade de cada um.
A outra ideia por trás do conceito de categorizar à mão livre, é que de acordo com o ambiente, posso classificar aquela categoria como importante, pouco importante e nada importante e usar essa informação posteriormente como critério de pesquisa.
Poderia então, por exemplo:
- Identificar a quantidade de eventos relacionados a erros graves no mês;
- Ver quantas mudanças de estado de bases ocorreram;
- Checar se tenho muitas entradas de logins inválidos, e através destas informações, tomar decisões de melhorias (algumas até preventivas) para o ambiente;
- Pesquisar mensagens de uma só categoria ou pesquisar mensagens de categorias que considero críticas (erros de backup, corrupções, etc) via query;
Vantagens:
- Conseguimos armazenar em um formato confortável (relacional, dentro do SQL Server) e que posteriormente pode ser backupeado ou expurgado conforme necessário.
- Conseguimos retirar informações e ter mais conhecimento de nossos servidores.
- Categorizar erros é uma ótima forma de gerenciar melhor tanta informação.
Desvantagens:
- Depender de uma proc fechada como a xp_readerrorlog traz uma série de limitações de performances e o código possui um comportamento não-desejado SQL Server 2014 (erros são gerados pela procedure e a leitura dinâmica dos errorlogs não é realizada conforme esperado).
- Performance não-escalável com o uso da xp_readerrorlog. Pode ser aprimorada com o uso de Powershell;
Eis o código da solução:
/* Errorlog completo */
/****************************************************************************************
Nome: ERRORLOG COLLECTOR
Descrição: Realiza a coleta de arquivos de errorlog. Essa versão é a prévia e contém trechos de puro debug.
***************************************************************************************/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
USE MASTER
GO
/* Cria tab. temporária que irá receber o resultado */
IF EXISTS (SELECT * FROM TEMPDB.sys.objects where name LIKE '%##ErrorlogTMP%')
BEGIN
DROP TABLE ##ErrorlogTMP;
END
CREATE TABLE ##ErrorlogTMP
(
[Servidor] VARCHAR(100),
[LogDate] DATETIME,
[ProcessInfo] VARCHAR(50),
[Text] VARCHAR(MAX),
[DataCarga] DATE
)
/* Efetivamente, é o repositório de eventos */
IF NOT EXISTS (SELECT * FROM sys.objects where name = 'Errorlog' and type = 'U')
BEGIN
PRINT '>> Criando tabela ERRORLOG!';
CREATE TABLE dbo.Errorlog
(
[Servidor] VARCHAR(100),
[LogDate] DATETIME,
[ProcessInfo] VARCHAR(50),
[Text] VARCHAR(MAX),
[DataCarga] DATE,
[Categoria] VARCHAR(60),
[Prioridade] TINYINT -- Aceita apenas dois valores possíveis
)
END
PRINT '>> Coletando informações dos logs';
/* Coleta número de arquivos e tamanho dos arquivos para iniciar contadores e tamanho dos arquivos */
DECLARE @NumArquivos AS SMALLINT
DECLARE @vEnumerrorlogs AS TABLE
(
Archives TINYINT,
[Date] DATETIME,
[Log File Size (Bytes)] BIGINT
)
INSERT INTO @vEnumerrorlogs (Archives,Date, [Log File Size (Bytes)])
EXEC XP_ENUMERRORLOGS
/* Inicia contador para o maior número de arquivo */
SET @NumArquivos = (SELECT MAX(Archives) FROM @vEnumerrorlogs)
-- Carrega as datas de intervalo e o timestamp de captura
DECLARE @Dias AS SMALLINT = 7
DECLARE @DataInicio AS DATETIME = GETDATE()-@dias
DECLARE @DataFim AS DATETIME = GETDATE()
DECLARE @DataCarga AS DATE = GETDATE()
/* Arquivo não pode execder limite de 500 MB --> (500*1024)*1024 */
WHILE @NumArquivos >= 0
BEGIN
IF (SELECT [Log File Size (Bytes)] FROM @vEnumerrorlogs WHERE Archives = @NumArquivos ) < 524288000 BEGIN INSERT INTO ##ErrorlogTMP (LogDate,ProcessInfo,Text) EXEC xp_readerrorlog @NumArquivos,1,N'',N'',@DataInicio,@DataFim PRINT ' > FILE #'+CAST(@NumArquivos AS VARCHAR(10))+' : '+CAST(@@ROWCOUNT AS VARCHAR(10));
SET @NumArquivos = @NumArquivos - 1
END
IF (SELECT [Log File Size (Bytes)] FROM @vEnumerrorlogs WHERE Archives = @NumArquivos ) >= 524288000
BEGIN
PRINT '> Arquivo maior que 500MB. Favor tratar o arquivo separadamente ';
SET @NumArquivos = @NumArquivos - 1
END
END
/* Atualiza a temporária com as informações */
UPDATE ##ErrorlogTMP
SET Servidor = @@servername,
DataCarga = GETDATE()
PRINT '> Linhas na tabela temporária: '+CAST(@@ROWCOUNT AS VARCHAR(10))
/* Insere apenas registros que não estejam no repositório. A unicidade do registro em tese seria Servidor - LogDate - Texto */
INSERT INTO dbo.ErrorLog (Servidor, LogDate, ProcessInfo,Text,DataCarga)
SELECT
T.Servidor,
T.LogDate,
T.ProcessInfo,
T.[Text],
T.DataCarga
FROM ##ErrorlogTMP T
LEFT JOIN ErrorLog E on E.Servidor = T.Servidor AND E.LogDate = T.LogDate AND E.Text = T.Text
WHERE E.Servidor IS NULL AND E.DataCarga IS NULL
PRINT 'Linhas inseridas na ERRORLOG: '+CAST(@@ROWCOUNT AS VARCHAR(10))
-- Atualiza apenas categoria
UPDATE ERRORLOG
SET [Categoria] =
CASE
/* AUDIT */
WHEN Text LIKE '%SQL Server Audit%' THEN 'AUDIT'
/* BROKER */
WHEN TEXT LIKE '%Check to ensure the conversation handle, service broker contract, and service specified in the event notification are active.%' THEN 'BROKER'
/* DBCC*/
WHEN Text LIKE 'DBCC TRACE%' THEN 'DBCC'
WHEN Text LIKE 'DBCC SHRINKFILE %' THEN 'DBCC'
WHEN TEXT LIKE 'DBCC execution completed. If DBCC printed error messages, contact your system administrator.' THEN 'DBCC'
WHEN TEXT LIKE 'DBCC execution completed. If DBCC printed error messages, contact your system administrator.' THEN 'DBCC'
WHEN TEXT LIKE 'DBCC CHECKDB%' AND TEXT LIKE '%EXECUTED BY %'THEN 'DBCC'
WHEN TEXT LIKE 'CHECKDB for database %' THEN 'DBCC:INFO'
/* Policy */
WHEN TEXT LIKE 'Policy%' AND TEXT LIKE '%has been violated%' THEN 'POLICY'
/* Database Mail */
WHEN TEXT LIKE 'Mail queued%' THEN 'DATABASE MAIL'
/* Trace */
WHEN Text LIKE '%SQL Trace%' THEN 'TRACE'
WHEN Text LIKE 'Trace ID %' THEN 'TRACE'
WHEN Text LIKE 'Launched startup procedure %' THEN 'TRACE'
/* Errorlog */
WHEN Text LIKE 'Attempting to cycle error log%' THEN 'ERRORLOG'
WHEN Text LIKE 'Logging SQL Server messages in file %' THEN 'ERRORLOG'
WHEN Text LIKE 'The error log has been reinitialized. See the previous log for older entries.' THEN 'ERRORLOG'
/* HA */
WHEN TEXT LIKE 'The Database Mirroring protocol transport is now listening for connections.' THEN 'MIRRORING'
WHEN TEXT LIKE 'Database mirroring has been enabled on this instance of SQL Server.' THEN 'MIRRORING'
WHEN TEXT LIKE 'The Database Mirroring protocol transport has stopped listening for connections.' THEN 'MIRRORING'
WHEN TEXT LIKE 'Database mirroring%' THEN 'MIRRORING'
/* LOGIN */
WHEN Text LIKE '%Login failed%' THEN 'LOGIN'
WHEN TEXT LIKE 'SSPI handshake failed with error code%' THEN 'LOGIN'
/* DTC */
WHEN TEXT LIKE '%SQL Server could not register with Microsoft Distributed Transaction Coordinator%' THEN 'DTC'
WHEN TEXT LIKE 'Import of Microsoft Distributed Transaction Coordinator%' THEN 'DTC'
WHEN TEXT LIKE 'The connection has been lost with Microsoft Distributed Transaction Coordinator (MS DTC)%' THEN 'DTC'
WHEN TEXT LIKE 'Attempting to recover in-doubt distributed transactions%' THEN 'DTC'
/* Arquivo */
WHEN Text LIKE '%There is not enough space on the disk%' THEN 'DISCO'
/* Transaction Log */
WHEN Text LIKE '%The transaction log for database%' THEN 'TLOG'
WHEN Text LIKE '%The log for database%' AND TEXT LIKE '%Check the event log for related error messages%' THEN 'TLOG'
WHEN TEXT LIKE '%has more than 1000 virtual log files which is excessive%' THEN 'TLOG'
WHEN TEXT LIKE '%because the log is out of space%' THEN 'TLOG'
/* CPU */
WHEN TEXT LIKE '% to be non-yielding on Scheduler %' THEN 'CPU'
/* AUTOGROWTH */
WHEN TEXT LIKE '%Autogrow of file %' THEN 'AUTOGROWTH'
WHEN TEXT LIKE '%DATA_FILE_AUTO_GROW%' THEN 'AUTOGROWTH'
/* Performance */
-- Eventos que impactam na performance do banco de dados em geral, pode envolver ou não recursos como disco, cpu, etc */
WHEN Text LIKE '%A significant part of sql server process memory has been paged out%' THEN 'PERFORMANCE'
WHEN Text LIKE '%cachestore flush%' THEN 'PERFORMANCE'
WHEN TEXT LIKE 'SQL Server has encountered %' and TEXT LIKE '%of I/O requests taking longer than %' THEN 'PERFORMANCE'
WHEN TEXT LIKE 'Cannot use Large Page Extensions: Failed to allocate%' THEN 'PERFORMANCE'
WHEN TEXT LIKE 'fcb::close-flush: Operating system error%' THEN 'PERFORMANCE'
WHEN TEXT LIKE '%There is insufficient system memory in resource pool%' THEN 'PERFORMANCE'
/* Backup */
WHEN TEXT LIKE 'BACKUP failed to complete the command BACKUP %' THEN 'BACKUP'
WHEN TEXT LIKE 'BackupDiskFile::CreateMedia: Backup device %' THEN 'BACKUP'
WHEN TEXT LIKE 'BackupIoRequest::ReportIoError: write failure on backup device%' THEN 'BACKUP'
WHEN TEXT LIKE 'BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device %' AND TEXT LIKE '%Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).' THEN 'BACKUP'
WHEN TEXT LIKE 'Cannot open backup device %' THEN 'BACKUP'
WHEN TEXT LIKE 'Database backed up%' THEN 'BACKUP'
WHEN TEXT LIKE 'BackupDiskFile::OpenMedia: Backup device%' THEN 'BACKUP'
/* Restore */
WHEN TEXT LIKE 'The database%' AND TEXT LIKE '%is marked RESTORING%' THEN 'RESTORE'
WHEN TEXT LIKE 'Log was restored%' THEN 'RESTORE'
WHEN TEXT LIKE 'Database was restored%' THEN 'RESTORE'
WHEN TEXT LIKE 'Log was restored%' THEN 'RESTORE'
/* Upgrade */
-- Scripts identificados durante o Script Upgrade Mode. Não necessariamente estão presentes apenas em upgrades de Service Packs
WHEN TEXT LIKE 'Create procedure sp_set_sqlagent_properties%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Setting object permissions%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Execution of PREINSTMSDB100.SQL complete' THEN 'UPGRADE'
WHEN TEXT LIKE 'MSDB format is%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Setting object permissions%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Connection Monitor status%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Starting Connection Monitor on server%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Connection Monitor status%' THEN 'UPGRADE'
WHEN TEXT LIKE 'MSDB format is%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Setting object permissions%' THEN 'UPGRADE'
WHEN TEXT LIKE '%is changing database script level entry%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Running SQL Server %'AND TEXT LIKE '% upgrade script%' THEN 'UPGRADE'
WHEN TEXT LIKE 'MSDB FORMAT is %' THEN 'UPGRADE'
WHEN TEXT LIKE 'msdb_upgrade_discovery starting' THEN 'UPGRADE'
WHEN TEXT LIKE 'Upgrading SQL Server Log Shipping...' THEN 'UPGRADE'
WHEN TEXT LIKE 'OBJECT %' AND TEXT LIKE '%does not exist, will not drop%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Adding user %' THEN 'UPGRADE'
WHEN TEXT LIKE 'Executing msdb.dbo.%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Definining default datatype mappings' THEN 'UPGRADE'
WHEN TEXT LIKE '%cannot be created because it already exists or you do not have permission%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Could not export certificate, trying again with random name. If this is a mkmastr build then this is an error.' THEN 'UPGRADE' /*QUESTIONÁVEL */
WHEN TEXT LIKE 'Check if %' AND TEXT LIKE '%exists%' THEN 'UPGRADE'
WHEN TEXT LIKE 'ALTER TABLE %' THEN 'UPGRADE'
WHEN TEXT LIKE 'DMF pre-upgrade %' THEN 'UPGRADE'
WHEN TEXT LIKE 'Starting execution of %' THEN 'UPGRADE'
WHEN TEXT LIKE 'Checking the size of MSDB...' THEN 'UPGRADE'
WHEN TEXT LIKE 'DC pre-upgrade steps...' THEN 'UPGRADE'
WHEN TEXT LIKE 'Check if Data collector config table exists...' THEN 'UPGRADE'
WHEN TEXT LIKE 'Data Collector state before upgrade%' THEN 'UPGRADE'
WHEN TEXT LIKE 'Deleting cached auto-generated T-SQL Data Collection packages from msdb...' THEN 'UPGRADE'
WHEN TEXT LIKE 'End of DC pre-upgrade steps.' THEN 'UPGRADE'
WHEN TEXT LIKE 'Starting DAC pre-upgrade steps ...' THEN 'UPGRADE'
WHEN TEXT LIKE 'Moving 2005 SSIS Data to 2008 tables' THEN 'UPGRADE'
WHEN TEXT LIKE 'Updating Query Activity Collector Type' THEN 'UPGRADE'
WHEN TEXT LIKE 'Updating Performance counters collector type' THEN 'UPGRADE'
WHEN TEXT LIKE 'Deleting collection set "Utility Information"...' THEN 'UPGRADE'
WHEN TEXT LIKE 'Starting execution of UPGRADE_UCP_CMDW_DISCOVERY.SQL' THEN 'UPGRADE'
WHEN TEXT LIKE 'User ''sa'' is changing database script level entry' THEN 'UPGRADE'
WHEN TEXT LIKE 'Skipping the execution of instmdw.sql.' THEN 'UPGRADE'
WHEN TEXT LIKE 'Upgrading SQL Server Log Shipping %' THEN 'UPGRADE'
WHEN TEXT = 'Upgraded SQL Server Log Shipping successfully.' THEN 'UPGRADE'
WHEN TEXT LIKE 'Execution of INSTMSDB.SQL complete' THEN 'UPGRADE'
WHEN TEXT LIKE 'Exporting ##MS_AgentSigningCertificate## to %' THEN 'UPGRADE'
WHEN TEXT LIKE 'Created #InstmsdbAgentCertPath with value %' THEN 'UPGRADE'
WHEN Text LIKE 'execution of UPGRADE_UCP_CMDW_DISCOVERY.SQL completed' THEN 'UPGRADE'
WHEN Text LIKE 'Upgrading publication settings and system objects in database%' THEN 'UPGRADE'
WHEN Text LIKE 'Upgrading subscription settings and system objects in database%' THEN 'UPGRADE'
WHEN Text LIKE 'Executing: %' THEN 'Upgrade' -- Verificar se só o upgrade causa essas mensagens (acho q sim)
-- Criação de objetos no geral são eventos de upgrade
WHEN Text LIKE 'Dropping extended database property%' THEN 'UPGRADE'
WHEN Text LIKE 'Creating extended database property' THEN 'UPGRADE'
WHEN Text LIKE 'Creating procedure [dbo].%' THEN 'UPGRADE'
WHEN Text LIKE 'Dropping procedure [dbo].%' THEN 'UPGRADE'
WHEN Text LIKE 'Creating view [dbo].%' THEN 'UPGRADE'
WHEN Text LIKE 'Dropping view [dbo].%' THEN 'UPGRADE'
WHEN Text LIKE 'Creating table [dbo].%' THEN 'UPGRADE'
WHEN Text LIKE 'Dropping table [dbo].%' THEN 'UPGRADE'
WHEN Text LIKE 'Creating function [dbo].%' THEN 'UPGRADE'
WHEN Text LIKE 'Dropping function [dbo].%' THEN 'UPGRADE'
WHEN Text LIKE 'Creating Collection Item%' THEN 'UPGRADE'
WHEN Text LIKE 'Dropping [dbo].%' THEN 'Upgrade'
WHEN Text LIKE 'Creating [dbo].%' THEN 'Upgrade'
WHEN Text LIKE 'Dropping view %' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating procedure %' AND TEXT LIKE '%...' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating view %' AND TEXT LIKE '%...' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating trigger %' AND TEXT LIKE '%...' THEN 'Upgrade'
WHEN TEXT LIKE 'Dropping MESSAGE%' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating MESSAGE%' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating sysmail%' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating sp_%' THEN 'Upgrade'
WHEN TEXT LIKE 'Dropping Database Mail MESSAGES, CONTRACTS, QUEUES AND SERVICES...' THEN 'Upgrade'
WHEN TEXT LIKE 'Dropping SERVICE %' THEN 'Upgrade'
WHEN TEXT LIKE 'Dropping QUEUE %' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating SERVICE %' THEN 'Upgrade'
WHEN TEXT LIKE 'DROPPING %' THEN 'Upgrade'
WHEN TEXT LIKE 'CREATING %' THEN 'Upgrade'
WHEN TEXT LIKE '%No foreign keys reference table%' THEN 'Upgrade'
WHEN TEXT LIKE 'Dropping signature%' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating SSIS folders...%' THEN 'Upgrade'
WHEN TEXT LIKE 'Create' AND TEXT LIKE '%role...' THEN 'Upgrade'
WHEN TEXT LIKE 'Create loginless user that has ownership of data collector agent-related securables...' THEN 'Upgrade'
WHEN TEXT LIKE 'Execution of InstDac.SQL complete' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating system Collection Sets..' THEN 'Upgrade'
WHEN TEXT LIKE 'Create dc_operator role...' THEN 'Upgrade'
WHEN TEXT LIKE 'Create dc_admin role...' THEN 'Upgrade'
WHEN TEXT LIKE 'Create dc_proxy role...' THEN 'Upgrade'
WHEN TEXT LIKE 'Updating Collection Item %' THEN 'Upgrade'
WHEN TEXT LIKE 'Configuration Option%' AND TEXT LIKE '%Run the RECONFIGURE statement to install.' THEN 'Upgrade'
WHEN TEXT LIKE 'Granting permissions to data collector roles...' THEN 'Upgrade'
WHEN TEXT LIKE 'Executing "drop PROCEDURE %' THEN 'Upgrade'
WHEN TEXT LIKE 'Executing "drop VIEW %' THEN 'Upgrade'
WHEN TEXT LIKE 'Executing "drop TRIGGER %' THEN 'Upgrade'
WHEN TEXT LIKE 'Executing "drop FUNCTION %' THEN 'Upgrade'
WHEN TEXT LIKE 'Signing sps ...' THEN 'Upgrade'
WHEN TEXT LIKE 'Parameter ''@schema_collection'' cannot be NULL or an empty string.' THEN 'Upgrade'
WHEN TEXT LIKE 'processing sp:%' THEN 'Upgrade'
WHEN TEXT LIKE 'Done dropping all DMF and Shared Registered Server procedures' THEN 'Upgrade'
WHEN TEXT LIKE 'Dropping existing Agent certificate ...' THEN 'Upgrade'
WHEN TEXT LIKE 'Common language runtime (CLR) functionality initialized using CLR version%' THEN 'Upgrade'
WHEN TEXT LIKE 'Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.' THEN 'Upgrade'
WHEN TEXT LIKE 'Dropping signature from: %' THEN 'Upgrade'
WHEN TEXT LIKE 'The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity.' THEN 'Upgrade'
WHEN TEXT LIKE 'Saved upgrade script status successfully.' THEN 'Upgrade'
WHEN TEXT LIKE 'Saving upgrade script status to %' THEN 'Upgrade'
WHEN TEXT LIKE '%is upgrading script%' THEN 'Upgrade'
WHEN TEXT LIKE 'REVOKE ALL ON %' THEN 'Upgrade'
WHEN TEXT LIKE 'Loading saved syssubsystems related user info...' THEN 'Upgrade'
WHEN TEXT LIKE 'Revoke any permission to public role...' THEN 'Upgrade'
WHEN TEXT LIKE 'Populate syssubsystem table...' THEN 'Upgrade'
WHEN TEXT LIKE 'Attempting to load library %' THEN 'Configuração'
WHEN TEXT LIKE 'Logshipping enabled successfully' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating or updating Collection Types...' THEN 'Upgrade'
WHEN TEXT LIKE 'Updating TSQL Query collector type' THEN 'Upgrade'
WHEN TEXT LIKE 'Creating data collector schedules' THEN 'Upgrade'
WHEN TEXT LIKE 'Hook up the activation procedure to the queue...' THEN 'Upgrade'
WHEN TEXT LIKE 'Installing out of the box Collector objects' THEN 'Upgrade'
WHEN TEXT LIKE 'Executing:%' THEN 'Upgrade'
WHEN TEXT LIKE 'Succesfully signed sps' THEN 'Upgrade'
WHEN TEXT LIKE 'Successfully granted execute permission in master to %' THEN 'Upgrade'
WHEN TEXT LIKE 'Updating system Collection Set %' THEN 'Upgrade'
WHEN TEXT LIKE 'sp_vupgrade_replication executed successfully' THEN 'Upgrade'
WHEN TEXT LIKE 'The Utility MDW does not exist on this instance.' THEN 'Upgrade'
WHEN TEXT LIKE 'Checking if Data collector was enabled before upgrade...' THEN 'Upgrade'
WHEN TEXT LIKE 'Execution of POSTINSTMSDB100.SQL complete' THEN 'Upgrade'
WHEN TEXT LIKE 'Completed upgrade of Database Mail related objects...' THEN 'Upgrade'
WHEN TEXT LIKE 'Executing replication upgrade scripts.' THEN 'Upgrade'
WHEN TEXT LIKE 'Checking if Data collector was enabled before upgrade...' THEN 'Upgrade'
WHEN TEXT LIKE 'Execution of POSTINSTMSDB100.SQL complete' THEN 'Upgrade'
WHEN TEXT LIKE 'Completed upgrade of Database Mail related objects...' THEN 'Upgrade'
WHEN TEXT LIKE 'Executing sp_vupgrade_replication.' THEN 'Upgrade'
WHEN TEXT = 'Moving package folders...' THEN 'Upgrade'
WHEN TEXT = 'Moving packages...' THEN 'Upgrade'
WHEN TEXT = 'Moving logs...' THEN 'Upgrade'
WHEN TEXT = 'Using ''xplog70.dll'' version %' THEN 'Upgrade'
WHEN TEXT = 'Performing replication job security meta-data upgrades...' THEN 'Upgrade'
WHEN TEXT = 'Mapping SSIS yukon roles to katmai roles...' THEN 'Upgrade'
/* Configuração e Inicialização */
WHEN TEXT LIKE 'The certificate%' AND TEXT LIKE '%was sucessfully%' THEN 'CONFIG'
WHEN TEXT LIKE 'Unsafe assembly%' THEN 'CONFIG'
WHEN TEXT LIKE 'Dedicated admin connection support was established for listening locally on port%' THEN 'CONFIG'
WHEN Text LIKE '%This instance of SQL Server has been using a process ID of%' THEN 'CONFIG'
WHEN Text LIKE '%Microsoft SQL Server%' THEN 'CONFIG'
WHEN Text LIKE '(c) Microsoft Corporation.' THEN 'CONFIG'
WHEN Text LIKE 'All rights reserved%' THEN 'CONFIG'
WHEN Text LIKE 'Server process ID is%' THEN 'CONFIG'
WHEN Text LIKE 'System Manufacturer%' THEN 'CONFIG'
WHEN Text LIKE 'Authentication mode is%' THEN 'CONFIG'
WHEN TEXT LIKE 'Using %' AND TEXT LIKE '%version%' THEN 'CONFIG'
WHEN Text LIKE 'Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.' THEN 'CONFIG'
WHEN Text LIKE 'Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.' THEN 'CONFIG'
WHEN Text LIKE 'Configuration option ''Agent XPs'' changed from 0 to 1. Run the RECONFIGURE statement to install.' THEN 'CONFIG'
WHEN Text LIKE 'FILESTREAM: effective level = 0, configured level = 0, file system access share name%' THEN 'CONFIG'
WHEN Text LIKE 'Attempting to load library ''xpsqlbot.dll'' into memory. This is an informational message only. No user action is required.' THEN 'CONFIG'
WHEN Text LIKE 'Using ''xpsqlbot.dll'' version%' THEN 'CONFIG'
WHEN Text LIKE 'Mail queued.' THEN 'CONFIG - Database Mail'
WHEN TEXT LIKE '%The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN)%' THEN 'CONFIG'
WHEN TEXT LIKE 'This instance of SQL Server last reported using a process ID of %' THEN 'CONFIG'
WHEN TEXT LIKE 'SQL Server is starting at normal priority base %' THEN 'CONFIG'
WHEN TEXT LIKE 'Detected %' AND TEXT LIKE '%CPUs%' THEN 'CONFIG'
WHEN TEXT LIKE 'Large Page Extensions enabled.' THEN 'CONFIG'
WHEN TEXT LIKE 'Large Page Granularity %' THEN 'CONFIG'
WHEN TEXT LIKE 'Large Page Allocated:%' THEN 'CONFIG'
WHEN TEXT LIKE 'Large Page Allocated:%' THEN 'CONFIG'
WHEN TEXT LIKE 'Using locked pages for buffer pool.' THEN 'CONFIG'
WHEN TEXT LIKE 'Using dynamic lock allocation. Initial allocation of %' THEN 'CONFIG'
WHEN TEXT LIKE 'Lock partitioning is enabled. This is an informational message only. No user action is required.' THEN 'CONFIG'
WHEN TEXT LIKE 'Node configuration: node %' THEN 'CONFIG'
WHEN TEXT LIKE 'Resource governor reconfiguration succeeded.' THEN 'CONFIG'
WHEN TEXT LIKE 'The NETBIOS name of the local node that is running the server is %' THEN 'CONFIG'
WHEN TEXT LIKE 'The resource database build version is %' THEN 'CONFIG'
WHEN TEXT LIKE 'The Service Broker protocol transport is disabled or not configured.' THEN 'CONFIG'
WHEN TEXT LIKE 'The Database Mirroring protocol transport is disabled or not configured.' THEN 'CONFIG'
WHEN TEXT LIKE 'Service Broker manager has started.' THEN 'CONFIG'
WHEN TEXT LIKE 'A self-generated certificate was successfully loaded for encryption.' THEN 'CONFIG'
WHEN TEXT LIKE 'A new instance of the full-text filter daemon host process has been successfully started.' THEN 'CONFIG'
WHEN TEXT LIKE 'Server is listening on %' THEN 'CONFIG - Rede'
WHEN TEXT LIKE 'Server named pipe provider is ready to accept connection on %' THEN 'CONFIG'
WHEN TEXT LIKE 'Dedicated admin connection support was established for listening remotely on port %' THEN 'CONFIG - Rede (DAC)'
WHEN TEXT LIKE 'Clearing tempdb database.' THEN 'CONFIG'
WHEN TEXT LIKE 'Large Page Granularity: %' THEN 'CONFIG'
WHEN TEXT LIKE 'Upgrading Database Mail related objects...' THEN 'CONFIG'
WHEN TEXT LIKE 'Server name is %' THEN 'CONFIG'
WHEN TEXT LIKE 'Using ''xprepl.dll%' THEN 'CONFIG'
WHEN TEXT LIKE 'Large Page Granularity: %' THEN 'CONFIG'
WHEN TEXT LIKE 'Upgrading Database Mail related objects...' THEN 'CONFIG'
WHEN TEXT LIKE 'Service Broker manager has shut down.' THEN 'CONFIG'
WHEN TEXT LIKE '%to execute extended stored procedure%' THEN 'CONFIG'
WHEN TEXT LIKE 'Server local connection provider is ready to accept connection on%' THEN 'CONFIG'
WHEN TEXT LIKE 'Informational: No full-text supported languages found.' THEN 'CONFIG'
WHEN TEXT LIKE 'Detected' and TEXT LIKE '%CPUs%' THEN 'CONFIG'
WHEN TEXT LIKE 'SQL Server is now ready for client connections.%' THEN 'CONFIG'
WHEN TEXT LIKE '%Registry startup parameters%' THEN 'CONFIG'
WHEN TEXT LIKE '%SPN%' THEN 'CONFIG'
WHEN TEXT LIKE 'AppDomain %' AND TEXT LIKE '%unloaded%' THEN 'CONFIG'
WHEN TEXT LIKE '%was successfully loaded for encryption%' THEN 'CONFIG'
/* Configurações de base */
WHEN TEXT LIKE 'Setting database option %' THEN 'CONFIG:DATABASE'
-- Inicialização
WHEN TEXT LIKE 'Starting up database %' THEN 'INIT'
WHEN TEXT LIKE 'Recovery is complete. This is an informational message only. No user action is required.' THEN 'INIT'
WHEN TEXT LIKE '%Recovery completed for database%' THEN 'INIT'
WHEN TEXT LIKE 'SQL Server is terminating in response to a ''stop'' request from Service Control Manager. This is an informational message only. No user action is required.' THEN 'INIT'
WHEN TEXT LIKE '%transactions rolled forward in database%' THEN 'INIT'
WHEN TEXT LIKE '%transactions rolled back in database%' THEN 'INIT'
WHEN TEXT LIKE 'Recovery is writing a checkpoint in%' THEN 'INIT'
/* Disponibilidade */
WHEN TEXT LIKE '%Recovery of database%' THEN 'DISPONIBILIDADE'
WHEN TEXT LIKE 'SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. %' THEN 'Disponibilidade'
WHEN TEXT LIKE 'SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.%' THEN 'Disponibilidade'
WHEN TEXT LIKE 'SQL Server is now ready for client connections. This is an informational message; no user action is required.' THEN 'Disponibilidade'
WHEN TEXT LIKE 'SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.' THEN 'Disponibilidade'
WHEN TEXT LIKE 'SQL Server cannot accept new connections %' THEN 'DISPONIBILIDADE'
WHEN TEXT LIKE 'Cannot open database %' AND TEXT LIKE '% The upgrade of replication security%' THEN 'DISPONIBILIDADE'
WHEN TEXT LIKE 'Restore is complete on database%' THEN 'DISPONIBILIDADE'
/* ERROS & WARNINGS: Categorização temporária */
WHEN Text LIKE 'Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.' THEN 'TRAN:TSQL'
WHEN TEXT LIKE 'Database name ''tempdb'' ignored, referencing object in tempdb.' THEN 'TRAN:TSQL'
WHEN Text LIKE '%was killed by hostname%' THEN 'TRAN:KILL'
WHEN TEXT LIKE 'The current event was not reported to the Windows Events log%' THEN 'WINDOWS'
--WHEN TEXT LIKE '%index restored for %' THEN 'Corrupçao*'
WHEN TEXT LIKE 'The client was unable to reuse a session with %' AND TEXT LIKE '%which had been reset for connection pooling%' THEN 'SESSAO'
WHEN TEXT LIKE '.NET Framework runtime has been stopped.' THEN 'WINDOWS***'
WHEN TEXT LIKE 'Warning! The maximum key length is 900 bytes. The index %' THEN 'INDEX'
WHEN TEXT LIKE '%dedicated administrator connections already exists%' THEN 'DAC'
WHEN TEXT LIKE 'SQL Server blocked access to procedure%' THEN 'ERRO:SURFACE'
--WHEN TEXT LIKE 'AppDomain %' THEN 'APPDOMAIN***'
WHEN TEXT LIKE 'A fatal error occurred while reading the input stream from the network. The session will be terminated%' THEN 'ERRO:TRACE'
WHEN TEXT LIKE 'Reverting database%' THEN 'INFO:SNAPSHOT'
WHEN TEXT LIKE '%Operating system error 112%' THEN 'ERRO:ARQUIVO'
WHEN TEXT LIKE '%and has been restored. This user is required for SQL Server operation%' THEN 'WARNING:PERMISSIONAMENTO'
WHEN TEXT LIKE '%Failed allocate pages:%' THEN 'ERRO:ALOCACAO'
WHEN TEXT LIKE '%The system catalog was updated directly in%' THEN 'WARNING:ATUALIZACAO'
WHEN TEXT LIKE '%Warning: Fatal error 8510 occurred at %' THEN 'ERRO:?'
/* RESOURCE GOVERNOR */
WHEN TEXT LIKE '%Failed to run resource governor classifier user-defined function%' THEN '%ERRO:RESOURCE GOVERNOR%'
-- Dummy: Linhas vazias ou que são continuação de outro evento.
WHEN TEXT LIKE '%----%' THEN 'DUMMY'
WHEN TEXT LIKE 'Error:%' AND TEXT LIKE '%Severity:%' AND TEXT LIKE '%State: %' THEN 'DUMMY'
/* Lixo: Futuramente, tratar mensagens de erro */
WHEN TEXT LIKE '' THEN ''
ELSE '-'
END
PRINT 'Linhas atualizadas: '+CAST(@@ROWCOUNT AS VARCHAR(10))
/* Atualização das prioridades. */
UPDATE ERRORLOG
SET PRIORIDADE =
CASE
WHEN CATEGORIA IN ('DISCO','TLOG','PERFORMANCE','CPU') THEN 1
WHEN CATEGORIA IN ('INIT','AUTOGROWTH','DTC','LOGIN','TRACE','AUDIT','DBCC') THEN 2
WHEN CATEGORIA IN ('CONFIG','UPGRADE','RESTORE','MIRRORING') THEN 3
ELSE 0
END
PRINT 'Linhas com prioridades atualizadas: '+CAST(@@ROWCOUNT AS VARCHAR(10))
DECLARE @NumLinhas VARCHAR(10) = (SELECT CAST(COUNT(1) AS VARCHAR(10)) FROM ERRORLOG)
PRINT 'Debug: Quantidade de linhas da ERRORLOG: '+@NumLinhas
Você possui alguma solução em seu ambiente para armazenar e se aproveiter melhor dos errorlogs?
Críticas, sugestões? Comente 🙂
—
Leia também: Controlando o crescimento do ERRORLOG http://renatomsiqueira.com.br/controlando-o-crescimento-do-errorlog/