March 27, 2007 at 12:48 pm
I don't understand this at all. I have a .net "Session State" database that is in Simple recovery mode; my understanding is that the transaction logs shouldn't really be written to at all. And yet, in the past two days we have suddenly had a problem where the transaction logs are increasing at the rate of 3MB EVERY SECOND! I have had to start truncating them every ten minutes just to keep the drive from filling up.
How can this be happening, or what could possibly cause it? I don't understand it at all. Thanks for any help in this arena...
Amy
March 27, 2007 at 1:51 pm
Are you running the same programs you have been running earlier
Mike
March 27, 2007 at 1:57 pm
We assume something has changed, but we don't know what yet. My inquiry is more along the lines of how transaction logs can be generated like that when it is in Simple recovery mode. My understanding was that if you put it in simple recovery mode, you usually didn't even need to truncate the transaction logs.
March 27, 2007 at 2:25 pm
If there are ongoing transactions into the transaction log it may not issue a checkpoint (does not truncate however even then) so the log will be marked for use. So if you have a lot of the same data going in continiously the system may not be able to issue a checkpoint, would test fine but in a high volume system you might see this very situation. I would watch how many inserts are happening with Profiler and see if any checkpoints get issued.
March 27, 2007 at 2:33 pm
That is helpful - is there any way to force a checkpoint every so often, from the server rather than through the application?
March 27, 2007 at 10:58 pm
Check the processes that are currently running and by the spid you could
see the command that is used and check if it can be killed.
March 28, 2007 at 3:33 am
First, Simple recovery mode in SQL 2000 does not reduce the amount of information logged in any way compared to Full recovery mode. What Simple mode does is allow that log information to be discarded after the transaction does a commit. If nothing was logged, you would not be able to do a rollback.
Second, Although a Checkpoint is needed to trigger the log truncate process, a Checkpoint will not always force a log truncate, even if you issue a manual Checkpoint statement. There is a limit on how much work a single Checkpoint will do - this is because Checkpoint processing prevents anything else from running, and Microsoft decided that limiting the work a single Checkpoint could do was preferable to pausing all other work for a long period. Most of the time-consuming work done by a checkpoint relates to flushing dirty pages to disk, and the log writing associated with this. However, the lazy writer is doing the same work asynchronously outside of Checkpoint processing. Therefore, even on a busy system the lazy writer will catch up with the outstanding workload during the course of a few Checkpoints. When a Checkpoint can complete all its work, the log truncate work can start.
The key thing that is needed to reduce the size of transaction logs is to issue regular COMMITs. Until the transaction ends no log data can be truncated.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 28, 2007 at 8:30 am
Ed brings up a great point. Also be sure that you aren't abandoning open transactions with connecitons being held open.
I'm not sure if the .NET state changes anything on the database with regard to logging. My impression is it does not. That the database still logs.
The best thing for a production database is to run regular t-log backups. You need these for recovery purposes.
March 28, 2007 at 9:12 am
you probably have an open transaction that is preventing the log being truncated. in the database run command 'dbcc opentran' , this will tell you oldest open command (with its spid), you can then identify this command and most likely will want to kill it. Log should then truncate
---------------------------------------------------------------------
March 28, 2007 at 9:15 am
All very good information, thank you - I tried the dbcc opentran, and unfortunately there is not a persistent open connection - just ones that start and then are dropped. 🙁 So still don't know the cause...
March 28, 2007 at 9:24 am
OK let me ask the obvious missed question, what version of SQL Server are you running so if there are any known issues we can be sure we talk about the right version.
July 13, 2007 at 12:21 am
Other questions are there any open transaction in the server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 14, 2007 at 10:00 am
We had a similar problem with a database. The transaction log would grow and grow and it wouldn't get truncated. No checkpoints were issued by sql at all (according to profiler); and if a checkpoint was issued directly in a query, the logs will get truncated normally. This was happening on sql server 2000 sp4 and the db was using the simple recovery model. What I did was to simply move the location of the log file to another drive, and everything went back to normal, but could never figure that one out.
November 14, 2007 at 10:21 am
Ah, forgot to say that no checkpoints were issued even despite the fact that DBCC OPENTRAN said there were no open transactions in the db.
November 14, 2007 at 10:48 am
Hi all - finally found the solution to this so thought I'd post it. Turns out they had upgraded the .net driver on the source box; what they don't really mention is that when you do that, you need to upgrade the ASPState session state database as well, to the version that comes with the .net driver. As soon as we did this, all problems were resolved.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply