Featured Article(s)
Tips for using alerts in SQL Server 2008
In this article, you can find some useful tips to work with SQL Server 2008 alerts.
Featured White Paper(s)
Why and How You Should Find and Fix Index Fragmentation
In a high-volume database you need frequent critical maintenance. If you skip this important task, index fragmentation may ca… (read more)
SQL Alerts
You don’t have to purchase a server monitoring tool in order to get alerts to events occurring in your SQL Server database. Out of the box, SQL Server already comes with a number of alerts you can easily configure for yourself. It also has extensions allowing you to write alerts based on your own queries, or even PowerShell scripts.
I have one customer that uses tempDB extensively in their queries. If tempDB goes down, their entire business is offline; not acceptable. As a company just emerging from the startup phase, cash is very tight. It is difficult to justify thousands of dollars for monitoring software for their simple needs. In this case, I simply establish an alert on tempDB that sends an Email whenever the tempDB transaction log percentage used reaches 70%. This provides me with plenty of time to make corrections before the server stops responding to business requests.
Alerts are pretty easy to configure.
- First you need to configure Email on the database server. You can use Database Mail using a SMTP service. This is the most straightforward method to implement,
- Second, Configure SQL Server Agent to use the Email account you have configured. You can do this by right clicking on the SQL Server Agent icon in the SSMS explorer and selecting properties. The Mail configuration is in the Alert System dialog.
- Third, you need to setup one or more operators that may be people or groups. Essentially, if using Email, it has to be a handle for an Email account. the operators have a schedule when they are active, and addresses for Email, Net Send and pager numbers. You configure operators by adding them in the Operators folder under SQL Server Agent in the SSMS object browser.
- Fourth, you configure your alerts. Alerts are added to the Alerts folder in the SSMS object browser under SQL Server Agent. When you create an alert you determine what event you wish to track, what is the threshold for which you want notifications, and one or more operators to be notified with the event is triggered.
Now you’re good to go. You have a number of alerts that may be configured easily built into the system. It looks very similar to the items you can track using Sys/Perf monitor, restricted to the SQL Server objects. There are WMI and User definable events that you can add as well. You can even write your own SQL Query to be executed. With the addition of the system stored procedures and functions, this provides a great deal of flexibility.
What kinds of things might you monitor? I find it important to monitor things that can predict eminent server performance degradation such as:
- Data Files or Transaction Log too Full
- High Degree of Fragmentation
- Running out of Disk Space
- CPU Utilization Really High for a Long Period of Time
- Disk Cache hit Ratio Really Low
- High Number of Page Faults
I don’t have an alert active for all of these at once. I don’t want to slow the server down with monitoring. I choose the ones that have the greatest risk at any point in time and turn them on.
If you want to set up alerts in your system there is plenty of help. Use your search engine to find Create SQL Server #### Alert and you’ll find plenty of help specific to your version.
Do you have a monitoring tool you prefer to use? How about tips on how to use the built in Alerts in SQL Server? Do you use Alerts for your SSAS or SharePoint Installations, and how does that differ from a standard Database configuration? Share your tips and insights with our readers by sending them to btaylor@sswug.org.
Cheers,
Ben