Automatic Transaction Log Dumps Using Performance Monitor

SQL Server for Windows NT is integrated with the Windows NT Performance Monitor, so you can use the "% Full" counter of the SQLServer-Log object to dump or truncate the transaction log.

Here is how to set up the event:

1. Set up a text file called DUMP.SQL. This file will contain the TRANSACT-SQL commands to dump or truncate the log.

   For example:

   dump tran pubs with truncate_only
   go

2. Start Performance Monitor, and add an alert that will run the script when the log is more than a specified percentage full.
3. Make sure that SQL Server is running.
4. Start Performance Monitor.
5. From the View menu, choose Alert.
6. Choose the button to add an alert event.
7. Choose the object SQLServer-Log.
8. Choose the counter Log Space Used(%).
9. Choose the instance of the database to monitor (pubs in this example).
10. Choose the Over button of the Alert If dialog box, and then enter the percentage at which you want the log to be truncated in the box to the right of this button. For example, entering "75" will issue the truncate command when the log becomes more than 75% full.
11. Enter the isql command under Run Program on Alert. The command should launch the DUMP.SQL file. For example:

    isql -Sserver -Usa -Ppassword -ic:\sql60\dump.sql

12. Click the Every Time button so that the log will be truncated every time the fullness exceeds the specified threshold.
13. Choose OK.

You can save these settings using the Save Alert Settings dialog box that appears when you choose Save As from the File menu so that you do not have to re-create this alert every time you start Windows NT.

Note If you are using Integrated Security, Performance Monitor must be started under an administrator account or one that maps to the database owner.