Shrink msdb database in SQL 2008 R2 Express

Posted on 2013-09-20
Medium Priority
Last Modified: 2013-09-26

I have a customer server with SQL Server 2008 R2 Express. It contains a lot of small databases (only around 2-10 MB each) except for the msdbdata.mdf. The size of that database is over 10GB and i now only have 189MB of free space left on C: so I have to shrink this database imeatitly.

My question is if the SQL-server vill create some kind of temporary file that will grow when I use the shrink command from management studio? If yes, can i in some way have this temporary file be created on a different partition?
Question by:Natverk_o_Teknik
LVL 12

Assisted Solution

Tony303 earned 750 total points
ID: 39508663
The log file of msdb will be where the "temporary file" data gets put in a shrink.
The log file will be the problem with msdb database.

You need to do a log backup of the msdb, then a full backup.

I am thinking your msdb is in Full recovery mode. Probably overkill, it could be set to simple recovery mode.

If you switch to simple mode, then the msdb should not get an excessively large log file. If your keep the Full recovery mode, you need to do regular log backups, say daily and a weekly full backup. This will manage your msdb from getting  to out of control.

Long term, get it off c drive.
LVL 43

Accepted Solution

Eugene Z earned 750 total points
ID: 39510921
it could be a bit more complicated with system databases like in your case with msdb

you need to know what file is big:
check mdf or ldf
ind before shrink
you need to know if you can do it
in any case check:

Purging MSDB Backup and Restore History from SQL Server

Moving System Databases

check size
    DB_NAME(f.database_id) AS DBName ,
    f.physical_name ,
    (f.size  * 8)/1024 MB_FileSize
From sys.master_files f

        where DB_NAME(f.database_id) ='msdb'

Open in new window


Author Closing Comment

ID: 39524763

Thanks for your answers. I ended up moving all databases to another partition.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

588 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