Shrink msdb database in SQL 2008 R2 Express

Hi!

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?
Natverk_o_TeknikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tony303Commented:
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.
0
Eugene ZCommented:
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
http://www.mssqltips.com/sqlservertip/1727/purging-msdb-backup-and-restore-history-from-sql-server/

Moving System Databases
http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.105).aspx


check size
Select 
    DB_NAME(f.database_id) AS DBName ,
    f.Name,  
    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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Natverk_o_TeknikAuthor Commented:
Hi!

Thanks for your answers. I ended up moving all databases to another partition.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.