[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5528
  • Last Modified:

tempdb.mdf is huge

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
TRocex
Asked:
TRocex
1 Solution
 
strivoliCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Scott PletcherSenior DBACommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now