Olá pessoal,
Boa tarde!
Neste post, vou demonstrar como se obter informações importantes sobre vários eventos que ocorrem na instância SQL Server sem precisar ativar nenhum controle ou auditoria, apenas lendo as informações já coletadas pelo trace padrão do SQL Server.
Embora eu já tenha falado sobre isso em alguns outros posts, resolvi fechar esse assunto e fazer um post com mais vários exemplos de utilização.
O trace padrão do SQL coleta 34 tipos diferentes de eventos, que podem ser gerados manualmente por ferramentas como o SQL Server Profiler ou com queries Transact-SQL.
Os exemplos abaixo mostram como pegar informações de diferentes tipos de eventos, como Shrink, AutoGrow, Atualização de Estatísticas Automática, Backup/Restore, DBCC, Ausência de Estatísticas de Colunas, dentro outros.
Um importante ponto que se deve observar, é que esse trace padrão do SQL Server é executado em background por padrão, coletando apenas alguns eventos e por isso, gera um impacto mínimo na instância SQL Server.
Ao ativar um trace utilizando o SQL Server Profiler, que exibe os resultados em tempo real pela interface, você pode acabar gerando impactos como degradação de performance no ambiente, principalmente se não filtrar muito bem apenas os eventos que deseja observar e quais critérios serão definidos para identificar as sessões que farão parte do Trace.
Trace padrão do SQL Server
Normalmente, temos executando em nossa instância SQL Server apenas o trace padrão, que é habilitado por padrão, embora em alguns ambientes você possa encontrar mais de um trace executando, caso algum DBA crie um trace personalizado.
Listando os traces ativos na instância
Para listar os traces ativos na instância, você pode utilizar a query abaixo:
|
1 |
SELECT * FROM sys.traces |
Identificando o trace padrão
Embora o trace padrão geralmente seja o trace com o ID = 1, nem sempre isso ocorre. Por isso, recomendo utilizar a query abaixo para identificar o trace padrão:
|
1 |
SELECT * FROM sys.traces WHERE is_default = 1 |
Listando os eventos do trace padrão
Como citado acima, o trace padrão do SQL Server coleta 34 eventos diferentes. Para identificar a lista completa de eventos, basta executar essa query:
|
1 2 3 4 5 6 7 8 |
DECLARE @id INT = ( SELECT id FROM sys.traces WHERE is_default = 1 ) SELECT DISTINCT eventid, name FROM fn_trace_geteventinfo(@id) EI JOIN sys.trace_events TE ON EI.eventid = TE.trace_event_id |
Ativando o Trace Padrão (Já vem habilitado após a instalação)
Para ativar o trace padrão do SQL Server é bem simples, basta executar a sp_configure:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'default trace enabled', 1; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE; GO |
Desativando o Trace Padrão
Para desativar o trace padrão do SQL Server é tão simples quanto ativar. Basta executar a sp_configure:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'default trace enabled', 0; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE; GO |
Identificando os eventos
Uma forma simples de identificar os eventos que ocorreram na instância é executando a query abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT B.trace_event_id, B.name AS EventName, A.DatabaseName, A.ApplicationName, A.LoginName, COUNT(*) AS Quantity FROM sys.fn_trace_gettable(@path, DEFAULT) A INNER JOIN sys.trace_events B ON A.EventClass = B.trace_event_id GROUP BY B.trace_event_id, B.name, A.DatabaseName, A.ApplicationName, A.LoginName ORDER BY B.name, A.DatabaseName, A.ApplicationName, A.LoginName |
Identificando eventos de Autogrowth
Um dos eventos mais importantes que podemos observar com o trace padrão, são os eventos de Autogrowth, que ocorrem quando essa opção está habilitada no database e ele atinge o tamanho máximo, necessitando de alocar mais espaço no disco.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT DatabaseName, [FileName], CASE EventClass WHEN 92 THEN 'Data File Auto Grow' WHEN 93 THEN 'Log File Auto Grow' END AS EventClass, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 92, 93 ) ORDER BY StartTime DESC |
Identificando eventos de Shrink de Disco
Outro evento muito legal de ser monitorar, é o evento que Shrink de disco, que ocorre quando o database está alocando mais espaço que está utilizando e o DBA realoca o espaço alocado, resultando em espaço livre no filesystem.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 116 ) AND TextData LIKE 'DBCC%SHRINK%' ORDER BY StartTime DESC |
Identificando quando comandos DBCC foram executados na instância
Auditoria bem útil para identificar quem executou ou quando um comando DBCC foi executado na instância
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 116 ) ORDER BY StartTime DESC |
Identificando quando os backups foram realizados
Outra auditoria bem interessante para o DBA, que possibilita identificar a data e quem rodou os backups na instância (que também pode ser consultado na tabela msdb.dbo.backupset).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT DatabaseName, TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 115 ) AND EventSubClass = 1 ORDER BY StartTime DESC |
Identificando quando os backups foram restaurados
Auditoria bem interessante que permite identificar a data e qual usuário realizou um restore na instância (que também pode ser consultando nas tabelas msdb.dbo.restorehistory, msdb.dbo.restorefile e msdb.dbo.restorefilegroup)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 115 ) AND EventSubClass = 2 ORDER BY StartTime DESC |
Esses são só alguns exemplos do que o trace padrão nos retorna. Existe a extensa lista de eventos pra vocês explorarem!
Boa sorte, abraço e até o próximo post!
Portuguese 
English
Bom dia Dirceu! estou fazendo um teste, gravando o trace direto da ferramenta Profiler, e também gravando o trace do arquivo para a tabela usando o fn_trace_gettable. Usando a função, não estou conseguindo gravar o Rownumber. Sabe me dizer como gravar esse campo? percebi que há outros campos que a função não grava, mas estes não são importantes para mim, no momento.
Obrigado
Cássio, não me lembro de ter visto isso.. Você já conseguiu resolver isso?
Se não, me fala que eu tento simular esse cenário e te falo
Dirceu, parabéns pelo post. Me ajudou muito e abriu minha visão para as possibilidades de auditoria no BD.