Syslogs

[DarkMan]

I'm having a problem with the syslogs constantly filling up in a database. I've checked out the settings and when I dump the logs, it says I have 30 megs for the log size, and 29.96 megs free space, but, the database throws off an error that says it is full and to dump the log. Once I do truncate the log, everything works fine, for a while, at least.<br>
<br>
Anyone know what is going on here? Is it possible that there is something being thrown into an infinate loop or something? I do have somewhat complex triggers in the database. Or is there a possibility that I've not thought of? Any suggestions are greatly appreciated.<br>
<br>
Thanks.<br>
DarkMan

 

[bigbak]

In SQL Enterprise Manager, Edit the database, click on options and ensure the "Truncate Log on Checkpoint" is checked.

 

[jnicho02]

If your database is set to back itself up regularly it should clear out the log. I have had some problems in the past on 6.5 with pages being left uncleared - fix with a regular 'DBCC Checkdb'.<br>
<br>
Selecting "truncate log on checkpoint" should only be used if you don't mind going back to your last backup should anything fail. If you are running a production database (e.g. a company's order system) you would want to be able to restore to the nearest half hour.

 

[Madsam]

In addition to dumping the log on checkpoint (provided you are using full database backups, rather than incremental log backups...), a second measure we take on a daily basis with each of our databases to groom the log is to run the following code for each db:<br>
<br>
/* Cleanup logs */<br>
<br>
use master<br>
go<br>
dump tran whatever_the_dbname_is to diskdump with truncate_only<br>
go<br>
use whatever_the_dbname_is <br>
go<br>
checkpoint<br>
go<br>
dbcc sqlperf(logspace)<br>
go<br>
dbcc checktable(syslogs)<br>
<br>
The dbcc commands "correct" the log information, which often is the "problem", rather than the log being filled up. <br>
<br>
Regards, madsam