Post reply

Negative Available Space for tempdb

  • March 11, 2013 at 9:16 am

    #269769

    I'm checking all my databases and when I look at my templog setting in the Shrink File window I see a negative number for both the database file and the log file. How do I handle this? What does it mean? Should I restart the instance to clear out the tempdb file? Right now there's close to 4GB on the tempdb file and about 150 MB in the log file. I have never seen this before. Live and learn or be surprise.

  • March 12, 2013 at 4:03 am

    #1595878

    Checkpoint will work only when log is >70% full.

    So Run Checkpoint manually in the SSMS and see if -ve value exists or not?

    Regards,
    Kumar

  • March 12, 2013 at 4:23 am

    #1595889

    Run DBCC UPDATEUSAGE, see if it fixes anything.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 13, 2013 at 8:25 am

    #1596537

    I ran DBCC UPDATEUSAGE on the tempdb and the negative space is still showing up. Somehow my database file has reached close to 4GB, last few days we have done quite of activity on that server. Could that be causing that problem? I also tried to shrink the database and log file but nothing happens. The db stays at 4GB and the log file keeps changing the negative number to different negative numbers. Other than trying to restart the server to clear the tempdb completely I'm not really sure what to do. Do you guys have any other suggestions?

  • March 13, 2013 at 8:29 am

    #1596540

    Ignore it? If it's not causing any problems, just ignore it.

    Oh, and don't shrink TempDB, it's documented to be able to cause corruption.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 13, 2013 at 10:02 am

    #1596602

    This happened on a couple of our servers before and while I can't remember the exact issue (it is typically related to autogrowth of the database files(s)), I remember this article...might pertain to you and is worth at least checking...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • March 13, 2013 at 10:36 am

    #1596616

    Thanks the article was helpfull. I ran some scripts against the tempdb and got the following:

    SELECT [Name], [Size]*8/1024 AS [File Size in MB] , , [Physical_Name]

    FROM master.[sys].[master_files] AS mf

    WHERE [database_id] = 2 AND [file_id] = 2

    NameFile Size in MBsizePhysical_Name

    templog064C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf

    select name, [Size]*8/1024 AS [File Size in MB], , physical_name, [file_id] from tempdb.sys.database_files where file_id = 2

    nameFile Size in MBsizephysical_namefile_id

    templog10914048C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf2

    The numbers are definitely different that explains the negative number. I'm assuming that once the server it's restarted that should cleared out and reset, I will potentially see this again. Thanks again guys.

  • March 13, 2013 at 10:08 pm

    #1596802

    Thanks for the article. This problem is new to me.

    Regards,

    Ram

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

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