September 10, 2009 at 2:40 am
I've noticed my transaction log file has been rapidly increasing in size, both actual size and the amount it's filled, recently. It seems to be fairly sporadic. My database is currently in full recovery mode, although it's questionable if this is necessary as we do full backups 4 times a day, and tran log backups at the same time, and no other. I was asked to investigate the seemingly erratic filling up of the transaction log because we couldn't understand why so much was being logged, when the database is only a mediumly active database - about 2,000 new rows per day and probably about 10,000 updates. Yet the log can sometimes grow by 20Gb in a day.
I used the redgate log tool to look at what was in the log file and it shows a lot of delete - insert processes where i'd expect to be seeing an update. The delete shows the before data and teh insert shows the after data - for a simple "update row where column(Not pk) = blah" process. My question is, is this normal and if it's not, why is it happening and what can I do to fix it?
September 10, 2009 at 3:20 am
Have a look at this article http://support.microsoft.com/kb/238254
A while ago, when looking at a replication issue, I discovered that if the update makes changes to the primary key or any column participating in a unique constraint, SQL Server actually does this as a delete followed by an insert.
That has disastrous consequences with replication if you are using foreign keys with CASCADE DELETES!!
September 10, 2009 at 3:37 am
Thanks for the link - I've checked it out but I'm not sure it entirely fits - it could be the source of some of the extra delete / inserts I'm seeing but certainly for most of them i'm only updating a status (int) column which has no constraints on it at all :S
The table itself is part of a vertically partitioned pair which makes up a view of the "whole" table, but I don't see how that could be the probalem as the inserts and updates are done on the underlying tables, not to the view. The primary key is never updated, and until looking at the trans logs I was under the impression that no rows were being deleted (Not that they remain deleted).
I'm gonna google the term "deferred update" as that could be what's going on, although the article linked seems to imply this is a replication mechanism and I'm not using any replication on this database, so that may be a dead end too.
Thanks for the help though 😀
September 10, 2009 at 5:11 am
I don't think it's specifically a replication mechanism... that is the way SQL Server handles some updates.
It makes itself visible in replication because SQL Server constructs the replication commands from the transaction log, and what it is seeing there is a delete followed by an insert.
The article also mentions a trace flag that forces UPDATES to be done as a DELETE/INSERT pair... I assume you don't have this flag set!
It might be worth running profiler to make sure your application is actually doing updates, not delete/inserts.
Also, are you sure it's your own application that is generating the large transaction logs... if you have a maintenance plan that does re-indexing, that can generate extremely large transaction logs.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply