Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSDB Log file is Huge and won't Shrink

Posted on 2008-10-10
12
Medium Priority
?
4,724 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
12 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22687572
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
ID: 22687733
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
ID: 22687878
Try again to Truncate it, then Shrink it.  I think it should work now.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 41

Expert Comment

by:graye
ID: 22713867
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
ID: 22714146
>>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
 
LVL 41

Expert Comment

by:graye
ID: 22716730
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
ID: 22719323
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
ID: 22719777
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
ID: 22719822
It IS the msdb system database
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 375 total points
ID: 22720349
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 375 total points
ID: 22722282
DanielWilson...  Yep,  you were right all along.. dang it!  :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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