Hello readers,
Good afternoon!
In this post I will demonstrate how to monitor the autogrowth growth of a database in SQL Server so that it is possible to identify the user who caused the growth and when the autogrowth events occurred in the database.
These results are possible thanks to the system function. :: fn_trace_gettable, which allows you to query trace events that run by default on all SQL Server instances.
Creating a test bank
Before we test the query that shows us the database's AutoGrowth events, let's create a test bank, with the default SQL Server options (initial size 5 MB and autogrowth every 1 MB) and insert some records to force the end to run. space and the bank has to grow automatically.
|
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 |
Your database should now be 788 MB and have autospace hundreds of times. Let's now find out when these events were and who caused the space increase.
To make the test cooler, I suggest opening two connections with different users and entering the data at the same time.
Identifying trace event types
Using the query below, we can quickly identify all EventClass that we can use in the :: fn_trace_gettable function:
|
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 |
Checking Growth Events
Now that our test base is done and the data has been entered, let's look at the database's AutoGrowth events using the query below:
|
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 |
With this we can analyze which database suffered the autogrowth event, which specific file, how long the server took to increase the space, how much space was increased, which software is processing the query that caused the increase of the space, hostname, and login of the user running this query.
That's it folks!
I hope this tip helps you in some way.
Hugs and until the next post!
Ball show, good I'm new here on your blog, already took a poke and has great content, awesome.
Vlw