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
, sql_txt
, sql_username
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
, n.value ('(action[@name="sql_text"]/value)[1]','nvarchar(500)') AS sql_txt
, n.value ('(action[@name="username"]/value)[1]','nvarchar(50)') AS sql_username
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.
Hope, this post would help to understand another way of capturing File Growth information.
Thanks Lat its a quick One. Expecting many more EE blogs.
LikeLiked by 1 person
Sure Vinuraj. Thanks for the reading.
LikeLike
Hi, Can you please add query which is causing the data/log file growth
LikeLike
good work, I recently starting putting something together, do you know if it is available in SQL2008?
LikeLike