Managing file growth is a vital task for any Database Administrator. Keeping tabs on when and why growth is occurring can be a challenging task which is dependent on how your have your growth settings configured for any particular database.
One of the more important databases that requires monitoring is the TempDB system database. This database is shared among all other databases on the server. As a result it has the potential to grow quickly if not managed properly. Though I will not be discussing the details of configuring and managing the growth settings of TempDB within this post, I will provide a detailed outline of how to monitor growth by utilizing a SQL Server Agent Alert.
The steps outlined below will walk you through creating a new Alert and configuring it to monitor growth against the TempDB database, regardless of whether you have a single data file or multiple files.
In order to create a new Agent Alert, you’ll first want to connect to your SQL Server through SQL Server Management Studio. Once connected, expand the server and then expand the options under SQL Server Agent. You will see an option titled Alerts. Right-click Alerts and select New Alert as shown below.
The New Alert window will be displayed.
The first step is to provide a Name for the new Alert. In this case we’re creating one to monitor TempDB file growth so in an effort to keep it simple and self descriptive, we’ll just call it TempDB Growth.
For Type you’ll want to choose SQL Server performance condition alert from the drop-down. Once selected the additional configuration options will be displayed. This is where we will configure the alert to monitor the actual file growth of the database. From here you can easily configure an alert to monitor growth against any database on the server, but for the purposes of this post we’ll be looking specifically at TempDB.
In order to configure the alert to monitor growth against TempDB you’ll want to select Databases from the Object drop-down. This let’s the alert know that you will be monitoring something against a specific database, as opposed to other objects such as Query Execution or Wait Statistics.
For Counter you need to select Data File(s) Size (KB). This specifies that the alert will be monitoring the current size of a data file for the specified database.
Next, select the applicable database from the Instance drop-down. In this case, TempDB.
The final step on the General page is to configure the Alert if counter and Value options. You have 3 available options for monitoring the growth (falls below, becomes equal to, or rises above). In this example, as in most, you’ll want to choose rises above. This will trigger the alert if the TempDB data file grows beyond a certain threshold. That threshold is determined by the Value specified. The key thing to note about the value is that it is monitored in kilobytes.
As shown in the screenshot above I have specified 11000000 as the value. This equates to roughly 11GB. Though this may seem high, the server I am working with actually has 4 TempDB files, each at roughly 2GB for a total of 8GB, each with a growth rate of 100MB. As a result, I have configured the alert to notify me if my TempDB files grow beyond an additional 3GB threshold.
Once the alert parameters have been configured, the next step is to configure the response. Click on the Response page option on the left hand side of the New Alert window.
As long as you have an Operator configured on your server, you should be able to select the appropriate Operator to notify when the Alert is triggered. Make sure the Notify operators box is checked and then select the desired delivery method. E-Mail is generally the best option here.
If you do not have any Operators configured and are not sure how to do so, refer to my post on Configuring SQL Server Agent Operators.
Finally, you’ll want to click on the Options page in order to fine tune the details of the alert notification. Though this step is not necessary, I highly recommend clicking the checkbox next to E-Mail under Include alert error text in: as this will instruct the alert to include vital information regarding the alert error within the email notification.
You can also configure the option to have the alert resend on a schedule if the issue has not been resolved. This could be useful as alerts tend to trigger rapidly when the configured event occurs. If the issue is not resolved, the alert will continue to trigger.
Once the Options page has been configured click OK to save the Alert.
If for some reason you start getting a barrage of alerts coming into your inbox after saving the alert you will likely need to go back into the Alert properties and reconfigure the value setting. Depending on how TempDB is configured you may need to extend this value. Keep in mind that if you have multiple TempDB data files you need to remember that the alert looks at the total size of all data files, not each individual file. So if you have 4 files that are 2GB each, you need to make sure the value is configured to account for all 4 files, not each individual file. It’s an easy mistake, and yes, I’ve made it before.