none
TempDB reporting wrong file size?

    Question

  • I have a TempDb that is reporting 16GB (the correct desired size) in SQL Managmenet Studio, but it is taking up 400GB on the disk.  Likewise, all indications in Management Studio say that my Temp Log file is 2GB (as I would like) but on the disk iteself, the file is taking up 4+ GB of space.

    In both cases, Management Studio is telling me that the files are about 99% empty (which is what I would expect).

    Anyone have thoughts?
    PAC
    Monday, August 03, 2009 7:52 PM

All replies

  • SELECT name AS 'FileName' , physical_name AS 'PhysicalName', size/128 AS 'TotalSizeinMB',
        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB', 
        CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB', 
        (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
        FROM sys.database_files;
    What happens when you run the above command? Does it still show 16 or 400 GB?


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Monday, August 03, 2009 8:35 PM
    Moderator
  • At some point in the past you had some process that ran an used alot of TempDB, what exactly that process was, you won't be able to tell now, but tempdb won't return to its configured sizes until you restart the SQL Service for the database engine.  The 16GB and 2GB information is being pulled from sys.master_files, while the actual size information is available in the sys.database_files DMV.  This is by design since TempDB is a "special" database that is recreated every time the service starts based on the configured sizes in sys.master_files.  Once you restart the instance the space will be freed back to the OS.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, August 04, 2009 2:36 AM
    Moderator
  • Hi Rusag2

    This is Mark Han, Microsoft SQL Support Engineer. I'm glad to assist you with the issue.

    According to your description, I understand that the size of the tempDB is unexpected. Once the issue resolved, i appreciate you making "AS Answer".

    in order to resolve the problem. I would like to explain the following
    1 since th recovery model of the tempdb is simple, the size of the temp log file won't grow generally. the sql will truncate the transaction logged in the log file.

    2 according to you description, we know that the size of the temp is 400GB. So we should run sp_who2 command to verify if there are some block in the tempdb

    3 if the checkpoint is not trigger normally, the issue will happan as expected. So please verify the version of the SQL is SP3(4035)

    if you have any questions on the above, please let me know.

    Regards
    Mark Han
    Wednesday, August 05, 2009 8:53 AM