I often have a problem of the transaction log problems. It seems that the system is not likely to truncate the logs when the checkpoint comes. Is the reason that there are many users using the SqlServer6.0 so that the backend cannot truncate even part of the logs? Would you suggest a way that the logs would not be accumlating?

-- Anonymous, December 13, 1998

Answers

Re: Transaction Logs Explosion

Patrick:

First of all, make sure that you have the "Truncate Log on Checkpoint" option set.

Then check these recommendations from Neil Pike of Protech Computing Ltd.:

Q. Why does my SQL Server log show that it's still full? - I have truncated it. (v1.0 17.10.1998)

A. The reason for this is that all the tools that interrogate log space - e.g. dbcc sqlperf, sp_spaceused and SQL EM all just look at the system catalog information in sysindexes - the dpages column. In SQL 6.5 and earlier this information is NOT kept up to date, so it is constantly wrong. The reason it is not kept updated is that it would cause a performance bottleneck.

The easiest way to correct the information is :-

dbcc checktable(syslogs) go checkpoint go

The information will then be correct until the next update/delete/insert transaction is issued.

If your log REALLY is full - i.e. you're getting a 1105 error on syslogs, then try a "dump transaction with no_log". If this still doesn't fix it, then one of the following is occurring.

1. You may have an open transaction. Check that with the following command.

use go dbcc opentran()

2. You may have un-replicated transactions. See Q184499 for info on this and how to clear it up.

See Q110139 and Q184499 for more information on syslogs filling up and how to resolve them.

Hope this helps,

Eric

-- Anonymous, December 15, 1998