Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 12, 2002

SQL Server Agent - Creating Performance Condition Alerts on Database Server Objects

By Alexzander Nepomnjashiy

In my previous articles we discussed the SQL Server Agent subsystem and one of its components, alerts. In this the fifth article in the series, we discover that the DBA can select among two types of alerts: SQL Server event alerts and SQL Server performance condition alerts. My next article, "Creating alerts on database server performance condition with Performance Monitor", will describe how DBAs can implement general performance monitoring for Microsoft SQL Server with Performance Monitor (a standard tool included in the Windows NT Server operating system family).

Compared to performance monitoring with Performance Monitor, SQL Server performance condition alerts are used to monitor the internal performance of SQL Server objects. A SQL Server performance condition alert fires upon a specific performance condition -- i.e. when the specific threshold has been reached.

To set up a SQL Server performance condition alert:

  1. Start SQL Server administrative console - Enterprise Manager

  2. Make a connection to a database server, drill down (expand) SQL Server objects to Alerts (your_database_server_name -> Management -> SQL Server Agent -> Alerts), and place a mouse cursor on it

  3. Select "New Alert..." in the context-dependent menu (right mouse click on the details pane)

  4. In the "New Alert Properties - (your_database_server_name)" dialog window, define the following options:

On the "General" bookmark:

  • Name = "Deadlock(s) detected . . ."
  • Type = "SQL Server performance condition alert." Ensure that the "Enabled" option is checked
  • Object = "SQL Server:Locks"
  • Counter = "Number of Deadlocks/sec"
  • Instance = "Database"
  • Alert if counter = "becomes equal to"
  • Value = "1"

On the "Response" bookmark:

  • Click "New operator..." button
  • In the "New Operator Properties - (your_database_server_name)" dialog window: for the "Name" field, input DBA; for the "Net send address" field, specify the net send address of the newly created operator. In our case, this should be the domain account name for the database administrator or his/her personal computer NetBIOS-name
  • Test the notification functionality by pressing the "Test" button. (Your database administrator should now see a very strange message :-)
  • Press the "OK" button 2 times

ATTENTION: You have just created an alert to notify you when the number of deadlocks in any of your SQL Server databases raises above 1 per second); however, the alert will not fire until the SQL Server Agent service has been started.

It is a best practice to start the SQL Server Agent with your database server. To do so, ensure that the "Auto-start service when OS starts" option for services SQLServerAgent in the SQL Server Service Manager window is checked.

See All Articles by Columnist Alexzander Nepomnjashiy

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM