November 11, 2010 at 4:11 am
The log seems to have peaked at 50GB and looking at the % used it's slipped down to using about 35GB so I will keep an eye on it. Thanks for your help.
November 11, 2010 at 4:50 am
GilaMonster (11/11/2010)
That means you have a long-running transaction open that's keeping the log large. You need to identify and fix that and whatever caused it before you decide what to do about the log.
I have found a transaction that has been running for an hour and appears to be blocking other transactions. I'm unsure as to how to go about finding what it is or how to stop and or prevent it continuing?
In the Process details of the transaction it gives the last T-SQL command as "exec purge_stat1_proc"? Being run by the SQLAgent under local SYSTEM
November 11, 2010 at 5:15 am
It's a job. Check SQL Agent's running job list.
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
November 11, 2010 at 7:28 am
GilaMonster (11/11/2010)
It's a job. Check SQL Agent's running job list.
Ok, so it turns out there were half a dozen jobs created to purge status/events that were just taking forever to complete. These jobs have now been removed and the log is actually shrinking by itself!!
Thank you everyone for your help and patience!
November 11, 2010 at 7:32 am
If the actual log file is shrinking, it means you have auto_shrink enabled. Turn it Off! Autoshrink should never, never, never be enabled.
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply