Capturing FileGrowth Event using SQL Agent Job

Sometimes unpredictable file growth become real pain of a DBA and you want to answers for questions "What happened?" and "When it happened?"
You might capture it using Extended Events, but I want to talk about old-fashion way of capturing these kind of events: via SQL Agent Alerts and Jobs
As always, we do it in steps. Just three easy steps.

Step 1.

For capturing file growth events you need to create a log table "tbl_FileGrowthEvents" in your "DBA_Util" database:

USE [DBA_Util]; GO DROP TABLE IF EXISTS tbl_FileGrowthEvents; GO CREATE TABLE tbl_FileGrowthEvents ( FileGrowthEvent_ID INT IDENTITY(1,1) , FileGrowthEvent_DT DATETIME NOT NULL CONSTRAINT Def_FileGrowthEvents_FileGrowthEvent_DT DEFAULT GetDate() , FileType CHAR(3) NOT NULL  , DatabaseName SYSNAME NOT NULL , [FileName] SYSNAME NOT NULL , FileGrowth_XML XML NOT NULL ); GO

Step 2.

You need to create a job with a name "Alert_File_Growth_Logging". Make sure to specify the owner of that job "SA" or another system account, which have appropriate access.

Create a single step in that job called "Logging Event" and copy-paste there following script:

INSERT INTO DBA_Util.dbo.tbl_FileGrowthEvents( FileType, DatabaseName, [FileName], FileGrowth_XML) SELECT LEFT([type_desc],3), DB_NAME(database_id), [name] , '<FileGrowth_Event> <FileGrowth_Event_Type>' + [type_desc] + '</FileGrowth_Event_Type> <Post_Time>' + IsNull(CAST($(ESCAPE_NONE(WMI(PostTime))) as VARCHAR),'') + '</Post_Time> <Duration>' + IsNull(CAST($(ESCAPE_NONE(WMI(Duration))) as VARCHAR),'') + '</Duration> <Database_Name>' + DB_NAME(database_id) + '</Database_Name> <File_Name>' + [name] + '</File_Name> <Physical_Name>' + IsNull(physical_name,'') + '</Physical_Name> <File_Size_Mb>' + IsNull(CAST([size] / 128 as VARCHAR),'') + '</File_Size_Mb> <NT_Domain_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(NTDomainName)))','') + '</NT_Domain_Name> <Login_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(LoginName)))','') + '</Login_Name> <Session_Login_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(SessionLoginName)))','') + '</Session_Login_Name> <Host_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(HostName)))','') + '</Host_Name> <Application_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(ApplicationName)))','') + '</Application_Name> </FileGrowth_Event>' FROM master.sys.master_files WHERE [name] = N'$(ESCAPE_SQUOTE(WMI(FileName)))'  and database_id = $(ESCAPE_SQUOTE(WMI(DatabaseID)));

You have to get something like this:

Step 3.

Now you need to create two alerts: "Alert Data file auto growth" and "Alert Log file auto growth".
In both you Specify following:
Type: "WMI event Alert"
Namespace: "\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
In this case "MSSQLSERVER" is an instance name of your SQL Server.
Query for Data File Alert: SELECT * FROM DATA_FILE_AUTO_GROW
Query for Log File Alert: SELECT * FROM LOG_FILE_AUTO_GROW
You have to get this:

 On "Response" Page you have to check "Execute Job" and select previously created job "Alert_File_Growth_Logging" from the list:

  On "Options" Page you can increase "Delay between responses" to one minute.

After saving both Alerts you are ready to test.

Step 4.

If you do not have it set, you have to check  the box "Replace tokens for all job responses to alerts" inside of SQL Server Agent -> Properties -> Alert System -> Token replacement.

Test:

Create a test database and run following script for it. (just make sure database name and file names are matching yours)

use SLAVA_TEST; GO SET NOCOUNT ON GO DROP TABLE IF EXISTS tbl_Copy_Messages; GO DBCC SHRINKFILE ('Slava_Test_Data') WITH NO_INFOMSGS; GO DBCC SHRINKFILE ('Slava_Test_Log') WITH NO_INFOMSGS; GO SELECT * INTO tbl_Copy_Messages FROM sys.messages; GO

If you've done all three building steps without errors, you should have a content of your "tbl_FileGrowthEvents" table like this:

From that point you can answer which file has grown and when. So, you might correlate it to your internal processes.
If you need deeper information you can click on XML content and get more information associated with a transaction, which caused file growth event: