INF: How to Set Up SQL Performance Monitor Database Alerts

  - Microsoft SQL Server version 4.2

SUMMARY

With SQL Server for Windows NT, the 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 NT SQL Server as it is performing its tasks. One valuable function is the ability to "Alert" selected clients/workstations that certain conditions on the NT SQL Server exist.

This article will illustrate how to setup 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. 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 C:\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 setup and save are the Alerts file and the "Workspace" file. The Workspace file, which contains PERFMON 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 under the More Information section 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 description will step 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 the SQL Performance Monitor.

2. From the View menu select Alert or type CTRL+A.

3. From the Edit menu select Add to Alert... or type CTRL+I.

4. Enter the appropriate SQL NT Server 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, and in the single line edit box enter 75.

8. Go to the Run Program on Alert frame. This is where you can enter 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 it blank. (See the Additional Information section.)

9. Select the database from the Instance list box.

10. Click on the Add button, the Cancel button will change to Done.

11. Click on the Done button.

    You will now notice that your new entry has been added to the under Alert legend in the Alert workspace.

12. Change to the Chart workspace by selecting the View menu and then Chart or using CTRL+C from the keyboard.

13. Go to the File menu and select Save Workspace or use CTRL+W.

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

15. Enter a valid filename and then click the OK button.

16. Exit from SQL Performance Monitor.

1. Open the Startup Group.

2. From the File menu in the Program Manager select New.

3. Select Program Item from the New frame box.

4. Select the OK button.

5. Enter a name for the Icon in the Description single line edit box.

6. Enter the following in the Command Line single line edit box:

         perfmon.exe c:\path_to\your_fil.pmw
   

7. Click the OK button.

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

2. Exit from the program.

3. Logout of Windows NT.

4. Log back in.

Additional Information

Another article Q110565, titled "Automatic Transaction Log Dumping via PERFMON" discusses exactly how to execute a batch script that will truncate a user database log when a correctly configured alert fires.