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 SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


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

Featured Database Articles

MS SQL

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