INF: Automatic Transaction Log Dumping via Performance Monitor

• Microsoft SQL Server version 4.2x

SUMMARY

One of the features of SQL Server for Windows NT is its integration with the Windows NT Performance Monitor. The SQL Server for Windows NT "Configuration Guide" indicates that you can use the % Full counter of the SQLServer-Log object to dump or truncate the transaction log.

MORE INFORMATION

Below is an example of how to set up the event.

1. Set up a text file called Dump.sql. This file will contain the Transact-SQL statements to dump or truncate the log, as in the following example:
   

   
          dump tran pubs with truncate_only
          go 




2. Start the Window NT Performance Monitor and add an alert that will run the script through ISQL when the log is more than 75 percent full (or whatever threshold you think is appropriate.)



3. Make sure that SQL Server is running.



4. Start Performance Monitor.



5. On the View menu, click Alert.



6. Click on the + button to add an alert event.



7. Select the SQLServer-Log object.



8. Select the Log Space Used(%) counter.



9. Select the instance of the database that you want to monitor (for this example, select the pubs sample database).



10. Click the Over button of the Alert If dialog box, and then type the percentage at which you want the log to be truncated in the box to the right of this button. For example, if you type 75, the truncate command will be issued when the log becomes more than 75 percent full.



11. Type the command file name that contains the following command:
    

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

    
    A command file is required because Windows NT Performance Monitor passes an extra parameter, the alert condition, to the program. For additional information, please see the following article in the Microsoft Knowledge Base:
    

    Q117371 Setting Performance Monitor Alert to Run Program May Not Work




12. Click the Every Time button so that the log will be truncated every time the percentage full exceeds the specified threshold.



13. Click OK.

• You can use the Save Alert Settings command on the File menu to save these settings so that you do not have to re-create this alert every time you start Windows NT.



• If you are using Integrated Security, Performance Monitor must be started either under an administrator account or the account that maps to the database's owner (DBO).

Additional query words: PERFMON 1105

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