May 22, 2012 at 11:24 am
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
May 22, 2012 at 11:26 am
Can you run a server-side trace for a bit and see what it comes up with?
Jared
CE - Microsoft
May 22, 2012 at 12:20 pm
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
May 22, 2012 at 1:58 pm
hmmmm.... this query returns zero rows.....curiouser and curiouser!
May 22, 2012 at 1:59 pm
Take a read through this: http://qa.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
May 22, 2012 at 2:06 pm
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.....?
May 22, 2012 at 2:12 pm
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
May 22, 2012 at 2:17 pm
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
May 22, 2012 at 2:19 pm
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
May 22, 2012 at 2:19 pm
I should have also mentioned that recovery_model_desc = FULL.
May 22, 2012 at 2:21 pm
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
May 22, 2012 at 2:22 pm
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
May 22, 2012 at 2:25 pm
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!
May 22, 2012 at 2:28 pm
EDIT: deleted comments... I must have missed a bunch of previous posts 🙂
Jared
CE - Microsoft
May 22, 2012 at 2:30 pm
Can you confirm that your transaction log backups are running successfully?
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply