Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Please help as this issue is driving me nuts...

Tempdb has four data files & one log file. They are all located on the same dedicated drive.

When I right click on tempdb in SQL Server Management Studio (SSMS) to view Properties --> General...I'm seeing "Size" as ~65000 MB with "Space Available" as ~64000 MB. However, when I try to shrink the files via the GUI, I see that the "currently allocated space" hasn't changed & is still showing the initial size.

This is also where the "Available free space" is shown as a negative number. The only work around I have found so far is to restart SQL Server to flush out tempdb, but I can't keep on doing this.

Has anyone ever come across this & know the root cause/permanent solution for it please?

Thanks in advance!

share|improve this question
2  
1 - don't shrink tempdb. 2 - are you getting any errors that would prompt you to do this? –  JNK Mar 27 '13 at 18:57
    
TempDB is at "~65000 MB" because that is how big it has needed to be at some point since the last restart. If that is how big it needed to be, then I imagine there is a reason for that. Shrinking it will only cause unnecessary I/O, during the shrink, and in future when it grows again. –  Max Vernon Mar 27 '13 at 19:00
2  
So really you should consider tempdb to be a part of the base SQL install and that drive space should be permanently allocated to tempdb. –  JNK Mar 27 '13 at 19:05
add comment

migrated from stackoverflow.com Mar 27 '13 at 19:00

This question came from our site for professional and enthusiast programmers.

2 Answers

Open Query Analyzer Run DBCC UPDATEUSAGE('tempdb') After it is done, check temdb value and it should show the true values.

Also, this might help you.

Also, check if there are any open transactions using dbcc opentran or sp_whoisactive (Adam Mechanic's SP)

This was a bug in SQL Server 2008 SP1 described here.

Out of curiosity, is it causing any performance problems ?

share|improve this answer
    
I'm pretty sure this is buggy behaviour. I saw this same thing on one of our instances (2008 R2... I think RTM at the time) before when I was shrinking tempdb files. Attempting to shrink when it was in this state actually froze the instance. I think the safest thing is to restart the instance at a convenient time. The problem hasn't come back since then, and I'm not about to start messing around on prod trying to reproduce it. –  Jon Seigel May 27 '13 at 13:29
add comment

Well, you have to check your tempdb settings and overview it usage by your server. Microsoft gives the primary advices for managing the tempdb and optimazing it's peformance (See http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx). Try apply them and see the result (in my case it helped to optimize my 17GB databse to 4GB)

Also you can use sp_spaceused proc it temp database. It will tell you which table consume most of the space in the data file. You can also use Profiler to detect the file auto grow and log file auto grow. If it often changes - you tempDb automaticly will take additional space, and you'd probably have to change the logic of using your #tmp tables.

And shrinking tempdb on working server, it can lead you to some troubles with the queries on the other databases (do it only in single mode)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.