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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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:
|
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) 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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!
Portuguese 
English
Show de bola, bom sou novo aqui no seu blog, ja dei uma fuçada e tem um otimo conteudo, demais.
Vlw