If you want to get alerted by DPA when a data or log file grows/shrinks, this alert will help you do that. As a pre-requisite, an extended event session needs to be configured to track the changes that occur.
Configure Extended Event Session
Extended Event Sessions (EES) can be used to track many events that occur within your SQL instance, and one of those events is named "database_file_size_changed". It fires when a database data or log file grows or shrinks and to configure the EES from Management Studio follow these steps:
- Click into Management > Extended Events > Sessions to see what is currently defined
- To create a new one, right click on Sessions, choose New Session
- On the General tab, give it a name as well as check the "Start the event session at server startup" option
- On the Events tab, find the database_file_size_change event and click the arrow to move it to the Selected events section
- On the Data Storage tab, click the Add button and choose ring_buffer to store the data within memory
- When all tabs have been completed, click OK to save it
When a database file changes in size, the EES will capture it and we can have DPA monitor it with a custom alert.
As an alternative to using Management Studio, you can also download the attached script and use the first portion of it to create the EES as well.
DPA Custom Alert
In DPA, click into Alerts > Manage Alerts and create a Custom alert of type Custom SQL Alert - Multiple Numeric Return. Paste in the second section of the attached script, and make the alert look something like this:
