How to shrink the tempdb.mdf and master.mdf

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to shrink the tempdb.mdf and master.mdf

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-29 : 14:19:25

I have a install of SQL 2005 that is about 5 months old. The tempdb.mdf and master.mdf files have ballooned to a huge size almost filling my hard drive. How do I reset them to a reasonable size?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-29 : 22:57:35

How big they are? Master db shouldn't grow much unless you create user tables in it, and sql will recreate empty tempdb when you restart sql. You may like to move tempdb to bigger disk.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-30 : 10:03:12

tempdb.mdf is over 18G. Is there w way to purge it?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-30 : 10:06:31

http://support.microsoft.com/kb/307487

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-30 : 10:18:33

That's great. I will use teh info in that KB artical. :)

Is there something i can do about the sizs of the master.mdf? It is 4G. I never use it or create files there. Is there som regular maintence i should be doing to keep it's size in check?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-30 : 10:28:10

use sp_spaceused on each table to see how large it is.
http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx

and then tell us the sizes for largest tables

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-30 : 13:54:16

I tried using:

EXECUTE sp_spaceused 'master.mdf'

To get the file size of master.mdf and got the error:
The object 'master' does not exist in database 'master' or is invalid for this operation

When I look at the fils size in Windows Explorer it is master.mdf is 1.4G and tempdb.mdf is 18.1G.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-01-30 : 13:57:54

just run

EXECUTE sp_spaceused

From the master database

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-30 : 14:04:42

Database size = 4063.13 MB
Unallocated space = 4004.13 MB

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-30 : 14:11:08

what did you do to that database?

what is your minimun size for the master database? you can't shrink the db below that.

if it's normal then try shrinking the master db and see if it comes back up to that huge size.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-30 : 14:15:07

I am totally new at this. How do I find the minimun size for the master database? and how do i shrink it to that minimum size?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-30 : 14:37:10

it's the initial size in the database properties.
to shrink it use DBCC shrinkfile command. look it up in BOL = Books Online = SQL Server help

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-30 : 14:46:17

Thank you. I think i can get things back to normal now.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-01-30 : 15:10:54