[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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