Database Transaction Log growing at 3-4GB per hour!!

Question

Post reply

Database Transaction Log growing at 3-4GB per hour!!

MarkThornton

SSCommitted

Points: 1731
More actions
May 22, 2012 at 11:24 am

#261662

Dear Everyone,
One of our key production databases has suddenly had its transaction log grow at an out of control rate - averaging 3GB growth per hour for the last 4 hours! My VM colleagues have helped me by increasing available diskspace, but this will only buy me some time.... I need a solution fast!
My working theory is that the website that sits on top of the Db has an out of control process that is asking the Db to do stuff repetitively. However, I have no idea what that process might be.
I have posted on that website that I may have to take the site down for emergency maintenance and plan to do that if things haven't improved by 10pm tonight.
But any advice from the online community would be greatly appreciated.
Thanks in advance
Mark Thornton

Viewing 15 posts - 1 through 15 (of 32 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Can you run a server-side trace for a bit and see what it comes up with?

Jared
CE - Microsoft

sroos Mr or Mrs. 500 Points: 541 More actions · Answer 2

Try to see which transaction(s) are taking up the space and kill it if possible..

You can find out with the DMV's with something like this (ordered by log space used)

Select B.session_id,A.transaction_id,C.database_transaction_log_bytes_used,C.database_transaction_begin_time,

DATEDIFF(Second,C.database_transaction_begin_time,getdate()) TimeTaken_In_Seconds,B.HOST_NAME,B.program_name,B.login_name,

b.login_time as UserLoginTime

from sys.dm_tran_session_transactions A

Join sys.dm_exec_sessions B On A.session_id=B.session_id

Join sys.dm_tran_database_transactions C On A.transaction_id =C.transaction_id

ORDER BY

c.database_transaction_log_bytes_used

MarkThornton SSCommitted Points: 1731 More actions · Answer 3

hmmmm.... this query returns zero rows.....curiouser and curiouser!

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

Take a read through this: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

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

MarkThornton SSCommitted Points: 1731 More actions · Answer 5

Important piece of information: last night's Full Backup failed, at 00:30 in the morning.

I presume this means that more data needs to be stored in the Transaction Log.....?

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

No it does not. If your log backups are failing or the full backup is still running however...

Please read through that article and identify what is preventing the log from being reused. Until we have that information we are all guessing.

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

MarkThornton SSCommitted Points: 1731 More actions · Answer 7

log_reuse_wait_desc = LOG_BACKUP

I have increased the frequency of log backups to every 15 mins, instead of every hour.

Is that a step in the right direction?

Best wishes

Mark

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

Check that your log backups are running then. if the log has been growing, it suggests that the log backups haven't been running at all.

Wait for the next log backup (or run one now), then check again and see if there's another reason.

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

MarkThornton SSCommitted Points: 1731 More actions · Answer 9

I should have also mentioned that recovery_model_desc = FULL.

MarkThornton SSCommitted Points: 1731 More actions · Answer 10

One of the hourly backups failed in the middle of the night, but all have run OK since then.

I have changed to every 15 mins, and the first one of those has just run OK.

Best wishes

Mark

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

If the log backups are succeeding, that's not the root cause. Check again, see if there's another reason for the log to not be reused.

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

MarkThornton SSCommitted Points: 1731 More actions · Answer 12

I have re-run this query:

SELECT name, recovery_model_desc, log_reuse_wait_desc

FROM sys.databases

WHERE name = @DatabaseName

Same results: recovery_model_desc = FULL, log_reuse_wait_desc = LOG_BACKUP

Puzzling!

Jared SSC Guru Points: 61793 More actions · Answer 13

EDIT: deleted comments... I must have missed a bunch of previous posts 🙂

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Can you confirm that your transaction log backups are running successfully?