MSDB Log file is Huge and won't Shrink

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.
RobertEnglishAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Daniel WilsonConnect With a Mentor Commented:
MSDB contains, afaik, the logs from the execution of jobs scheduled with the Agent.  That logging of data, presumably, could increase the size of the LDF file.

Most of us don't run enough jobs with enough logging to worry about it.  And a default installation has it at Simple recovery, I believe. That would be the main reason in doesn't require daily maintenance.  (Of course it should be backed up regularly so you don't lose the jobs / maintenance plans, etc.)

There's usually no business reason to keep the logs of the jobs up-to-the-minute in case of disk failure.  Which means Simple recovery makes sense for MSDB.
0
 
Daniel WilsonCommented:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23360888.html should be some help.

Honestly ... I'd try changing MSDB to Simple recovery mode and this wouldn't be an issue.  MSDB shouldn't really need all the log stuff saved anyway.
0
 
RobertEnglishAuthor Commented:
Thanks Daniel

Now I've changed MSDB to SIMPLE Recovery mode what do I do about the 70GB transaction log?
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Daniel WilsonCommented:
Try again to Truncate it, then Shrink it.  I think it should work now.
0
 
grayeCommented:
There may be some good business reason to leave the recovery model at Full...  we can't really advise you on that without more information.
Take a look at the following article.... it explains why the log file grows, how to fix it, and how to prevent it from happening again.   There is also a section that helps diagnose why a shrink operation might fail
http://home.hot.rr.com/graye/Articles/SQL_LogMaintenance.htm 
0
 
Daniel WilsonCommented:
>>There may be some good business reason to leave the recovery model at Full.

On a user DB I would agree.

But why on MSDB?
0
 
grayeCommented:
DanielWilson...
In my opinion, there's not enough information for a good recommendation.   Besides does "MSDB" really mean "MSDE" ?   I wouldn't make that assumption... look at the part about "agent jobs".   That makes me wonder...
0
 
RobertEnglishAuthor Commented:
Thanks guys

I've changed it to SIMPLE and set a daily database backup job
On Monday the msdb log file was 13GB
On Tuesday the msdb log file was 33GB
Today the msdb log file was 12GB

I'll monitor it throughout the week to see if it calms down to a regular size.

In meantime if there's anymore info you want me to provide let me know
0
 
grayeCommented:
Just a question...   what is this "msdb" database?
For example, in the SQL Server world, there is a "system database" called "msdb".   It's a required part of the SQL Server and does not grow like you've described, nor normally require daily maintenance.   I had made the assumption (perhaps incorrectly) that you were talking about a user database that you created... and not the system database.
If you *are* talking about the system database with that name, then whew... you've got something very strange going on!
0
 
RobertEnglishAuthor Commented:
It IS the msdb system database
0
 
grayeConnect With a Mentor Commented:
DanielWilson...  Yep,  you were right all along.. dang it!  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.