Shrink TempDB

Question

Post reply

Shrink TempDB

mvillegascuellar

SSC Eights!

Points: 881
More actions
December 16, 2010 at 9:23 am

#141792

Hi,
Recently I had a problem, the tempdb of my development server has grown more than usual, it used to be under 100MB, but now it's about 3.5GB. I'd like to know if I can shrink it without restarting my instance.
I run the sp_spaceused and this are the results:
database_name database_size unallocated space
--------------- ------------------ ------------------
tempdb 3569.99 MB 3515.84 MB
Since there is so much unallocated space I executed
USE [tempdb]
GO
DBCC SHRINKFILE (N'Templog2' , 0, TRUNCATEONLY)
GO
and
USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb' )
GO
But it still remains the same size. I'd like to know why is this happening? and if it is possible to shrink it without restaring my instance?

Viewing 12 posts - 1 through 11 (of 11 total)

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Check this

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

Thank You,

Best Regards,

SQLBuddy

mvillegascuellar SSC Eights! Points: 881 More actions · Answer 2

Hi I've already tried the methods that do not requiere to restart my instance, but non of them worked, I want to know why I can't shrink it without restarting my instance if I can se I have a lot of unallocated space.

SkyBVI SSCoach Points: 15128 More actions · Answer 3

@mvillegascellar

I had the similiar case as you and i use this code to shrink the tempdb.

USE tempdb

GO

DBCC SHRINKDATABASE(N'tempdb', 10, TRUNCATEONLY)

DBCC SHRINKDATABASE(N'tempdb', 10)

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

mvillegascuellar SSC Eights! Points: 881 More actions · Answer 4

mvillegascuellar

SSC Eights!

Points: 881

December 16, 2010 at 10:38 am

#1263576

No Luck, sorry

The tempdb still remains the same size.

SkyBVI SSCoach Points: 15128 More actions · Answer 5

did you try to shrink it thru SSMS gui?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

TRUNCATEONLY doesn't help much. As long as tempdb is in use, you can't shrink it no matter which method you follow. Try to kill the spids using Tempdb and then try to shrink it.

Thank You,

Best Regards,

SQLBuddy

mvillegascuellar SSC Eights! Points: 881 More actions · Answer 7

I disconect every session apearing in the sp_who2 which had a conection to tempdb and did the shrink but still no luck.I also tried to do it by the SQL Management Studio, but I get the same results.

I want to know why if I have so much space unallocated in my tempdb, I can't reclaim it?

SkyBVI SSCoach Points: 15128 More actions · Answer 8

If you are unable to do anything, why dont you restart your sql server?

More options :-

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/7b45f0de-2aa3-4de0-930b-d9d0fe931b3a

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Adiga One Orange Chip Points: 27284 More actions · Answer 9

The procedure cache makes some objects remain in the tempdb. I had a similar issue[/url] and clearing the procedure cache helped me reduce the tempdb size.

Note: Please do not clear the procedure cache on Production systems unless you are very sure.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

mvillegascuellar SSC Eights! Points: 881 More actions · Answer 10

@Adiga you're the man, thank you so much for helpping me understand why I could not shrink my tempdb. I really apreciate your help.

Finally after running the

DBCC FREEPROCCACHE

in my dev enviment I could shrink my tempdb.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 11

Try reading the following articles / blog posts to determine additional methods or items to check to determine why tempdb is not shrinking.

http://www.sqlservercentral.com/blogs/adventuresinsql/archive/2009/12/17/how-to-shrink-tempdb-in-sql-2005.aspx

and

http://www.sqlservercentral.com/articles/tempdb/65919/

http://www.sqlservercentral.com/articles/Editorial/71888/

http://www.sqlservercentral.com/blogs/steve_jones/archive/2009/11/30/what-s-using-space-in-tempdb.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]