MSSQL - Transaction Log grows to a very large size

Posted by: jeff_anderson (IP Logged)
Date: January 13, 2006 11:15AM

Ever since I’ve been using FactorySQL on this particular small project my SQL Server transactions logs have been growing apparently without bound (over a gig now). This wasn’t happening before I installed FactorySQL. I have a few bidirectional groups logging at a rapid rate (.5 sec) and logging groups once every 2 minutes. Any idea what’s going on?

Posted by: shechtman (IP Logged)
Date: January 16, 2006 08:20PM

Yes. Undoubtedly your SQL Server is set for the full backup mode. Try changing it to the partial backup mode. What happens is the transaction log records every request ever made to the database when in the full backup mode. Since FactorySQL is updating the database frequently the log can grow very big.

You can change the setting through Enterprise Manager. In the partial backup mode your request is still written to the transaction log but when the database engine is done executing your query that portion of the transaction can be written over again. In other words, that part of the transaction log file is freed up with the result that the transaction log never grows too large.

The full backup mode is unnecessary in this type of application.