Tempdb Full

  • aroojahmad

    SSC Veteran

    Points: 271

    Hi ,

    One of my production Server tempdb is getting full abnormally. When I checked about consume tempdb space so I found there is four “Delete” command running continuously and wait type is “BROKER_RECEIVE_WAITFOR”.

    I checked about the Delete command its actual a Stored procedure which is sending and receiving message and command is written there is “WAITFOR”

    Is there anyway related which is causing to tempdb full.

    Kindly request to help me on this.

    Thanks,

    Arooj

    • This topic was modified 1 week, 3 days ago by  aroojahmad.
  • Chris Harshman

    SSC-Forever

    Points: 41584

    BROKER_RECEIVE_WAITFOR is typically not a problem and means that something is using Service Broker but there are no messages for it to process.

    To research why TEMPDB is filling up, here are some troubleshooting tips:

    1. to see high level TEMPDB usage:

    BROKER_RECEIVE_WAITFOR is typically not a problem and means that something is using Service Broker but there are no messages for it to process.

    To research why TEMPDB is filling up, here are some troubleshooting tips:

    1. to see high level TEMPDB usage:

    USE tempdb;
    SELECT SUM(unallocated_extent_page_count) AS FreePages,
    CAST(SUM(unallocated_extent_page_count)/128.0 AS decimal(9,2)) AS FreeSpaceMB,
    SUM(version_store_reserved_page_count) AS VersionStorePages,
    CAST(SUM(version_store_reserved_page_count)/128.0 AS decimal(9,2)) AS VersionStoreMB,
    SUM(internal_object_reserved_page_count) AS InternalObjectPages,
    CAST(SUM(internal_object_reserved_page_count)/128.0 AS decimal(9,2)) AS InternalObjectsMB,
    SUM(user_object_reserved_page_count) AS UserObjectPages,
    CAST(SUM(user_object_reserved_page_count)/128.0 AS decimal(9,2)) AS UserObjectsMB
    FROM sys.dm_db_file_space_usage;

    2. to see what sessions are using the most space:

    SELECT top 10 s.login_name, ssu.*
    FROM sys.dm_db_session_space_usage ssu
    INNER JOIN sys.dm_exec_sessions s ON ssu.session_id = s.session_id
    --ORDER BY (ssu.user_objects_alloc_page_count + ssu.internal_objects_alloc_page_count) DESC
    ORDER BY (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count) + (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) DESC;

    3. to see largest temp tables:

    SELECT s.name AS schema_name, t.object_id, t.name AS table_name,
    t.create_date, i.name AS index_name, SUM(ps.row_count) as row_count,
    SUM(used_page_count) / 128 AS total_mb, SUM(used_page_count) * 8 AS total_KB
    FROM tempdb.sys.tables t
    INNER JOIN sys.schemas s on t.schema_id = s.schema_id
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
    WHERE i.index_id IN (0,1)
    GROUP BY s.name, t.object_id, t.name, t.create_date, i.name
    ORDER BY total_KB desc, s.name, t.name[/code]
    this should at least get you started to piece together what is using TEMPDB

    this should at least get you started to piece together what is using TEMPDB

  • aroojahmad

    SSC Veteran

    Points: 271

    Hi Chris,

    Thanks for the response. I ran second query and got the same stored procedure which I mentioned in my first post. The code has written as Waitfor and Receive message and timeout is given as in millisecond.

    When I investigate further from SQL log , I got there is some disk I/O error message:

    “SQL Server has encountered 14817 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL11.abcde\MSSQL\Data\abcde.mdf] in database [abcde] (5). The OS file handle is 0x0000000000000B08. The offset of the latest long I/O is: 0x00003309eb0000”

    Is this causing to grow tempdb and what is the solution for it. Please advise.

    Thanks,

    Arooj

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713653

    That message indicates your IO subsystem is overloaded. It cannot handle the load. This could lead to things taking longer, which could mean more concurrency in tempdb.

    What is abnormal? Do you have a regular size that tempdb uses? Is there monitoring to confirm this? If things are growing abnormally, you might need to run queries, such as those above, over a few time periods to understand where the load is.

  • Andrey

    SSC Veteran

    Points: 246

    aroojahmad wrote:

    Is there anyway related which is causing to tempdb full. Kindly request to help me on this.   Thanks, Arooj

    Arooj,

    if your tempdb files are configured to autogrow and “default trace” is running, it is possible to find out who and when caused tempdb to grow up to the limit.

    Default trace contains events “Data/Log File Growth”.

    Default trace – A Beginner’s Guide

     

  • aroojahmad

    SSC Veteran

    Points: 271

    Yes, its growing abnormally. Due to space issue on temp drive , we thought tempdb require more space so we asked server team to uplift the space . They have added 200 GB but still the issue is remain same . Now, tempdb consuming all 400 GB of space. we are worrying , why this is growing because every time we can’t ask to extend the space and we have other instance as well on that server but their tempdb is working fine.

     

  • aroojahmad

    SSC Veteran

    Points: 271

    Andrey,

    I checked default trace and found missing stats in tempdb:

    “NO STATS:([tempdb].[dbo].[#calculateRequestNewResourceNestedSetStatementTransitionsNonMemberSet_______________________________________________0000000E4593].[SetKey])”

    Is this a causing the issue? Please confirm.

  • aroojahmad

    SSC Veteran

    Points: 271

    I could find , one of the table is having million of records and when we tried to open index fragmentation for same table sql server is not responding and after sometime its shows nothing.

    Index rebuild never be success because of this table and it gets failed due to insufficient disk space but we have around 200 GB free space out of 1.18 TB.

    Is there any issue on this table. Is this reason to get tempdb full. please help to find out cause. Thanks in advance!

    Arooj Ahmad

  • Andrey

    SSC Veteran

    Points: 246

    aroojahmad wrote:

    Andrey, I checked default trace and found missing stats in tempdb: "NO STATS:([tempdb].[dbo].[#calculateRequestNewResourceNestedSetStatementTransitionsNonMemberSet_______________________________________________0000000E4593].[SetKey])" Is this a causing the issue? Please confirm.

    Arooj, hi

    I’m pretty sure that this finding doesn’t relate to the issue.

    this message tells us that there’s missing statistics on column SetKey of table #calculateRequestNewResourceNestedSetStatementTransitionsNonMemberSet

     

  • Andrey

    SSC Veteran

    Points: 246

    aroojahmad wrote:

    I could find , one of the table is having million of records and when we tried to open index fragmentation for same table sql server is not responding and after sometime its shows nothing. Index rebuild never be success because of this table and it gets failed due to insufficient disk space but we have around 200 GB free space out of 1.18 TB. Is there any issue on this table. Is this reason to get tempdb full. please help to find out cause. Thanks in advance!   Arooj Ahmad

    Arooj

    This table either causes the problem with tempdb (50%), or not (50%).

    It depends a bit on do the issue with tempdb and your “open index fragmentation” happen in the same time.

    If you rebuild the index with option “sort_in_tempdb”, then it can be a reason.

    Anyway, there are two ways to get to the bones :   long way  (guessing)  and short way (monitoring, see post of Chris Harshman )

    Regards,

    Andrey.

     

  • Sue_H

    SSC Guru

    Points: 89409

    You may want to check the article and see if the issue applies in your case – make sure to read the beginning and query sys.dm_db_task_space_usage. There is a CU to apply if it applies:

    https://support.microsoft.com/en-us/help/3005011/unexpected-growth-of-tempdb-data-files-when-using-sql-server-service-b

    Sue

     

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

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