Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server 2005 SP2 system with a single database that is 42gigs in size.

It is a modestly active database that sees on average 25 transactions per second.

The database is configured in Full recovery model and we perform transaction log backups every hour. However it seems to be pretty random at some point during the day the log backup will go from it's average size of 15megs all the way up to 40gigs.

There are only 4 jobs that are scheduled to run on the SQL server and they are all typical backup jobs which occur on a daily/weekly basis.

I'm not entirely sure of what client activity takes place as the application servers are maintained by a different department.

Is there any good way to track down the cause of these log file growths and pinpoint them to a particular application, or client?

Thanks in advance.

share|improve this question
add comment

3 Answers

You can use fn_dblog() to analyze the log content.

share|improve this answer
add comment

Do you have a generic Update statistics job in your maintenance plan? This can cause your Log to get large. You might want to consider adding some logic like using sp_updatestats

share|improve this answer
add comment

There is a very large operation which is happening which is causing the large transaction log. You can use the function that Remus posted to look at the log to what's going on.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.