Solved

Shrink the size of tempdb

Posted on 2014-11-25
4
102 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 45

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 45

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
TSQL - Rollup data set 7 29
SQL Query Syntax Error 9 34
SQL Server 2008 R2 - Updating Table/Fields Documentation 3 30
Sql query 34 22
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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now