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

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.
jbaird123Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ZberteocConnect With a Mentor Commented:
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
 
SStoryCommented:
"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
 
jbaird123Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.