Transaction log backup size

Question

Transaction log backup size

Alan Spillert

SSCommitted

Points: 1964
More actions
April 20, 2009 at 9:11 am

#208396

Our production SQL Server 2000 system (9 GB database) runs hourly transaction log backups and a full backup every night. Last Sunday the application was idle during the day - our batch jobs all run at night, and the application logs showed no user activity.
The TRAN LOG showed sizes changing between 79KB and 19,152KB all afternoon. Noon, 2PM, 4PM, and 6PM are 79KB, and odd hour backups are 19,152KB (with some variation).
Any idea why? Is there something in SQL Server that could cause this ? or am I missing something in my application?
Thank you,
Alan

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

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

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

If you T-Log backup size is increasing then I would check to see if anyone is doing batch imports, Index rebuilds, jobs etc. SQL Server cannot backup what has been marked for truncation already and is not hardended back to the hdd.

Thanks.

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

tosscrosby SSCoach Points: 18045 More actions · Answer 2

Mohit (4/20/2009)
SQL Server cannot backup what has been marked for truncation already and is not hardended back to the hdd. Thanks. Mohit.

What does that mean?

-- You can't be late until you show up.

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 3

Alan Spillert (4/20/2009)
Is there something in SQL Server that could cause this ? or am I missing something in my application?
Thank you,
Alan

It's most likely that you are missing something in your 'application' in the broadest sense, though it could be SQL Agent or anything else the application uses. Start a profiler session and see whats going on.

I don't know of anything in SQL Server that would do this that wouldn't have been set up by someone. E.g. a maintenance plan that performed index rebuilds might do this but someone would need to have set that up in the first place.

.

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 4

I have to agree with Tim here - you are missing something from the application. Could be a monitor process, a scheduled job - or even a user that just happened to be working on that Sunday.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

tj_beavis Newbie Points: 3 More actions · Answer 5

Its could be that you are running a optimisation step in your maintenance plan - defgmenting indexes - this can cause the transaction log to grow to up to twice the size iof the database.

Alan Spillert SSCommitted Points: 1964 More actions · Answer 6

Deep in our application we have a set of background jobs that send users email and update data based on application data conditions. These email/update jobs, run from the application (Click Commerce eResearch), not from SQL Server, were causing the odd fluctuation of transaction log sizes.

Thank you to everyone for your assistance.