Monitor SQL Server Database File Growth with WMI Alerts

By:   |   Comments (21)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Monitoring


We have our databases setup to autogrow, but at the same time we have fixed maximum file sizes. The file's autogrowth value, initial database size and maximum size are set to big enough values to accommodate the database growth. We configure all these database file settings with growth in mind, so that a file's size would not increase too often. To make sure that the database does not grow over the maximum size unexpectedly (for example somebody uploading gigabytes of documents to SharePoint) we would like to have notifications every time a database file grows.


In a previous tip, we explained how to setup WMI alerts for database change monitoring. The setup consists of SQL Server Agent configuration steps, Database Mail configuration and creation of the alert and SQL Server job.

In this tip we will provide scripts for setting up WMI alert and a job to monitor database file growth.

Make sure that Database Mail is configured and SQL Server Agent is setup to allow replacing tokens as per the last WMI tip.

SQL Server Agent job

The job that will be created is going to respond to the WMI event every time a database file grows.

The image below displays the job step. You will need to update @profile_name and @recipients parameters with your values (@profile_name will be the "Mail profile" that you created during Database Mail configuration):

SQL Server Agent job

Here is the complete script that you can use to create the job (you will need to update @profile and @recipients parameters with your values):

EXEC msdb.dbo.sp_add_job @job_name=N'WMI Response - DATABASE Growth Event', 
  @description=N'Sends notifications to DBA when DATABASE File Growth event(s) occur(s)', 
EXEC msdb.dbo.sp_add_jobstep @job_name=N'WMI Response - DATABASE Growth Event', 
  @step_name=N'Send e-mail in response to WMI alert(s)', 
  @command=N'EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ''DBServerAlerts'', -- update with your value
    @recipients = ''[email protected]'', -- update with your value
    @body = ''File Name: $(ESCAPE_SQUOTE(WMI(FileName))); 
Start Time: $(ESCAPE_SQUOTE(WMI(STartTime))); 
Duration: $(ESCAPE_SQUOTE(WMI(Duration))); 
Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName))); 
Host Name: $(ESCAPE_SQUOTE(WMI(HostName))); 
Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));'',
    @subject = ''Database file growth event - $(ESCAPE_SQUOTE(WMI(DatabaseName)))'' ;
',   @database_name=N'master'
EXEC msdb.dbo.sp_add_jobserver  @job_name=N'WMI Response - DATABASE Growth Event',  
    @server_name = @@SERVERNAME

Setting up WMI Alert

Now we will setup the alert:

  • set the alert type to "WMI event alert"
  • make sure you use the correct WMI namespace (see below image for more info)
Setting up WMI Alert

Note: the namespace will be different for the default instance and for the named instance:

-- DEFAULT instance's namespace ("DEMOSQL1" SQL Server):
-- NAMED instance's namespace ("DEMOSQL1\INSTANCE1" SQL Server):
  • set the response in alert's properties to execute the job we created earlier:
Set the alert type to "WMI event alert"

Here is the script for the alert including the alert's response:

EXEC msdb.dbo.sp_add_alert @name=N'WMI - Database Growth Events', 
  @notification_message=N'WMI - DB Growth notification', 
  @wmi_query=N'select * from DATA_FILE_AUTO_GROW', 
  @job_name=N'WMI Response - DATABASE Growth Event'

Testing Alerts

Now we should be able to receive email notifications every time a database file grows.

First, we will create a new database called _Demo_DB1. We will configure on purpose for this demo to grow in 1 MB increments:

( NAME = N'_Demo_DB1', FILENAME = N'S:\TESTSQL_DATA\_Demo_DB1.mdf', FILEGROWTH = 1024KB )

Then we will create a table and populate it with large enough values (to trigger the database growth event):

CREATE TABLE dbo.Demo_Table_1
GO 3

You may need to insert more records before the file starts growing:

GO 50 -- repeat insert operation 50 times

You should get an email when the file grows as follows.  We can see in the subject line the database in question as well as more detail in the body.

Monitoring Databases files growth using WMI alerts.

Setting up these alerts helps me control unexpected database growth. With the files growth settings and the database sizes we have setup, I only get a couple (or less) emails a month for normal database growth. But when I get several subsequent emails at the same day then I know we need to look at the database size and review it right away. This usually happens during application upgrades, mass uploads of documents to SharePoint databases, etc...

Next Steps
  • Refer to the previous tip about setting up WMI alerts if you need to troubleshoot WMI alert setup.
  • Read this tip about How to Automate SQL Server Monitoring with Email Alerts.
  • Read this tip about How to setup SQL Server alerts and email operator notifications.
  • Read another tip about monitoring SQL Server disk space.
  • Get familiar with "WMI Provider for Server Events Concepts".
  • Use other classes for your SQL Server events monitoring.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, November 19, 2019 - 8:15:30 PM - Svetlana Back To Top (83139)

Thanks for reading, Jeff.

I've been using WMI alerts for this and many other things for years. Before I haven't had a chance to use them with Availability Groups (AGs) and just recently started using them with AlwaysOn. Just a note here, that some of my servers didn't like these alerts. Most of the AGs were fine, but some of them had some kind of broker issues. I haven't had a chance to investigate it and I wasn't the one who built these servers. Something to keep in mind and test...

Tuesday, November 19, 2019 - 8:05:52 AM - Jeff Moden Back To Top (83133)

Ah... I realise this is an older article but it's just what I was looking for.  The methods that use the default trace file are fine except on really busy systems where they just roll over too fast and have no auto-magic alerting mechanism.  I knew there had to be some sort of WMI method out there and this is it.  Thank you for having taken then time to write this article way-back-when because I think it's going to be just what the doctor ordered.

Thursday, February 8, 2018 - 11:20:34 PM - Rajeev Back To Top (75141)


 Hi Svetlana,

Below is the screenshot of the error i am gettting while creating the job using the below script.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'test',
    @recipients = '[email protected]',
    @body = 'File Name: $(ESCAPE_SQUOTE(WMI(FileName)));
Start Time: $(ESCAPE_SQUOTE(WMI(STartTime)));
Duration: $(ESCAPE_SQUOTE(WMI(Duration)));
Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));',
    @subject = 'Database file growth event - $(ESCAPE_SQUOTE(WMI(DatabaseName)))' ;




Thursday, February 8, 2018 - 6:12:03 PM - Svetlana Golovko Back To Top (75140)

 Hi Rajeev

Can you post the exact error in the comments? I wasn't able to reproduce this. I have an offline database on my demo server and was able to create the job without any issues.




Thursday, February 8, 2018 - 7:39:46 AM - Rajeev Back To Top (75134)


 Hi, This has been very helpful. But I am not able to create a job through this script if any one of the databases are "OFFLINE" in my instance. Could you please help me with this.




Thursday, October 6, 2016 - 9:38:53 AM - Ian Back To Top (43503)

 Great article.  Concise, yet well explained.  Worked first time.

You'd think an alert like this would be straightforward and out of the box with MSSQL.



Wednesday, August 3, 2016 - 3:12:22 AM - Nicolas Back To Top (43036)

 Very interesting and useful. Thanks for this post.



Wednesday, October 7, 2015 - 3:06:45 AM - GLeb Back To Top (38833)

Svetlana, thanks for the article!

For me also was helpful Microsoft documentation: {classes tree is a picture so you can't use text search on the page}

and the utility wbemtest [included in windows os] to explore WMI classes, in the youtube you can see how to use it

Monday, June 29, 2015 - 8:02:29 AM - sqlism Back To Top (38057)

Thanks. Cheers.

Sunday, June 28, 2015 - 7:42:34 PM - Svetlana Back To Top (38054)


The overhead is the same as you would have with any other alerts.

The only overhead I would see would be if someone for example has 200 databases that grow by 1 MB and they are very active. But even then the overhead is more on the data file growth side, not the alerts themselves.


If you pre-size your database properly and set reasonable data files growth value then there shouldn't be even many alerts.

We get probably 1 alert in 2 weeks for 500 databases (sometimes even less frequent).

Thursday, June 25, 2015 - 4:41:40 AM - sqlism Back To Top (38031)

Hi Svetlana,

Thank you for the great tip. Very useful indeed and I'm currently going through other events that might be of use.

One question: Is there any overhead for setting up WMI alerts or are they lightweight? How do they affect the performance of the server?



Tuesday, April 14, 2015 - 2:53:07 PM - Svetlana Golovko Back To Top (36933)

Hi Sree

You can setup Windows scheduled task that will run every NN minutes (for example 5 minutes) and run SQL script with query to the default trace. The default trace records file growth as well. The problem is that you will still have to figure out how to send e-mails as without SQL Server agent in your case SQL Mail won't be available as well. Your systems administrators can probably advise you on mail client that is approved in your environment.



Tuesday, April 14, 2015 - 11:00:02 AM - sree Back To Top (36929)


How to configure db growth alerts in SQL server 2008R2 express edition(Production). In express edition we can not enable the SQL agent. How do i configure dbgrowth alerts?

Please help me asap.


Friday, January 23, 2015 - 2:10:10 PM - Srini Back To Top (36042)

Thanks for the valuable info.

Sunday, April 6, 2014 - 5:16:32 AM - Lsuse Back To Top (29980)
Hi Svetlana, thx for your quick respone. I figured it out. I forgot the following step: "Make sure that Database Mail is configured and SQL Server Agent is setup to allow replacing tokens as per the last WMI tip." after enabling this option the WMI alert worked fine.
By the way great articel!

Saturday, April 5, 2014 - 7:33:46 PM - Svetlana Golovko Back To Top (29979)

Hi Lsuse

Do you try to start the job manually? To test the job you need to use my example or wait for the trace file to roll over normally (it may take a couple of days on the test server if it's not very busy). This will trigger event and populate variable.



Thursday, April 3, 2014 - 9:05:50 AM - Lsuse Back To Top (29961)

I'm testing this out but the agent job returns a error....


Unable to start execution of step 1 (reason: Variable WMI(FileName) not found).  The step failed.

any idea?

Wednesday, December 18, 2013 - 4:15:12 PM - Sangram Back To Top (27838)

Hi Svetlana, Much appreciated.

cheers, Sangram

Wednesday, December 18, 2013 - 10:57:38 AM - Svetlana Golovko Back To Top (27831)

Hi Sangram,

You can find columns list from published by Microsoft XML schema:




Tuesday, December 17, 2013 - 7:10:49 PM - Sangram Back To Top (27824)

Hi Svetlana, Until today, I never used WMI. Quite a useful article.  I have one question though. How do you find what columns list are available for a given wmi event. in this case, DATA_FILE_AUTO_GROW.

cheers, Sangram

Wednesday, December 11, 2013 - 4:43:06 PM - Junior Galv„o - MVP Back To Top (27771)

Hi Svetlana,

Great article, I will use these script in test environment!!!


get free sql tips
agree to terms