By: Svetlana Golovko | Comments (21) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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):
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 = ''[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'
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)
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:
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.
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.
About the author
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
ying