Post reply

Sp_spaceused output - Help in interpreting its result

  • May 27, 2011 at 10:17 am

    #104071

    Hi All,

    One of the server with SS2000 SP4 EE has shown performance degradation. on checking for the sp_spaceused for 'tempdb' database, I am amazed to find following results:

    -------------------------------------

    Database Size Unallocated Space

    45825.60 MB 45580.41 MB

    Reserved Data Index_Size Unused

    --------------------------------------------

    600 KB 176 KB 344 KB 88 KB

    I checked about the meaning of different terms involved on BOL but not able to get clear idea. I am not sure specifially about the figure written in bold above.:w00t:

    Please help me to clarify the:-

    1. Terms involved in this output

    2. How can we set SQL Server to use this unallocated space, so that database file will remain of small size and donot inflate?

    Thanks in advance

    With regards,

    Ankur

  • May 30, 2011 at 2:39 am

    #1331541

    Hi All,

    Please help and provide your valuable thoughts on this topic..

    Thanks in advance.

    regards,

    Ankur

  • May 30, 2011 at 5:48 am

    #1331599

    2 things could have occured.

    #1 Tempdb is set to have a starting size of 45 GB.

    #2 tempdb has had to grow to that size to accomodate the server load or a big report. That means that tempdb needs to have THAT much space available.

    As a 1 time test you could shrink it to maybe 20 GB to see if it grows back again to 45 GB. If it does, just leave it alone.

  • May 30, 2011 at 11:27 am

    #1331699

    Thanks Ninjafor the response.

    As this huge size of Tempdb is occupying major chunk of hard disk space and leading to free space issues, the server has been rebooted and now the tempdb has following :-

    Tempdb Size: 4 GB's

    Auto Increment : 50 MBs

    Still there are messages logged in Error log stating the " Server has encountered 3523 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf] "

    Do I have to do something else to manage the Tempdb Size and improve performance hit? Also, please let me know why it is showing so high figure of Unallocated Space. Is it a problem?

    With regards,

    Ankur

  • May 30, 2011 at 11:43 am

    #1331704

    It's quite possible that those errors are due to waits for the db to autogrow. At some point it could also be due to physical fragmentation in the db.

    I'd personally start temdb at maybe 24 GB + 1GB autogrowth and see if it still grows. This is where you need to focus your next steps because you can't fix what you don't see.

    Check this out for the full story about vlf and tempdb optimization :

    http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

  • June 1, 2011 at 8:08 am

    #1332519

    Hi,

    Thanks Ninja_RGR for the wondeful link.:-)

    Just to be equipped of the meaning of different terms, can you please explain more about the Terms involved in this output of Sp_Spaceused command?

    With regards,

    Ankur

  • June 1, 2011 at 8:10 am

    #1332520

    Which one is not clear?

  • June 1, 2011 at 9:07 am

    #1332549

    Hi Ninja_RGR,

    Thanks again..

    In my case unallocated space is very huge (almost near to the size of 48 GB) i.e.:

    Database Size Unallocated Space

    45825.60 MB 45580.41 MB

    as BOL at link http://msdn.microsoft.com/en-us/library/ms188776(v=sql.90).aspx states, "it is the Space in the database that has not been reserved for database objects."

    So does it means that so much free space is there in Tempdb but not being utilised?

    If so, kindly let me know how can this space be utilised, so that tempdb data file doesnot grow any more and uses this space instead.

    With regards,

    Ankur

  • June 1, 2011 at 9:12 am

    #1332555

    Yes it means free space... not assigned to anything.

    Tempdb doesn,t just grow for the fun of it. Someone set it to that size or it grew there because it had to.

    So bottom line, unless the server is crashing because of lack of space, just leave that alone.

  • June 1, 2011 at 10:32 am

    #1332596

    Hi,

    Thanks a ton Ninja_RGR !!! for the continous help and clarifying doubts.:-)

    With regards,

    Ankur

Viewing 10 posts - 1 through 9 (of 9 total)

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