The information in this article applies to:
- Microsoft SQL Server version 4.2
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.
- Set up a text file called DUMP.SQL. This file will contain the
TRANSACT-SQL commands to dump or truncate the log. For instance:
dump tran pubs with truncate_only
go
- Start Window NT's Performance Monitor and add an alert that will
run the script through ISQL when the log is more than 75% full (or
whatever threshold you think appropriate.)
- Make sure that SQL Server is running.
- Start Performance Monitor.
- Choose Alert from the View menu.
- Click on the + button to add an alert event.
- Choose the object SQLServer-Log.
- Choose the counter Log Space Used(%).
- Choose the instance of the database you wish to monitor--pubs in this
example.
- Click on the Over button of the Alert if dialog, then enter the
percentage at which you want the log to be truncated in the box to
the right of this button. For instance, entering "75" will issue the
truncate command when the log becomes more than 75% full.
- Enter the ISQL command under Run Program on Alert. The command should
launch your DUMP.SQL file. For example:
isql -Sserver -Usa -Ppassword -ic:\sql\dump.sql
- Click on the Every Time button so that the log will be truncated
every time the percentage full exceeds the specified threshold.
- Click on OK.
NOTE: You can save these settings using the File, Save Alert Settings
dialog so that you do not have to recreate this alert every time you
start Windows NT.
NOTE: If you are using Integrated Security, Performance Monitor will need
to be started under an administrator account or one that maps to the
database's DBO.