SQL Transaction Logs

[Ely]

My firm has written a program in VB6 (Enterprise), that works against an SQL Server 6.5, installed on a NT 4.0 Server. The database is declared on two devices:
a 150 mb data device and a 50 mb log device.

Gradually, the log is filling up, until you have to increase its device's size to several hundreds of mb to keep on working. The database itself is normally smaller than 10mb.

For some reason, the log cannot be truncated or dumped. We've tried doing this in code, at the isql command line and in the Enterprise Manager.

I know that this may happen because of 'open' transaction, but shutting down the sql server didn't help.

Any ideas how I may reduce the log's size, or at least avoid this problem when starting fresh with a clear database?

Thanks in advance,
Ely

[This message has been edited by Ely (edited 10-25-1999).]

[JP]

To set the log to truncate after a successful commit/checkpoint, take a look at the "Truncate log on checkpoint" option for the database. It may help, but I'd suggest checking with a dba with regards to transaction log backups etc.

As to why you can't truncate the log, I have come across this at a couple of places - the coincidence being that this always happens when the log is split over 2 or more devices. I'd be interested if you found the answer to this one!!

Good luck.

[Ely]

The log is only on one device this time. And the trunc on checkpoint option you were talking about might help, but the logs were truncated manually (or in code) after each transaction, and they still got filled up.