How to shrink the tempdb (without service re-start or re-boot) ?

Question

How to shrink the tempdb (without service re-start or re-boot) ?

onlo

SSCertifiable

Points: 5080
More actions
October 24, 2011 at 8:08 pm

#261873

My SQL Server configuration :
2008 Standard Edition (64-bit), version 10.0.2531.0
My tempdb data file has risen to 32 GB, I have tried all of the following 3 ways (without having SQL Server service re-start), but none of them could shrink the db :
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE=2048),
DBCC SHRINKDATABASE(tempdb, 10),
DBCC SHRINKFILE(tempdev, 2048)
Is it possible to shrink a 2008 tempdb without having service re-start or system re-boot ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://support.microsoft.com/kb/307487

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

onlo SSCertifiable Points: 5080 More actions · Answer 2

Already read.

The ways it proposed need service re-start.

I want to get a way that doesn't need to take SQL Server service re-start.

Is it possible ?

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 3

onlo (10/25/2011)
Is it possible ?

No.

But if you find a way let me know

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

As it says, if you shrink TempDB while it's in use, you can get corruption errors that require a restart to fix.

I suspect the better question here is why are you shrinking TempDB at all?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

I agree that shrinking tempdb ~~may not be~~ is not a good idea.

However, you could try to issue DBCC FREESYSTEMCACHE('All') before shrinking. This flushes the system cache, including internal objects stored in tempdb.

Be aware that it could have a huge impact on performance, since it flushes everything from the cache, including query plans.

If you don't have other options, you could give this a try.

-- Gianluca Sartori

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 6

Gianluca Sartori (10/26/2011)
I agree that shrinking tempdb ~~may not be~~ is not a good idea.
However, you could try to issue DBCC FREESYSTEMCACHE('All') before shrinking. This flushes the system cache, including internal objects stored in tempdb.
Be aware that it could have a huge impact on performance, since it flushes everything from the cache, including query plans.
If you don't have other options, you could give this a try.

To me this says you may as well restart the instance in a safe maner

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

MysteryJimbo (10/26/2011)
Gianluca Sartori (10/26/2011)
I agree that shrinking tempdb ~~may not be~~ is not a good idea.
However, you could try to issue DBCC FREESYSTEMCACHE('All') before shrinking. This flushes the system cache, including internal objects stored in tempdb.
Be aware that it could have a huge impact on performance, since it flushes everything from the cache, including query plans.
If you don't have other options, you could give this a try.
To me this says you may as well restart the instance in a safe maner

That's not what I wrote.

-- Gianluca Sartori