Friday, February 24, 2012

Cannot shrink tempdb

HI There

I have a strange problem.

This is on Sql Server 2005 Standard Edition X64.

Tempdb ran out of space, ok cool no problem i know why we fixed it.

However temodb grew to the size of the disk when this happened. So now i just need to shrink the tempdb file on disk.

But no matter what i try it wont shrink.

The temodb data file is 29Gigs on disk. When i right click on th DB and you to tasks and go to shrink file, the GUI says that the file is 15 Gigs with 15 Gig free space available, i select the option to shrink to 15Gig. I click ok. Afterwards the file is still 29Gig on disk.

SO i ran sp_spaceused , it reports that tempdb has 29Gig free space, so i ran DBCC SHRINKFILE for the tempdb data file to 15 Gig, the command completely successfully , however afterwards the file is still 29Gig on disk.

My first problem is that management studio, thinks the file 15 gig not 29 gig, and i cannot shrink the file with management studio or tsql, even though they report that there is plenty of free space.

PLEASE HELP?

Thanx

You can start here.
http://support.microsoft.com/kb/307487|||...and don't forget to read up on this excellent whitepaper on tempdb.
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

No comments:

Post a Comment