January 14, 2015 at 11:52 am
So, I have run into a bit of a problem. I have gotten a run away MSDB log file. 184GB to be exact, and growing.
That said, now I cannot seem to do anything about it. The log_reuse_wait_desc shows LOG_SCAN and it's just stuck there.
I am at a bit of a loss as to what to do...
Any insights on this?
January 14, 2015 at 12:28 pm
What is the recovery model of the MSDB database. Is it in FULL and you are not doing periodic transaction log back up?
January 14, 2015 at 12:59 pm
It's full... And no, somewhere along the way, it looks like the transaction log backup got left out when we updated out backup scheme...
At any rate though, I need to figure out how to get it back to a state where I can shrink the log and get it running reasonably again.
Seems to alternate between the reuse reason of log_scan and active_transaction
Since I have been working on it the past couple of days, haven't seen it say anything but those two.
January 14, 2015 at 1:24 pm
If your DB is in full recovery and you're not doing log backups, either put the DB into simple recovery model or start taking log backups. If you don't do one of those, the log will keep growing until it fills the drive.
Also, if you're seeing active transaction a lot, make sure you don't have some strange long-running transaction from somewhere.
edit: turns out log_scan can show up during checkpoints, but that should be transient. Is Msdb maybe on an incredibly slow drive?
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
January 14, 2015 at 1:27 pm
Not sure what to tell you there...
select name,log_reuse_wait_desc from sys.databases where name like 'msdb%'
namelog_reuse_wait_desc
msdbLOG_SCAN
January 14, 2015 at 1:53 pm
I would take a full backup and a transaction log back up since then and see if the log file size has gone down.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply