Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

Using the standard SQL Server trace to audit events (fn_trace_gettable)

Post Views 5,196 views
Esse post é a parte 6 de 21 da série Security and Auditing
Reading time 5 minutes

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:

SQL Server - 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:

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:

SQL Server - fn_trace_gettable EventClass

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:

Desativando o Trace Padrão

Para desativar o trace padrão do SQL Server é tão simples quanto ativar. Basta executar a sp_configure:

Identificando os eventos

Uma forma simples de identificar os eventos que ocorreram na instância é executando a query abaixo:

SQL Server - Trace Events Ocurred

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.

SQL Server - Traces Autogrowth

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.

SQL Server - Traces Shrink

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

SQL Server - Trace DBCC

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

SQL Server - Trace Backup

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)

SQL Server - Traces Restore

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!