Solved

tempdb.mdf is huge

Posted on 2014-02-11
3
4,179 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 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:
Scott Pletcher 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 )
1

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

690 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