unexpected transaction log growth

Question

unexpected transaction log growth

sjcubehead

SSC Enthusiast

Points: 179
More actions
July 29, 2013 at 9:29 am

#296186

Hey all,
we are running SQL 2005 Ent. x64 in production. The transaction log of one of the databases suddenly grew by 25GB in one hour earlier this morning. I noticed it when I checked the hourly transaction log backup files - it was over 25GB between 9am and 10am. Normally, the transaction log backup file is around 1GB around the same time. The size of transaction log file grew from about 20GB to around 50GB during the same time period. How do I figure out what was going on during that time? What made the log grew that much?
Thank you in advance for any advices on this!!

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

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

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

I have a server side trace running 24/7, so I dump the trace file(s) to a table, then query for a specific date range and sort by duration desc for a start.

Without a trace, it's harder to figure out. Any scheduled jobs running then ?

Terry Troisi SSC-Addicted Points: 430 More actions · Answer 2

The only thing that causes a transaction log to grow are transactions within the database. Something was occurring during that time period. I'd check with everyone/everything that has access to the DB for clues.

The previous poster was correct is his idea to set up a trace so at least you can catch what's occurring next time.

sqlman63 SSC-Addicted Points: 499 More actions · Answer 3

Replication and mirroring can also cause growth. if the connection is lost, the logs will grow.

durai nagarajan SSCoach Points: 15775 More actions · Answer 4

sqlman63 (7/30/2013)
Replication and mirroring can also cause growth. if the connection is lost, the logs will grow.

In source server? what will it do to increase it?

Regards
Durai Nagarajan

homebrew01 SSC Guru Points: 55539 More actions · Answer 5

You may have open transactions. run dbcc opentran

SQLAli Ten Centuries Points: 1079 More actions · Answer 6

We can also determine whether there are open transactions or not.

select log_reuse_wait,log_reuse_wait_desc, * from sys.databases

check the log_reuse_wait_desc if there is any cross verify with dbcc opentran(). If there are really open transactions then we check the command by using dbcc inputbuffer(SPID). As it is already completed i hope we cannot troubleshoot without trace.

Thanks,
I’m nobody but still I’m somebody to someone………….

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Here is an article that can help you find those growths

http://www.sqlservercentral.com/articles/Log+growth/69476/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Smith SSCertifiable Points: 5941 More actions · Answer 8

I would also try to get some before and after stats on things such as table sizes (data and indexes) in order to help narrow down what activity is affecting what parts of your DB and causing it to grow so much.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

You can also check the default trace to get some (albeit limited) information. For autogrowth events, it records the login and application names, which is sometimes helpful. Especially if the growth was caused by a SQL Server agent job, you can track down which one as the job is identified in the ApplicationName column.

I find this is often useful to see whether it was a .Net SQL provider, SQL Server Management Studio from a particular login, or an Agent Job that caused the growth.

Hope this helps!