Solved

tempdb.mdf is huge

Posted on 2014-02-11
3
3,232 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:
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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