?
Solved

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

Posted on 2013-11-08
3
Medium Priority
?
1,507 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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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