asked on

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.

SStory

"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.

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jbaird123

ASKER

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