Solved

Shrink the size of tempdb

Posted on 2014-11-25
4
111 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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 50

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Getting local user timezone in Sql Server 5 40
How to keep a record with the highest value 3 59
How to add an Index to a date/time field? 15 42
SQL Query 20 22
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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