INF: How to Set Up SQL Performance Monitor Database Alerts

ID: Q110904

The information in this article applies to:
  • Microsoft SQL Server version 4.2x

SUMMARY

With SQL Server for Windows NT, the Windows NT SQL System Administrator has the advantage of having the additional functionality provided by the Windows NT Performance Monitor. This additional functionality provides the ability to monitor various activities with SQL Server as it is performing its tasks. One valuable function is the ability to "Alert" selected clients and workstations that certain conditions on the SQL Server exist.

This article illustrates how to set up the SQL Performance Monitor so that a predefined alert will automatically be loaded during startup after an Administrator logs on to the Windows NT Server computer. The alert that will be used in this example will be the Percent of Log Space Used for a selected database. First, some background information is needed in order to understand how this will be done.

Background Information



There are two slightly different forms of Performance Monitor available from the Windows NT Program Manager. The Windows NT Performance Monitor can be found in the Administrative Tools Group, while the SQL Performance Monitor exists in the SQL Server for Windows NT Group. The primary difference between the two program items is that when the SQL Performance Monitor starts up, a file called Sqlctrs.pmc, located in the SQL\BINN directory, is called and passed as a parameter to Perfmon.exe (both program items call Perfmon.exe). Sqlctrs.pmc has the initial default SQL Server Performance Monitor settings. A feature of Perfmon.exe is that it allows you to configure your view of the system and save this snapshot in a file for later use.

The two configuration files that you can set up and save are the Alerts file and the "Workspace" file. The Workspace file, which contains Performance Monitor chart settings and alerts, is saved with a default extension of .pmw. The Alert file containing the user configured alerts is saved with a default extension of .pma.

The example listed in the MORE INFORMATION section of this article describes the creation and use of a Workspace file that will be passed as a parameter to Perfmon.exe, allowing it to automatically start with a user configured SQL Server Database Log Alert.


MORE INFORMATION

The following procedure steps you through the process of creating the Workspace file to creating an icon in the Program Manager Startup group.

The first thing to do is to create a Workspace file that has an alert. (The following should be done as the System Administrator or as a member of the Administrators group.)

  1. Start up SQL Performance Monitor.


  2. On the View menu, click Alert (or type CTRL+A).


  3. On the Edit menu, click Add to Alert (or type CTRL+I).


  4. Type the appropriate SQL Server name for the computer.


  5. From the Object drop down list box, select SQLServer-log.


  6. From the Counter list box, select Logspace Used(%).


  7. Go down to the Alert If frame box and select the radio button Over. In the single line edit box, type 75.


  8. Go to the Run Program on Alert frame. This is where you can type the name of a .cmd or .bat file, or any other executable file that can be triggered when the alert activates. There is also the option of triggering your program once or more than once. Leave this section blank (see the "Additional Information" section of this article).


  9. From the Instance list box, select the database.


  10. Click the Add button; the Cancel button will change to Done.


  11. Click the Done button. You will now notice that your new entry has been added under Alert legend in the Alert workspace.


  12. Change to the Chart workspace by clicking Chart on the View menu (or by using CTRL+C from the keyboard).


  13. On the File menu, click Save Workspace (or use CTRL+W).


  14. You will be prompted for a file name with a .pmw extension.


  15. Type a valid file name and then click OK.


  16. Close SQL Performance Monitor.


Now there is a workspace file in the directory you selected. The next step is to create an icon and put it into the Startup group.

  1. Open the Startup group.


  2. On the File menu, click New.


  3. Select Program Item and click OK.


  4. On the Description line, type a name for the icon.


  5. Type the following in the Command Line box: 
    
      perfmon.exe c:\path_to\your_fil.pmw


  6. Click OK.


The new icon will exist in your Startup group. Next you need to verify that the icon works.

  1. Double-click on your icon and verify that the alert that you have set up exists in your new instance of Performance Monitor.


  2. Close all programs and log out of Windows NT and then log back on again.


The new instance of the Performance Monitor should be up and running.

Additional Information



For additional information about how to execute a batch script that will truncate a user database log when a correctly configured alert fires, please see the following article in the Microsoft Knowledge Base:
Q110565 Automatic Transaction Log Dumping via Performance Monitor

Additional query words: 4.20 Windows NT 1105

Keywords : kbother SSrvAdmin
Version : winnt:4.2x
Platform : winnt
Issue type : kbinfo


Last Reviewed: April 6, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.