Solved

Shrink the size of tempdb

Posted on 2014-11-25
4
110 Views
Last Modified: 2014-12-10
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
Comment
Question by:AXISHK
  • 2
4 Comments
 

Assisted Solution

by:AddOnsInc
AddOnsInc earned 250 total points
ID: 40464418
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
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40464421
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
 

Author Comment

by:AXISHK
ID: 40465852
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
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40466297
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

680 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