The new task, dump_sales_log, is a Transact-SQL (T-SQL) type task, which lets you enter any T-SQL command in the command box. The command for this task is
dump tran sales with truncate_only
You don't have to select the name of the database you are monitoring from the Database drop-down list because the T-SQL command already contains it. You need to enable the task by selecting On Demand in the Task Schedule window. This setting lets the SQL Executive Alert Manager demand that the task run when the error occurs.
2. Create a new error number. Because the system does not recognize an 80 percent capacity database transaction log as an error, no predefined error message exists for that situation. You can create an error message with SQL EM by selecting Server, Messages from the SQL EM menu and clicking New at the bottom of the screen. You can select any unused error number larger than 50,000 and supply a message. Enter a severity setting greater than or equal to 16, because anything below 16 signals a user error and the system rarely fires an alert for such a minor error. As Screen 3 shows, the error number for this example is 55555. Be sure to select the Always Write to Windows NT Eventlog check box because the NT Application Log is the only place where the SQL Executive Alert Manager will look for the error.
3. Define the SQL Executive alert. Using the SQL EM, right-click the SQL Executive icon and select Manage Alerts (or select Server, Alerts, Operators from the SQL EM's main menu). Then click New Alert. Screen 4 shows you SQL EM's New Alert dialog box.
Select the name of the database you are monitoring (i.e., sales in the example) from the Database Name drop-down list and select the enabled check box. You can define the alert's response by selecting a task from the Task to Execute drop-down list, write an email notification for SQLMail to send to the operator, or both.
4. Test the SQL Executive alert. Before integrating the new SQL Executive alert, you can test it from within SQL EM to make sure it fires when the defined error occurs (i.e., the transaction log fills to more than 80 percent capacity) and prompts the correct action (i.e., clears the transaction log). First, check the size of the log by running
DBCC CHECKTABLE(SYSLOGS)
and note the amount of free space available. Next, force SQL Server to generate the new error message by using a SQL query window to execute this command
RAISERROR (55555, 17, 1) WITH LOG
You can verify that this message appears in the NT Application Log by using NT's Event Viewer to open the Manager Alerts and Operators screen in SQL EM. (Click Refresh if you have left this screen open.) You can verify that the task ran by opening the Manage Scheduled Tasks screen and noting the last run date. Finally, you can verify that the program truncated the log by running the command
DBCC CHECKTABLE(SYSLOGS)
If the alert is working, the amount of free space will have increased.
5. Define the Performance Monitor alert. You can configure the Performance Monitor alert to activate when the value of the counter exceeds a certain threshold. In Performance Monitor, select View, Alert, Add. At the bottom of the Add to Alert dialog box, define the threshold and the server's action when the threshold is crossed. Screen 5 shows Performance Monitor's Add to Alert screen.
In this example, the server will run SQL Server's sqlalrtr program. The sqlalrtr program interprets an error number as an argument, which causes SQL Server to generate a RAISERROR WITH LOG statement. The complete command in the Run Program on Alert text box shown in Screen 5 is
c:\mssql\binn\sqlalrtr /E 55555 /T /S <server_name>
in which the name of your SQL Server machine replaces server_name. The /E provides the error number to write to the NT Application Log. If Performance Monitor and SQL Server run on different machines, you must provide the name of the SQL Server machine with the /S flag. Finally, you need to specify a login name and password for SQL Server or use the /T flag to specify you want to use a trusted connection. If your SQL Server is not installed on the C drive's default directory, you must change the path for the sqlalrtr executable. The sqlalrtr program will write the message to the NT Application Log. As soon as the SQL Executive accesses the CPU, it will fire the alert and run the associated task.
Running Performance
Monitor as a Service
To detect whether a transaction log is reaching full capacity, you must run Performance Monitor and SQL Server simultaneously. However, because Performance Monitor requires a lot of resources and is usually a foreground program, you must manually start Performance Monitor with SQL Server. This situation might be inconvenient if SQL Server is configured to start automatically when you boot NT. Fortunately, monitor.exe, a utility in the Microsoft Windows NT Server 4.0 Resource Kit, lets Performance Monitor run as a service. You can save your alert definitions in a Performance Monitor Workspace (PMW) file and then specify this file as an argument to monitor.exe. The resource kit documentation provides sufficient detail to help you with this task.
An Important Counter
In addition to the five predefined counters for monitoring SQL Server with Performance Monitor, the counters that let you track the growth of your transaction logs are especially useful. And because you need to do more than just monitor growth of an application, the technique for integrating Performance Monitor and SQL Executive alerts so that SQL Server automatically clears the log when it reaches a defined percentage of its capacity easily solves the transaction log space problem. This technique can help administrators proactively manage SQL Server.
End of Article
Prev. page
1
[2]
next page -->
