Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSDB Log file is Huge and won't Shrink

Posted on 2008-10-10
12
Medium Priority
?
4,774 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

886 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