Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitor SQL Server Database File Growth with WMI Alerts


By:   |   Read Comments (16)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Monitoring

Problem

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.

Solution

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', 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @description=N'Sends notifications to DBA when DATABASE File Growth event(s) occur(s)', 
  @owner_login_name=N'sa'
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)', 
  @step_id=1, 
  @subsystem=N'TSQL', 
  @command=N'EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ''DBServerAlerts'', -- update with your value
    @recipients = ''dba_alerts@YourCorpDomain.com'', -- 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'
GO
EXEC msdb.dbo.sp_add_jobserver  @job_name=N'WMI Response - DATABASE Growth Event',  
    @server_name = @@SERVERNAME
GO

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):
\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER
-- NAMED instance's namespace ("DEMOSQL1\INSTANCE1" SQL Server):
\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCE1
  • 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', 
  @message_id=0, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=15, 
  @include_event_description_in=1, 
  @notification_message=N'WMI - DB Growth notification', 
  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
  @wmi_query=N'select * from DATA_FILE_AUTO_GROW', 
  @job_name=N'WMI Response - DATABASE Growth Event'
GO

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:

CREATE DATABASE [_Demo_DB1]
 ON  PRIMARY 
( NAME = N'_Demo_DB1', FILENAME = N'S:\TESTSQL_DATA\_Demo_DB1.mdf', FILEGROWTH = 1024KB )
GO

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
 (NCHAR_Big NCHAR(4000) NULL)  ON [PRIMARY]
GO
INSERT INTO dbo.Demo_Table_1 (NCHAR_Big) SELECT REPLICATE(N'W', 4000)
GO 3

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

INSERT INTO dbo.Demo_Table_1 (NCHAR_Big) SELECT REPLICATE(N'W', 4000)
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.


Last Update:






About the author





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, October 06, 2016 - 9:38:53 AM - Ian Back To Top

 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.

Thanks

 


Wednesday, August 03, 2016 - 3:12:22 AM - Nicolas Back To Top

 Very interesting and useful. Thanks for this post.

 

 


Wednesday, October 07, 2015 - 3:06:45 AM - GLeb Back To Top

Svetlana, thanks for the article!

For me also was helpful Microsoft documentation:

https://technet.microsoft.com/en-us/library/ms186449(v=sql.105).aspx {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

Thanks. Cheers.


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

Hi,

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

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?

Thanks.

 


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

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.

 

Svetlana


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

Hi,

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.


Thanks,
Sree.


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

Thanks for the valuable info.


Sunday, April 06, 2014 - 5:16:32 AM - Lsuse Back To Top
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 05, 2014 - 7:33:46 PM - Svetlana Golovko Back To Top

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.

 

Svetlana


Thursday, April 03, 2014 - 9:05:50 AM - Lsuse Back To Top

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

Message

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

Hi Svetlana, Much appreciated.

cheers, Sangram


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

Hi Sangram,

You can find columns list from published by Microsoft XML schema: http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd

 

Thanks,

Svetlana


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

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

Hi Svetlana,

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

Regards.


Learn more about SQL Server tools