Let's play what's wrong with this picture! (tempdb size discrepencies)

  • I am baffled ... how am I seeing these different sizes ...

    Anyone seen anything like this?

  • Okay, could you please explain what has you baffled? I've looked at your screen shot, but not sure what I should be looking at.

    😎

  • Look at my SSMS ... sysaltfiles vs sysfiles ...

    Then compare the properties window of tempdb, vs. the size on disk.

    Two different sets of sizes being reported for the size of my tempdb, how?!

  • Pardom me on this one, but the screen print (at least for me) is a little fuzzy (and it could just be my old tired eyes). Could you list this values for me in a post so it is easier for me to comprehend? Be sure to include column headers for context of the values.

    Sincerely appreciated.

    😎

  • I think it is picking up the autogrowth limitation is that roughly what you have available for disk space?

  • Adam Bean (3/14/2008)


    Look at my SSMS ... sysaltfiles vs sysfiles ...

    Then compare the properties window of tempdb, vs. the size on disk.

    Two different sets of sizes being reported for the size of my tempdb, how?!

    This is Apples & Oranges, isn't it? Your comparing current sizes (sysfiles, Explorer) to initial/min sizes: (sysaltfiles, File properties window) aren't you?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • sys.sysfiles is reporting:

    - data - 750904kb

    - log - 30152kb

    file size on disk is reporting:

    - data - 6gb

    - log - 241mb

    The above two match up and should be correct, but the next two match up with one another and are not correct, which is what has me baffled ...

    database properties in SSMS is reporting:

    - data - 8mb

    - log - 1mb

    sys.sysaltfiles is reporting:

    - data - 1024kb

    - log - 64kb

  • Check the upper limit of what you have allowed for tempdb growth. It may be set extremely large.

  • Adam Bean (3/14/2008)


    sys.sysfiles is reporting:

    - data - 750904kb

    - log - 30152kb

    file size on disk is reporting:

    - data - 6gb

    - log - 241mb

    The above two match up and should be correct, but the next two match up with one another and are not correct, which is what has me baffled ...

    database properties in SSMS is reporting:

    - data - 8mb

    - log - 1mb

    sys.sysaltfiles is reporting:

    - data - 1024kb

    - log - 64kb

    What you are looking at in the second set of numbers is the initial size of tempdb. If you were to restart SQL right now, and then look at tempdb, the second set of number would be correct.

    I have confirmed this on our development DW sql server. The actual size is much larger the initial size displayed in the properties window. Restarting SQL resulted in tempdb being recreated to its initial size.

    😎

  • Also, fyi for others reading this, the values reported in sys.sysfiles are in 8K pages, so multiplying those values by 8 should match what you see in windows explorer.

    😎

  • Again, I am pretty sure that TempDB is always like this. The smaller values are the *Initial* sizes that SQL Server uses when it creates TempDB on startup. (I think!)

    I know that sys.sysaltfiles is flaky, (from BOL):

    Under special circumstances, contains rows corresponding to the files in a database.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • So the size in syfiles you have to * by 8 if so i make it

    750904 * 8 = 6,007,232 = 6 Gig

    But the sysaltfiles is in ?

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

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