Solved

MSDB Log file is Huge and won't Shrink

Posted on 2008-10-10
12
4,528 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
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 125 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 125 total points
ID: 22722282
DanielWilson...  Yep,  you were right all along.. dang it!  :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

770 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