Solved

Shrink the size of tempdb

Posted on 2014-11-25
4
109 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 48

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 48

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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