Solved

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

Posted on 2013-11-08
3
1,480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 27

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

Industry Leaders: 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!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

705 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