 | |||
|
|
||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| Book Excerpt: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||
System Monitor Alerts Alerts are a nice way to trigger events when given system thresholds are exceeded or not met. For example, you can configure an alert to occur when your system has exceeded 60 percent of the processor and has used 80 percent of the physical memory. When these two example conditions are met, you can trigger a program to be executed, a message to be sent to the operator using NET SEND, and even start up the System Monitor log to take a snapshot of the system. To configure an alert, open System Monitor and drill down to Alerts under the Performance Logs and Alerts group. Right-click on Alerts and select New Alert Settings. This will open the screen shown in Figure 5-6. Next, type any message in the Comment section to help you remember what the alert does, then click Add to add the first counter. Once the counter is selected, choose whether you want the alert to be triggered when the value is over or under the given limit. Finally, select how often you want this counter to be checked with the Interval option.
NOTE In the Action tab, you can specify what action will occur when the alert has been triggered. In Figure 5-7, I am sending a NET SEND message to the workstation with the name XANADU. At the same time, I'm starting a System Monitor log to capture a snapshot of the system. Optionally, you can have programs execute to further help you diagnose a problem or self-fix it. For example, you could have a table automatically truncated when disk space is at a premium.
Figure 5-6 Creating a sample alert with a sample interval of 30 seconds
Figure 5-7 Configuring the alert to send a message and start the log when it is triggered The last tab is the Schedule tab, which is where you can specify when you want this alert to be activated. For example, you can specify that the alert will only be activated during business hours or turned off during peak system time. Once our example alert is triggered, the message popup will look like Figure 5-8.
Figure 5-8 Message received when the alert has been triggered sp_monitor The sp_monitor system stored procedure shows you statistics about your SQL Server. You must be a member of the sysadmin role to run the procedure. The procedure provides an interesting way to perform trend analysis on your server by creating a table to log the data over time and a SQL Server agent job to execute your procedure. The last_run column shows you the last time the sp_monitor stored procedure was run, and the current_run column shows you the current system time. The seconds column displays how many seconds ago the stored procedure was run. Some sample results are shown in the following table.
Each time you restart SQL Server, the three columns are reset. The rest of the columns show general statistics about SQL Server. The columns are represented as number(number)percentage. The first number represents the amount of activity since SQL Server started, and the number in parentheses tells you how much activity has occurred since the last time sp_monitor was run. The percentage is the amount of activity as a percentage since startup. For example, in the following table, you can see the second part of a report:
| ||||||||||||||||||||||||||||||||||||||||||||||||||
The following excerpt, courtesy of McGraw-Hill Osborne Media, is from
Chapter 5 of the book "SQL Server 2000 for experienced DBAs" written by Brian Knight. Click for the complete 
