SIMPLE recovery mode not truncating tranlog
-
I have a database in SIMPLE recovery mode. If I create a simple table in it, and bang in 4 million rows (for example), the tran log grows by about 600 Mb. No problems there.
I then sit around for a few minutes, and check the size of the log again. Still approx 600Mb (sometimes a little higher, as other activity on the server is contributing to its size).
I run DBCC OPENTRAN - no open transactions.
Check the size of the log once more (10 minutes old now) - approaching 700 Mb.
Run BACKUP LOG databasename WITH TRUNCATE_ONLY.
Log usage falls to less than 10Mb.
So, my question is why is this log not autotruncating? There's clearly no open transaction in there, since DBCC OPENTRAN does not report one, and an explicit BACKUP command does truncate the log.
I've observed instances where a genuine long-running transaction has contributed to the overall size of the log, but running an explicit BACKUP command whilst that transaction is still open (and is the oldest open transaction according to DBCC OPENTRAN) still frees up some log space. This is presumably due to completed transactions that are even older, but which haven't yet been auto-truncated by the checkpoint process.
The CHECKPOINT SLEEP, LOG WRITER and LAZYWRITER threads are all visible in the process list, and stopping/starting the SQLServer and SQLAgent services have not fixed the problem. Other databases on the same server do NOT suffer from this problem.
Any thoughts?
-
the log will truncate at a checkpoint, sql server will decide when best to do that , or not, try issuing an explicit checkpoint.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
But the log ISN'T truncating at checkpoint. The recovery interval is set to default, but even when I leave my test transactions (and there are lots of these) to mix with the actual on-line business transactions (yes, this is a production server), the log still does not truncate, even when it reaches sizes exceeding 50Gb.
This is causing us operational problems.
-
Then schedule a job that truncates the log. It's easy, and then your operational problems go away (at least that one).
Tom
-
Simple recovery mode does not cause the tran log file to shrink. You could set the autoshrink option for the database to make that happen (not a best practice in many situations).
A checkpoint marks the inactive part of the log as reusable. A truncation can release the inactive part of the log, but requires a specific BACKUP LOG, BACKUP LOG ... WITH TRUNCATE_ONLY, DBCC SHRINKDATABASE, or DBCC SHRINKFILE.
Another reason the file might not shrink is that it has a large minimum size. The initial size specified when the database was created, or the last value used in a DBCC SHRINKFILE command sets the minimum size, and only another DBCC SHRINKFILE command can shrink it further.
-
I double-checked in BOL just to make sure I was correct. A CHECKPOINT in simple recovery mode truncates the logical log file (marks virtual log segments as reusable) but doesn't shrink the physical log file. But the TRUNCATE_ONLY and NO_TRUNCATE options in BACKUP LOG do refer to truncating the physical file. I would agree that using the same term for both concepts is confusing. A quote from the "Truncating the Transaction Log" topic in SQL 2000 BOL:
Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log.
It seems to be clearer in SQL 2005 BOL that you can't just set simple recovery mode and rely on checkpoints to shrink the file:
If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files. To reduce the size of the logical log and, also, free disk space for reuse by the transaction log file, the inactive log must be truncated periodically.
When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, the inactive portion can be freed by log truncation. Truncation frees the inactive virtual log files for reuse. Eventually, when a new record is written to a freed virtual log, that virtual log file becomes active again.
Note: Truncation does not reduce the size of a physical log file. Reducing the physical size of a log file requires shrinking the file. For information about shrinking the size of the physical log file, see Shrinking the Transaction Log. Although if you analyze this:
Paragraph 1: You must truncate the transaction log to shrink the file.
Paragraph 2: CHECKPOINT (in simple recovery mode) causes truncation.
Paragraph 3: Truncation won't shrink the file.
You've really got to pay attention to when they're talking about the physical log file, the active logical log file, and the inactive logical log file.
-
Thanks for all the replies, but I should clarify that ai am NOT talking about SHRINKING the log - just truncating the active portion. The problem is that if I do nothing, then the active portion of the log will grow and grow and grow until it fills up the disk (like it did yesterday), just as if the database was in FULL mode and we weren't backing up the log.
Tom, thanks, but I've already written a TRUNCATE LOG job as a workaround. However, that doesn't resolve the problem - just masks it.
I don't understand why this is just affecting one database on the server. If I repeat my dummy transaction test on other databases on the same server, the log truncates itself every few minutes. In this one database, though, it doesn't - the ACTIVE portion of the log remains exactly the same size unless I issue an explicit TRUNCATE command myself.
-
you might be suffering with active transactions, there's something I recollect about things not finishing correctly and thus leaving a pointer/entry in the log beneath which the system won't truncate. Sorry it's years since I encountered this hence vagueness. I figure I scheduled a job to deal with it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
You might try examining the log for clues with undocumented DBCC commands:
DBCC
LOG ( '<database>' | <dbid> , 0 | 1 | 2 | 3 | 4 | -1 )
DBCC LOGINFO ( '<database>' | <dbid> )
The DBCC LOG command produces increasingly verbose output for each value of the type parameter. DBCC LOGINFO produces more detailed (but possibly even more obscure) info.
These commands will show you what's in the log, and the transaction that each log record came from. Hopefully that will help pinpoint the cause. I tested DBCC LOG on a simple recovery db this morning (before anyone else showed up, so there was no activity) and found 2899 log records. After forcing a checkpoint there were only two log records.
-
Has this database been in full recovery before? Try this if you can. Detach the database and re-attach a single file db. A new log file will be created. See if truncation will work with the new log file.
Just a guess.
Tom
-
Just to revisit this a little - I am intending to try Tom's suggestion, since I suspect this is a corrupt logfile.
However, if I issue a manual CHECKPOINT command, the log then truncates. Again, it is ONLY in this database that I see this behaviour. If I leave the database alone for, say, a week, then the log will have grown to something approaching 9 Gb. Issuing a CHECKPOINT then takes several minutes to run, but the end result is an active log of only a couple of hundred Mb.
It would appear that the checkpoint is simply not happening in this database - but I've no idea why. (The recovery interval is set to the default of 0, but that's a server-wide setting in any case, so shouldn't have affected just one database even if it had been set to a weird value).
I've discovered a second server which is exhibiting exactly the same behaviour. And guess what - the name of the databases that are misbehaving on the 2 servers are the same! I strongly suspect that one was copied from the other (or both were copies of another common source), and so any "corruption" in the transaction log has been preserved in the .LDF files.
I'm still no closer to understanding why a manual CHECKPOINT and a manual TRUNCATE both work, yet the system-fired ones do not.
-
Was this database (or an ancestor) ever replicated? A database that was set up for replication would exibit the same symptoms (log not truncating) if the replication agents were stopped but the server thinks replication is going on. Or if a copy is made of a replicated database.
You could try sp_removedbreplication '<dbname>'. Even if this is not the solution, it won't break anything.
-
Hi Scott,
I'll try it (doesn't do any harm as you say), but I doubt it was ever replicated. Even if it had been, I wouldn't be able to truncate the log manually either, whether using the TRUNCATE_ONLY syntax or issuing a manual CHECKPOINT, so I really don't think this will be the solution.
-
I hate to point out the obvious, but the first question asked by Singer Sewing Machines tech support is: Is your sewing machine plugged in? (50% of the calls end at that point)
So if you haven't done so already, please double check that you have the same options on this database as you do on others. Maybe it is in full recovery mode. It seems to me that in SQL 7 there was a truncate log on checkpoint option, which has apparently gone away (at least in enterprise manager) but if this database happened to be upgraded from a SQL 7 server, that might leave something (an old option) set in a manner you did not want.
Good luck,
Brian
-
Well, it's always worth pointing such things out because we all forget the obvious at times. But no, I have checked that (several times!). It's also worth pointing out that even if that had been the case, issuing a manual CHECKPOINT would NOT have caused the log to truncate. In my situation, issusing a manual checkpoint DOES cause the log to truncate.
Note: Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply