Shrinking the system database

Question

Shrinking the system database

Juanita

SSCertifiable

Points: 5300
More actions
July 13, 2007 at 10:59 am

#68857

HI. I recently inherited a sql server. As I was getting familiar with it, I noticed the master database was HUGE(like 772 mb) I found 2 user tables that were placed in there. I just deleted the 2 user tables. Amazingly, I now have 758 mb free. it is only using about 14 mb now. I would like to shrink the system db to around 60 MB. From reading BOL, I can do this while users are on the server? Can someone verify that for me? I have never had to shrink the system database before and am a little nervous about doing it. I was going to use EM to do this.
Thanks so much !!
Juanita

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

But why do you want to shrink the system databases. If you find any wanted tables then check and remove them. There will be no huge space occupied by your system DBs unless they have user tables and data. Check the size of the databases they will be in MBs not more than that shrinking them will anyhow not help you to reduce disk space.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

Because it's 3/4 of a gig of disk space that will never ever be used again if things are done right.

Juanita,

You are correct in being nervous... As you know, if you mess up the Master database, you'll have big problems. So... make sure you do full backups first AND make sure they are restorable on another machine. Make sure the full backups include the Master, MSDB, and Model databases and have at it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 3

dont ever shrink the system dbs except tempdb......but i wonder how your master is 750MB it shud be less than 100MB....check and remove all user tables if any from the master.......

[font="Verdana"]- Deepak[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

I'm thinking you didn't read the original post and there's nothing wrong with shrinking Master when this type of mistake has been made.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

If there is 772MB free then you can shrink the master DB but as Jeff suggested take a backup of the database before that.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com