Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)

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:

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:

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:

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:

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:

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:

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.

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.

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

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).

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)

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!