Solved

SQL Server 2005 - How to shrink / move the log file for msdb.

Posted on 2013-11-08
3
1,422 Views
Last Modified: 2013-11-08
The log file for my msdb database is currently 15 GB. I would like to shrink it and possibly move it to a different disk drive to save space.

Is it safe to run a command like this?

backup log msdb with truncate_only

After running that command, I would then shrink the log file by going to Tasks > Shrink > Files > Log.

Are there any risks with running this?

I am also interested in knowing whether it is possible to move this database to a different drive, but I am concerned that I won't be able to do so because this is a system database.

Can anyone help?

Thanks.
0
Comment
Question by:jbaird123
3 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 39633343
"A DBCC SHRINKDATABASE or DBCC SHRINKFILE operation immediately tries to shrink the physical log file to the requested size:"
(source: http://technet.microsoft.com/en-us/library/ms178037%28v=sql.90%29.aspx)

I try one or both of these commands. If you need more info, read the link mentioned above that gives a broader description of the issue.)

As to your truncate only question...that same link says:
"Forcing log truncation breaks the log chain and leaves your database vulnerable until the next full database backup. For this reason, the TRUNCATE_ONLY option will be removed from the BACKUP statement in a future version of SQL Server. Avoid using this option in new development work, and plan to modify applications that currently use it. "

So if you do it, do a full backup immediately after.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39633507
Change the recovery mode for msdb database to Simple, this will cause the transaction log to shrink automatically. Because is on FULL and you don't do transaction log backups the log file grows forever. If you just shrink the log file but you don't change the recovery mode to Simple the log file will grow back.

If you want to keep teh recovery model on FULL then you will have to setup a regular FULL and transaction log backups for the msdb database, but you don't need that.

To change the recovery mode run this:

USE [master]
GO
ALTER DATABASE [msdb] SET RECOVERY SIMPLE WITH NO_WAIT
GO

You should do daily FULL backups though.

Also you should make sure that you have history cleanup jobs in place for backup history and jobs history.

In order to move the msdb files check here:

http://www.ryanjadams.com/2011/07/how-to-move-msdb/#axzz2OlNSfDzo

However if you have enough space after setting msdb to simple recovery you don't need to move the files anymore. It involves restarting the SQL service.
0
 

Author Closing Comment

by:jbaird123
ID: 39633550
Thanks!  This is exactly what I needed.  I changed the mode to "simple" recovery, and then I was able to shrink the log file.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 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

15 Experts available now in Live!

Get 1:1 Help Now