TempDb free space

  • Today I found TempDb size has increased and disk space is very low. I need to get more free space in disk. And I tried to shrink TempDb but it is not getting any free space in disk. So May i know that what is alternative step to take to get free space by without restarting SQL Server. Because it is in production environment the users are connected. My disk Used Space is 15.6 GB and Free Space is 4.29 GB

  • mohan.bndr (2/17/2012)


    Today I found TempDb size has increased and disk space is very low. I need to get more free space in disk. And I tried to shrink TempDb but it is not getting any free space in disk. So May i know that what is alternative step to take to get free space by without restarting SQL Server. Because it is in production environment the users are connected. My disk Used Space is 15.6 GB and Free Space is 4.29 GB

    Please execute below query & post the output here:

    SELECT [name],[log_reuse_wait_desc] FROM sys.databases

    WHERE [name]='tempdb'


    Sujeet Singh

  • ya, I got the result below...

    namelog_reuse_wait_desc

    tempdbNOTHING

  • first thing I would look at is what caused tempdb to grow, index rebuilds being done in tempdb, excessive sort operations, increased number of temp tables table variables etc.

    shrinking the file will give you the space but it isn't identifiying root cause and could happen again.

  • But when I try to shrink, I will not get any free space in disk.

    I executed the query below ...

    DBCC SHRINKFILE ('tempdev', 1024)

    eventhough I didn't get free space in disk.

  • http://support.microsoft.com/kb/307487

    if you cant shrink the file it means something is using the space, find out what, kill the session drop the object etc then shrink the file

    i wouldnt shrink tempdb though, i would get more space on the drive as i said in my first post it will happen again unless you stop what caused it to grow in the first place

  • mohan.bndr (2/17/2012)


    But when I try to shrink, I will not get any free space in disk.

    I executed the query below ...

    DBCC SHRINKFILE ('tempdev', 1024)

    eventhough I didn't get free space in disk.

    The query that I provided was to check why log can not be reused, well you are trying to shrink the data file. You must have got some temporary object created within tempdb which is still being referenced somewhere. That is why you are not able to shrink it.

    Did you run any heavy query with sorting ???


    Sujeet Singh

  • No, I didn't run any heavy query with sorting in my side. may be the users are trying it.... so that case how can we find. Please let me know what should i do to get free space back to disk.

  • mohan.bndr (2/17/2012)


    No, I didn't run any heavy query with sorting in my side. may be the users are trying it.... so that case how can we find. Please let me know what should i do to get free space back to disk.

    Please run this query to identify the sessions that are utilizing most of your TempDB.

    SELECT session_id,(user_objects_alloc_page_count*8/1024) AS SpaceUsedByTheSessionMB,*

    FROMsys.dm_db_session_space_usage DDSSU

    WHEREdatabase_id=DB_ID('tempdb')

    ANDuser_objects_alloc_page_count > 0

    If you find any session taking more than expected space, check the query that has been executed from that session_id.

    DBCC INPUTBUFFER (session_id)

    If the query execution had been completed successfully but somehow the session is still open then you can decide to kill the session or you may want to check the status of the session. Once you do it, you should be able to shrink the TempDB database.


    Sujeet Singh

  • mohan.bndr (2/17/2012)


    Today I found TempDb size has increased and disk space is very low. I need to get more free space in disk. And I tried to shrink TempDb but it is not getting any free space in disk. So May i know that what is alternative step to take to get free space by without restarting SQL Server. Because it is in production environment the users are connected. My disk Used Space is 15.6 GB and Free Space is 4.29 GB

    Mohan, this is a big issue you can't find it instantly what is causing the tempdb growth.

    There are 3 kinds of process which use tempdb space internal storage, user storage and version storage. You have to run some dmv's to figure which one of the above is causing the tempdb growth.

    Try to reboot the server for now in maintenance time when not many users are connected and work on the above issues.

  • sorry, it is in production environment. It will not allo any down time also.

  • use following script to query space used by objects, then figure out what happened.

    ----the tempdb space used by user and by internal objects

    Select

    SUM (user_object_reserved_page_count)*8 as user_objects_kb,

    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,

    SUM (version_store_reserved_page_count)*8 as version_store_kb,

    SUM (unallocated_extent_page_count)*8 as freespace_kb

    From sys.dm_db_file_space_usage

    Where database_id = 2

  • This will also work

    SELECT SUM(unallocated_extent_page_count) AS [free pages],

    (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

    FROM sys.dm_db_file_space_usage;

  • It may be that you have service broker filling up your transmission queue. What is the result of the following query?

    select count(*) from sys.transmission_queue



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • You don't need to reboot the server as a last resort to shrink the tempDB. You can shut off & turn on the SQL Server service in Configuration Manager.

    Yes, that does mean downtime, but not as much as a server reboot would.

  • Viewing 15 posts - 1 through 15 (of 15 total)

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