Monitorando os eventos de crescimento automático de espaço (Autogrowth) em databases no SQL Server

Olá leitores,
Boa tarde!

Neste post vou demonstrar como monitorar o crescimento automático de espaço de um database (autogrowth) no SQL Server, de modo que seja possível identificar o usuário que causou o crescimento e quando os eventos de crescimento automático ocorreram no database.

A obtenção desses resultados é possível graças a função de sistema ::fn_trace_gettable, que permite consultar os eventos do trace que é executado por padrão em todas as instâncias SQL Server.

Criando um banco de teste

Antes de testarmos a query que nos mostra os eventos de AutoGrowth do database, vamos criar um banco de testes, com as opções padrão do SQL Server (tamanho inicial 5 MB e autogrowth a cada 1 MB) e inserir alguns registros para forçar que acaba o espaço e o banco tenha que crescer automaticamente.

CREATE DATABASE [AutoGrowth] 
GO

ALTER DATABASE [AutoGrowth] SET RECOVERY FULL
GO

USE [AutoGrowth] 
GO

IF (OBJECT_ID('dbo.Teste') IS NOT NULL) DROP TABLE dbo.Teste
CREATE TABLE dbo.Teste (
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    Nome CHAR(8000),
    Profissao VARCHAR(8000)
)
GO


DECLARE @Contador INT = 1

WHILE (@Contador < 100000)
BEGIN

    INSERT INTO dbo.Teste(Nome, Profissao)
    VALUES ('Dirceu', 'DBA')

    SET @Contador = @Contador + 1

END

Seu database deve ter agora 788 MB e ter realizado o crescimento automático de espaço centenas de vezes. Vamos agora descobrir quando foram esses eventos e quem causou o aumento de espaço.

Para ficar mais legal o teste, sugiro abrir duas conexões com usuários diferentes e inserir os dados ao mesmo tempo.

Identificando os tipos de eventos do trace

Utilizando a query abaixo, podemos identificar rapidamente todos os EventClass que podemos utilizar na função ::fn_trace_gettable:

DECLARE @id INT = ( SELECT id FROM sys.traces WHERE is_default = 1 )

SELECT DISTINCT
    eventid,
    name
FROM
    fn_trace_geteventinfo(@id) A
    JOIN sys.trace_events B ON A.eventid = B.trace_event_id  

Verificando os eventos de crescimento

Agora que a nossa base de testes está feita e os dados já foram inseridos, vamos analisar os eventos de AutoGrowth do database através da query abaixo:

DECLARE 
    @Ds_Database sysname = 'AutoGrowth',
    @Ds_Arquivo_Trace VARCHAR(500) = (SELECT [path] FROM sys.traces WHERE is_default = 1)
    
DECLARE
    @Index INT = PATINDEX('%\%', REVERSE(@Ds_Arquivo_Trace))

DECLARE
    @Nm_Arquivo_Trace VARCHAR(500) = LEFT(@Ds_Arquivo_Trace, LEN(@Ds_Arquivo_Trace) - @Index) + '\log.trc'


SELECT
    A.DatabaseName,
    A.[Filename],
    ( A.Duration / 1000 ) AS 'Duration_ms',
    A.StartTime,
    A.EndTime,
    ( A.IntegerData * 8.0 / 1024 ) AS 'GrowthSize_MB',
    A.ApplicationName,
    A.HostName,
    A.LoginName
FROM
    ::fn_trace_gettable(@Nm_Arquivo_Trace, DEFAULT)		A
    LEFT JOIN sys.databases					B	ON ( B.name = @Ds_Database )
WHERE
    A.EventClass >= 92
    AND A.EventClass <= 95
    AND A.ServerName = @@servername 
    AND A.DatabaseName = @Ds_Database 
    AND B.create_date < EndTime
ORDER BY
    A.StartTime DESC

Com isso conseguimos analisar qual o database que sofreu o evento de autogrowth, qual o arquivo específico, quanto tempo o servidor demorou pra realizar o aumento do espaço, quanto de espaço foi aumentado, qual o software que está processando a query que causou o aumento do espaço, hostname e login do usuário que está executando essa query.

É isso aí, pessoal!
Espero que esta dica ajude vocês de alguma forma.

Abraços e até o próximo post!