Solved

MSDB Log file is Huge and won't Shrink

Posted on 2008-10-10
12
4,472 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:RobertEnglish
  • 4
  • 4
  • 3
12 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
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
 

Author Comment

by:RobertEnglish
Comment Utility
Thanks Daniel

Now I've changed MSDB to SIMPLE Recovery mode what do I do about the 70GB transaction log?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
Try again to Truncate it, then Shrink it.  I think it should work now.
0
 
LVL 41

Expert Comment

by:graye
Comment Utility
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
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
>>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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 41

Expert Comment

by:graye
Comment Utility
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
 

Author Comment

by:RobertEnglish
Comment Utility
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
 
LVL 41

Expert Comment

by:graye
Comment Utility
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
 

Author Comment

by:RobertEnglish
Comment Utility
It IS the msdb system database
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 125 total points
Comment Utility
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
 
LVL 41

Assisted Solution

by:graye
graye earned 125 total points
Comment Utility
DanielWilson...  Yep,  you were right all along.. dang it!  :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now