7

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

Tempdb has four data files and 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 and is still showing the initial size.

This is also where the "Available free space" is shown as a negative number. The only workaround 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 and knows the root cause/permanent solution for it please?

Improve this question
3
  • 2
    1 - don't shrink tempdb. 2 - are you getting any errors that would prompt you to do this?
    – JNK
    Mar 27, 2013 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.
    – Hannah Vernon ♦
    Mar 27, 2013 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, 2013 at 19:05

3 Answers 3

Reset to default
2

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 ?

Improve this answer
2
  • 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, 2013 at 13:29
  • If this is a bug reported in SP1 it seems that is not resolved, since our customer has installed Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) and negative free space is still shown for tempdb.
    – Junior Mayhé
    Jan 16, 2015 at 12:00
0

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)

Improve this answer
-1

You can simply change the initial size of the files and accordingly shrink the file after that. The issue is that updated size is not reflecting in the file size and hence the available space is in negative. Hope this helps.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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