Transaction Log Backup Size

Question

Transaction Log Backup Size

Steve

SSCommitted

Points: 1844
More actions
April 21, 2008 at 6:57 am

#380080

Hello All
I have a question regarding transaction log backup size. Here is the skinny.
I have a db for which I am running a tlog backup every 10 minutes. Throughout the day and through my peak db usage periods my Tlog backup size remains pretty constant. Early in the wee morning hours when I am sure that there is little to no db activity, the backup log size increases by around 150%. The transaction backup size taken immediatly this spike in growth goes back to normal. This occurs at exactly the same time every day.
My backup schedule is as follows:
1. Full backup at 5am daily.
2. 630 am to 4am on the following day transaction log backup every 10 minutes.
The transaction log backup growth size is seen around 310am every day.
I have check for any jobs that would be running on that database at that time and have not found any.
So here are the questions.
What makes a transaction log grow? If updates, inserts and deletes are the only cause, what would you recommend as a method that I could use to trace down this mysterious growth pattern?
Thanks in advance for your help.
Steve

Viewing 6 posts - 1 through 5 (of 5 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Your log may have grown due to index rebuilding. When you checked the jobs, did you also consider the ones that rebuild your indexes?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Steve SSCommitted Points: 1844 More actions · Answer 2

There are no SQL Server Agent Jobs running during this time frame.

Are there other areas where jobs can be scheduled and or run?

Steve

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

There could be a Windows or third-party scheduler running stuff on your SQL Server. Try setting Profiler to start at 3:09 in the morning and capture what happens.

John

Steve SSCommitted Points: 1844 More actions · Answer 4

So I am accurate in saying that only Index rebuilds (thanks Andras), Deletes, Inserts and Updates cause the transaction log to grow, right?

Steve

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Steve

Anything that changes your database in any way will cause your log to grow: DML statements, DDL statements, reindexing, TRUNCATE TABLE, bcp, BULK INSERT....

John