MSDB Log file is Huge and won't Shrink
Posted on 2008-10-10
The on my MSDB database the transaction log is 71GB, although the database itself is only 223MB.
How can I shrink it to a more manageable size?
Orginally it was about 30GB so I setup backup maintenance plan for databse and log but the Log File grew to 71GB and backups failed due to lack of disk space (theres now only 41 GB free!)
I tried right-clicking on the msdb file in Management Studio but it would only shrink it a couple hundred MB if that
I also tried the code below to no avail&
BACKUP LOG msdb WITH TRUNCATE_ONLY
DBCC SHRINKFILE(N'msdblog', 1)
Bit I get Cannot shrink log file 2 (MSDBLog) because all logical log files are in use.
There are some pretty big Agent Jobs that run once a day which I guess causes the increase, but I would be happy to truncate the log as I wont need to restore backups.