?
Solved

tempdb.mdf is huge

Posted on 2014-02-11
3
Medium Priority
?
4,631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 20

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:
Scott Pletcher earned 1500 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 )
1

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

765 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