Can't shrink large Tempdb database

Question

Can't shrink large Tempdb database

sqlstad

SSCrazy

Points: 2484
More actions
October 19, 2009 at 1:57 am

#211157

I have a server where my Tempdb database has grown to be more than 33Gb in size.
When I try to shrink it to a specific size it gives me the message "MODIFY FILE failed. Size is greater than MAXSIZE".
My database in SSMS is only *MB in size but on disk it's still more than 33GB. I've already tried to shrink the database or the files eperately but that doesn't release the space to the operating system.
Does anybody know how to fix this. My disk is almost full and I can't just move the database to another disk.
Thanx in advance.

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

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

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

I don't know if this helps you, but restarting database service will reset tempdb to initial size. What is the size you want to shrink to and what is MAXSIZE of the file for your tempdb?

Also, you might consider adding a second file to tempdb on a different drive if you don't have one.

Regards

Piotr

...and your only reply is slàinte mhath

Silverfox SSC-Insane Points: 23150 More actions · Answer 2

You shouldnt have to shrink tempdb, as previously suggested restarting the sql service will recreate the tempdb database at a much lower size

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

You may or may not want to shrink tempdb - it got to be its present size for a reason. I would suggest profiler to perform some monitoring before you do. Once you make a determination that shrinking tempdb is necessary then the following link should give you everything that you need:

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

One note on this - you will be taking a SQL availability outage.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

dmigo SSC Journeyman Points: 78 More actions · Answer 4

From your error message, it sounds like you're using an ALTER DATABASE command. This will make changes that are effective next time you restart SQL Server (provided your new size is smaller than the MAX size).

Use DBCC SHRINKFILE to shrink it down to your target size without having to restart SQL Server. If you get messages about not being able to move work tables or something like that, free your procedure cache and try again. Cached plans may be tied to work tables (not defined #temp tables but just implicit sort work tables) which will prevent it from shrinking beyond the point that one of these objects exist in tempdb. Also ensure that your SSMS session hasn't performed an operation that created a temporary table which again might not let you shrink past a certain point.

However, like other posters have said, you may want to review the cause of the tempdb usage or add some disk or secondary files on another drive or else you'll probably be back to the large size again.

Tim Riley-487603 SSChasing Mays Points: 645 More actions · Answer 5

If you can, move tempdb to a different PHYSICALdrive, you'll see a big improvement in performance. Just moving it to a different drive will have no impact if the same controller is being used so it needs to be a different physical unit.

sqlstad SSCrazy Points: 2484 More actions · Answer 6

I restarted the tempdb and now it's has shrunk to its normal size.

I know about the tempdb if you place it on adifferent drive it will increase performance if it's used heavily. In this case that isn't neccesary