Auto-Growth events are occurring when the data/log file is trying to expand size due to out of space.The amount that grows, depends on the file growth option provided for the database. Recently, in one of the project, it has been observed that the Auto growth event occurred 4 times for one of data file and a delay in response time.

Whats the problem with Auto Growth?

Auto Growth can not be stopped completely, however, a careful configuration of Growth option can control the number of Auto Growth events.The problem with Auto Growth is that during the growth event, the database would be halted until the growth event has completed. For a higher size, it can take more time there by a huge delay in processing for the request and in turn several performance issues.In addition, when the space is allocating, it may not be a contiguous space resulting a physical fragmentation of the database file on the disk. If the number of Auto Growth is high, then there will be more physical fragmentation.

How do you find the Auto Growth events?

This blog post is neither intended nor explained the cause of the growth or its resolution.Lets us look at it later. Here, We would see some of common methods to identify the Auto Growth events in SQL Server.

–Using default trace

Default trace is a very powerful way of capturing issues in SQL Server. Some of common EventClasses and its description is given below.

/*

EventClass Description
********************************************************
18 -Audit Server Starts And Stops
20 -Audit Login Failed
22 -Error log
46 -Object:Created
47 -Object:Deleted
55 -Hash Warning
69 -Sort Warnings
79 -Missing Column Statistics
80 -Missing Join Predicate
81 -Server Memory Change
92 -Data File Auto-Grow
93 -Log File Auto-Grow
116 -Audit DBCC Event
164 -Object:Altered
175 -Audit Server Alter Trace Event
*/

Now, lets look at the way to capture Auto Growth Event from trace as below.

DECLARE @filename NVARCHAR(1000);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

--To find the filename without rollover number
SET @filename = REVERSE(@filename);
SET @filename = REVERSE(SUBSTRING(@filename,CHARINDEX('_',@filename)+1,LEN(@filename))) 
					+ REVERSE(SUBSTRING(@filename,1,CHARINDEX('.',@filename)));

-- Get the details
SELECT 
  Trace.StartTime, Trace.EndTime,TraceEvent.name AS EventName,Trace.DatabaseName  
,Trace.Filename,Trace.EventClass,(Trace.IntegerData*8)/1024.0 AS GrowthMB 
,(Trace.duration/1000)AS DurationInMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS Trace 
INNER JOIN sys.trace_events AS TraceEvent ON Trace.EventClass = TraceEvent.trace_event_id  
WHERE (Trace.EventClass = 92  
    OR Trace.EventClass = 93) 
ORDER BY Trace.StartTime

–Using SQL Error log

SQL Server will provide the Auto Growth events in the Error log as below snapshot. The errorlog information contains the file name and database with the time taken to process the Auto Growth. Note, these are the events 5144 & 5145 logged only when autogrowth takes longer time or times out.

–Using Live monitoring query

Using the script in the post, you would be able to see the wait types like LATCH_EX/PREEMPTIVE_OS_WRITEFILEGATHER occurring in the server along with the resource description as FGCB_ADD_REMOVE(A prominent type of Latch – File Group Control Block Add/Remove), which is an indication for Auto Growth Event.

These are my favorite methods to identify the Auto Growth events. If you have any other ways, I would like to request you to share the information here.