0

We have an problem with our transaction log (2008 R2) growing very quickly (despite full backups). In SQL profiler, I ran a trace capturing all Insert, Delete and Update statements with rowcount and they were all very low.

Many of the apps running on the server use SP's and Rowcount is set to off, so I can't see which ones are doing massive update, insert, delete operations (I know there are a few, but there are hundreds of SP's, many of which are part of 3rd party applications). What's the best way to track these down?

I realize there are other issues except Insert, Update, Delete that can cause the log to grow or not be truncated, but I'd like to rule these out (if I can)

Any suggestions?

TIA

Mark

mark1234
  • 1,110
  • 2
  • 24
  • 41

2 Answers2

0

transaction log (2008 R2) growing very quickly (despite full backups)

That would be because full backups do not truncate the log. Only LOG backups truncate the log. This is known as Myth 30-05:

30-05) a full or differential backup clears the log

No. A log backup includes all the log since the last log backup – nothing can change that – no matter whether that log was also backed up by a full or differential backup. I had a famous argument on Twitter last year and wrote this blog post as proof: Misconceptions around the log and log backups: how to convince yourself. In the FULL or BULK_LOGGED recovery models, the only thing that clears the log is a log backup.

You need to look first at the usual place to investigate log growth: log_reuse_wait.

Community
  • 1
  • 1
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

yup like remu mentioned, lol. full backup does not clear your tlog. you need to take a tlog backup to reduce the size of the tlog.

firstly, how often do you backup your tlogs? or do you even back them at all? the only way to keep the tlog size in check is to make sure the tlog is in simple recovery mode (this is not recommended, you cannot restore your database to a point in time, in case of a disaster)

OR

regularly as part of maintenance backup the tlogs and keep them nice and tidy. still there are times when the applications do massive transactions once a while, which can make the tlog grow to large sizes, but thats why its important to have some kind of maintenance jobs that would back the tlog once it reaches like 80% or 85% of the current size.

Jimmy John
  • 89
  • 1