Tempdb not releasing the space

Question

Tempdb not releasing the space

Admingod

SSCertifiable

Points: 5997
More actions
July 22, 2014 at 10:20 am

#288052

I did try shrink the tempdb database then file. Also, restart SQL Server to release tempdb space to OS. But it did not release. I deleted the tempdb files so that it will recreate.However, it recreated with same file size. How to shrink the tempdb?
Thanks

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

Why do you want to?

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

Admingod SSCertifiable Points: 5997 More actions · Answer 2

We had unusual growth happened for one bulk operation. Which we may run again. So the tempdb suppose to be 25 GB and it grow to 50 GB. So I am hoping after shrink it will grow back to 25 GB not 50 GB.However, restarting the sql server did not release the space. Do you suggest move the tempdb and then restart the SQL Server?

Thanks

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

No. Won't do anything.

If you may run that operation again, leave TempDB as-is, unless you are major out of disk space now.

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

You can adjust the tempdb file(s) size using the following commands. The changes will be in effect the next time SQL Server starts.

--EXEC tempdb.sys.sp_helpfile --to list the logical file names, if you need to

-- names in bold below must be the correct logical names

-- be sure to specify exactly the same size for all tempdb data files

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = <nnnnnn>KB )

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev2, SIZE = <nnnnnn>KB )

--...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Admingod SSCertifiable Points: 5997 More actions · Answer 5

Thanks it worked! So here we are not moving the file to different location.

That means we are just altering the file size with following command.

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 1024 ).

So this will resize after SQL Server restart? Please correct me if I am wrong?

Thanks

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

Admingod (7/22/2014)
Thanks it worked! So here we are not moving the file to different location.
That means we are just altering the file size with following command.
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 1024 ).
So this will resize after SQL Server restart? Please correct me if I am wrong?
Thanks

Correct: that is not moving the file, it's just changing the initial size the next time SQL restarts.

If you want to move the file, you can do this:

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'x:\full\path\toew\tempdb\file\tempdb.mdf' /*, SIZE = .... */ )

And again when SQL restarts the new location will be used for tempdb, but you have to delete the old tempdb files yourself. For example, say you moved the file from c: to x: -- once SQL restarts, you have to delete the old tempdb files from the c: drive yourself.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Admingod SSCertifiable Points: 5997 More actions · Answer 7

Admingod

SSCertifiable

Points: 5997

July 23, 2014 at 10:33 am

#1731835

Thanks so much!