Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Shrink the size of tempdb

My tempdb database has grown up to 50GB. I want to shrink its size to 10GB using the command below and it doesn't shrink although no error is returned. Any idea ?

DBCC SHRINKFILE (tempdev, 10000);
0
AXISHK
Asked:
AXISHK
  • 2
2 Solutions
 
AddOnsIncCommented:
Shrinking Tempdb might work (depending upon the load on your system), but be aware that every time your database instance restarts, TEMPDB will automatically be recreated at the last configured size.

If you have a lot of activity going on in the system (tempdb is in use somewhere), the command will complete successfully but nothing will appear to have happened because there was no free space in the database to shrink.

Have a read of this article at MS Support - it explains what is going on and a few options you might have: http://support.microsoft.com/kb/307487
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The tempdb must be in use and that's why you can't shrink it.
Take a look in this MSDN article on how to shrink a tempdb database.
0
 
AXISHKAuthor Commented:
Do u mean I issue the following to set the size of tem[pdb first. Afterwards, reboot the server, correct ? FYI, current size of my tempdb

tempdev  : 50,028MB
templog   : 845 MB

Although I have set the size in tempdev to 10,028MB. After waiting for a while, the size is only set to 41,519. So, even though I reboot the server, it will set to this size, correct ? Any way to set to the size that I want ? Tks

Can I set it in business hour using MS SQL Server Management Studio GUI , or need to went through command below ?

  ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'tempdev', SIZE = target_size_in_MB)
   --Desired target size for the data file
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your main problem is with tempdb Log size and not the data size, so you need to run the shrink file on the log file:
 dbcc shrinkfile (templog, 10000)

Open in new window


You don't need to restart the server but the SQL Server service. That will restore the tempdb to the original size.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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