Solved

tempdb.mdf is huge

Posted on 2014-02-11
3
2,856 Views
Last Modified: 2014-02-28
I have serious problem..my TEMPDB.mdf grew up to 50GB on MS SQL Server 2005 so I am thinking of shrinking the tempdb.mdf but in general opinion, they don't recommend trying to shrink tempdb so I dont know why? I know it is best solution to restart sql service then tempdb is created again whenever sql server is restarted but I could not talk to the responsible person of this server so far to restart it so what is the other best solutions to reduce the tempdb.mdf or ldf files to lower size? I don't much space on this Server..disk size seems "0" in size..Is restarting the SQL Server the only way to release the space. Can you please help me to release the space ? Is there any way i can do it without restarting the SQL service.
0
Comment
Question by:TRocex
3 Comments
 
LVL 19

Expert Comment

by:strivoli
ID: 39853087
I don't know any way without stopping.
I suggest you stop SQL and move the tempdb to another drive.
Stop/Start takes a few tens of seconds. Move the tempdb takes a few minutes.
If you do that out of office time, there should be no problems.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39853692
you can keep on shrinking the mdf files, shrinking depends on how many connections actually uses the tempdb at that moment; some times i need to run the shrink statement 20-30 times in order for it to shrink.
If you are so worried abt the size of the tempdb, you can also set the maximum size of the file too.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39853875
You can free the space with a restart, but it could grow back to that size again if the process that made it grow before runs again.  In general, if tempdb needs to grow, you need to get space to accommodate it.

In the meantime, since it's very bad for performance to do autogrow, I'd suggest pre-allocating as much space as you can for tempdb file(s).

BEFORE THE RESTART, divide the available space up, then allocate each tempdb data file exactly the same.  Use ALTER DATABASE commands to set the file size you want.  

For example, if you can afford 24GB, and have two data files, give each file 12GB, like this:

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 12GB )
-- change "tempdev2" to whatever your logical name for the file is
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev2, SIZE = 12GB )
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard 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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now