Transaction log backups are huge

Question

Transaction log backups are huge

ChrisM@Work

SSC Guru

Points: 186127
More actions
January 31, 2008 at 5:18 am

#120305

Hi
The space allocated for my tran log is about 2gb of which 290mb is currently used. Tran log backups are run hourly and the size of the generated file seems ok, but from time to time, backup generates a huge file of say 20-30gb. I'd like to find out why this happens as it could cause a backup to fail if it happens twice between purges to tape. I'm not a DBA - this is a favour for my boss - any suggestions welcome.
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

A good place to start would be to track what time of day it seems to be growing. A Perfmon log could help with that; SQLServer : Databases and use one of the Log counters such as Percent Log Used.

Not that this is what's happening but as an example, if it's growing consistently at 3:00am everyday then it might be worth looking to see if a maintenance plan is being used to rebuild indexes. Depending on the number of indexes and size of the database that operation can cause significant growth since that task rebuilds all indexes and index rebuilds are a logged operation.

Also, seems odd that your transaction log is 2GB but your Log backups are 20-30GB. Are you shrinking the log back down manually?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What recovery mode is your database in? If bulk logged, are you doing any bulk operations (bcp, bulk insert, select into, index rebuilds)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Todd & Gail

I'm looking into this now. Index rebuilds are 3am. It's an odd system, most columns are covered, and one of our accounts tables has 30m rows. Will reply to other questions shortly.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Recovery model is "Full"

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Are index rebuilds really necessary nightly? As Paul Randal pointed out in another thread recently, nightly is usually WAY overkill, and just creates a lot of activity for little or no gain. Just like the OS, some amount of fragmentation is normal and ultimately does NOT impede performance; only once you go past a certain point does performance start to suffer.

Have you tried simply skipping the index rebuild for a night or two and see if the 20GB stops happening?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Good point Matt, I don't know the answer but your suggestion is pretty easy to check out. The system is Lawson which I'm finding out is kinda "pernickity" and quirky (a "quirky" BTW is what you get if you cross a tiny but very tasty quail with a huge but bland turkey).

Many thanks

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

persnickety is a kind word to describe Lawson....:) But still - you may find it doesn't need that much maintenance.

Actually - I thought QWIRKY was the layout of your keyboard after beating your head on it on those days when Lawson crashes for the fifth time:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ian Yates SSCoach Points: 19738 More actions · Answer 8

It will definitely be some sort of index rebuild. Another thing it could be is if you run a command similar to (but much larger in scope)

update myTable

set colA = colA

ie, you update all of a table's rows and don't use a where clause to filter out records that haven't changed. I saw this with a very frequent systemA -> systemB data transfer where 99% of the data didn't change but they did the update anyway. If the update was run multiple times as part of a nightly batch then you'd also see your behaviour.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Matt Miller (1/31/2008)
persnickety is a kind word to describe Lawson....:) But still - you may find it doesn't need that much maintenance.
Actually - I thought QWIRKY was the layout of your keyboard after beating your head on it on those days when Lawson crashes for the fifth time:)

Haha Matt, looks like you've BTDT, got the Tshirt!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Ian Yates (1/31/2008)
It will definitely be some sort of index rebuild. Another thing it could be is if you run a command similar to (but much larger in scope)
update myTable
set colA = colA
ie, you update all of a table's rows and don't use a where clause to filter out records that haven't changed. I saw this with a very frequent systemA -> systemB data transfer where 99% of the data didn't change but they did the update anyway. If the update was run multiple times as part of a nightly batch then you'd also see your behaviour.

Yep, we've got some ETL into the "old" system which is "arguido".

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden