There are a few things you an do:
1. If you have no plan to perform any maintanence on a regular basis on
this database ( ie. dump the transaction log ) you need to set the
'trunc log on chkpt' option on. This will automatically truncate
committed transaction about every minute.
2. Use the scheduler to dump that transaction log to disk, tape or
truncate_only on a regular basis.
3. I think it is possible to imitate Sybase's threshold stored procedure
but using the SQL Executive or the SQL Monitor determine the size of the
transaction log and have it start an ISQL session that can dump the
transaction log. You have to write the bat file and sql script for isql
Remember that dumping the database needs to be followed or preceeded
with a transaction log dump. As I recall, dump database doesn't clear
out the syslogs table.
If you do this an still have problems with the log filling up, I'd say
that you have an application that fails to commit transactions but
continues to run until the log is full.
Good luck, Robert
> I have a problem with SQL Server 6.5(SP4) on Compaq, NT 4.0(SP3) where
> the transaction log fills up every few days. The tran log is on a
> separate device and I have increased the log size up to 1GB and it
> still fills up! The actual database is only about 6MB in size. I do a
> 'dump transaction with truncate only' to get around the problem. My
> question is does anyone know of any tips or tricks that could be used
> to trap or trace the transaction that is filling up the log?
> Thanks in advance for any responses.
> Melvin Mariney