Auto File Growth: Track the growth events Using Extended Events in SQL Server

This is a continued blog post for Auto File Growth – Different ways to track the growth events to answer one of the comments.

The author of the comment (Vinuraj R) was looking for a way to capture the Growth events through Extended Events. Let me begin with a Thank you note to the author talking about Extended Events. Extended Events are powerful ways to capture most of the metrics from the SQL Server 2008 own wards.

Here, We are going to see a method to track/cpature the growth of (Data/Log)files using Extended Events.

First of all, we are going to create an EVENT SESSION on the Server to capture the database_file_size_change Event as below: (You may also be able to provide a filter to capture information for any specific database, if required.)


-- Create event
CREATE EVENT SESSION [DB_file_size_changed] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
    ACTION
   (sqlserver.client_app_name,sqlserver.client_hostname, sqlserver.database_id,sqlserver.session_id,
   sqlserver.session_nt_username,sqlserver.username) 
   /* You can filter the database that needs to be monitored for the auto growth, for which you need to pass the database id for the filter*/
   --WHERE ([database_id]=()) 
   ) 
ADD TARGET package0.event_file
(SET filename=N'C:\temp\DB_file_size_changed.xel')
WITH (MAX_DISPATCH_LATENCY=1 SECONDS)

Once , Event Session has been created, We need to START the EVENT SESSION as below:Please note that you may need CONTROL SERVER permission to Alter/Drop the Event Session.


ALTER EVENT SESSION DB_File_size_Changed 
ON SERVER STATE = START -- START/STOP to start and stop the event session

Once the Event session is started, events will be capturing the Growth information in the file and we would be able to see those information using the below script:


USE [master];
GO
SELECT
        Case when file_type = 'Data file' Then 'Data File Grow' Else File_Type End AS [Event Name]
	   , database_name AS DatabaseName
	   , file_names
	   , size_change_kb
	   , duration
       , client_app_name AS Client_Application
	   , client_hostname
       , session_id AS SessionID
	   , Is_Automatic 
       
FROM (
       SELECT
           n.value ('(data[@name="size_change_kb"]/value)[1]', 'int') AS size_change_kb
           , n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
           , n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
           , n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
           , n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
           , n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
		   , n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS Client_HostName
		   , n.value ('(data[@name="file_name"]/value)[1]','nvarchar(50)') AS file_names
		   , n.value ('(data[@name="is_automatic"]/value)[1]','nvarchar(50)') AS Is_Automatic
           
       FROM 
           (   SELECT CAST(event_data AS XML) AS event_data
               FROM sys.fn_xe_file_target_read_file(
                   N'C:\temp\DB_file_size_changed*.xel',
                   NULL,
                   NULL,
                   NULL)
           ) AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
ORDER BY database_name

Here is a sample report generated with my testing.
AutoGrowth_ExtendedEvent

Hope, this post would help to understand another way of capturing File Growth information.

Advertisements

4 thoughts on “Auto File Growth: Track the growth events Using Extended Events in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s