Development Matters Blog

A while ago I was assisting a client who ran into a problem with extreme ‘tempdb’ database growth – over 80 Gb. The only remedy for this is to determine what exactly is causing this growth and to deal with the cause. This may sometimes be difficult, but a couple of neat scripts can help this. However, before that, we should capture the moment of database growth, to help us identify the problem.

Email alert functionality is probably one of the most underused functionalities SQL server has. It is easy to use and configure:

1. Make sure that SQL Server Agent is up and running.

2. Under SQL Server Agent, right click on Alerts and select New Alert…

3. In the Name field, type in something like tempdb over 40GB. Make sure that Enable is checked.

4. From Type, select SQL Server performance condition alert.

5. Under Object drop down list, select atabases.

6. Under Counter, select Data File(s) Size(s) (KB).

7. Under Instance, select tempdb.

8. Under Alert if counter, select rises above.

9. In Value field, type in 41943040 (this is for 40 GB, change as needed).

10. Click Response page (upper left).

11. Make sure to have only Notify Operators checked.

12. Click New Operator button.

13. In Name field, type in Send an email.

14. In the Email name field, type in the email address you want to send the email to.

15. Click OK. Window closes.

16. Operator is displayed in the operator list. Select it and check E-mail field.

17. Click Options page.

18. Configure Delay between responses.

19. Click OK button.

20. Repeat steps 3-19 to create another alert for Log File(s) Size(s) (KB) – see step 6.

Then, make sure the SQL Agent has its mail profile defined:

1. Right click on SQL Server Agent, select Properties.

2. Select Alert System.

3. Make sure that Enable mail profile is checked and that Mail system is SQLMail and the Mail profile is defined. You can test this by clicking Test button. If there aren’t any profiles defined, you have to configure them.

SQLMail uses one of the existing account profiles. If not already configured, maybe you would like to create a separate account just for this, configure Outlook on that machine and then configure SQL Server Agent. This is the account FROM which alert emails will be sent from.

You can play around with this in order to use different objects and counters, but the principle is the same.

Note: You can configure SQL Server Agent to auto start (it is not auto started by default). This service is required for sending emails.

We apologize for the inconvenience, but we are experiencing technical difficulties on the Development Matters Blog. We are working dilligently to resolve the issues.

We appreciate your patience. Meanwhile, be sure to check out the Merit Matters Blog.