Finding culprit for ongoing TempDB growth

  • A couple of weeks ago, our TempDB size went crazy, claiming more than 100GB of space within about a week. It has continued to steadily grow ever since, until it finally set off a disk free space monitoring warning a couple of days ago.

    The script from http://www.sqlservercentral.com/scripts/tempdb/72007/ does not show any consistent session that is hogging space; usually everyone in the list shows zero space.

    If I'm to believe the GUI when I ask how much I can shrink the files, something is still blocking off nearly 200GB of tempDB somehow, but when I add up all the usage reported in sys.dm_db_session_space_usage, it only comes to about 8GB.

    What else should I be checking?

  • nhansen pcc - Tuesday, July 3, 2018 1:48 PM

    A couple of weeks ago, our TempDB size went crazy, claiming more than 100GB of space within about a week. It has continued to steadily grow ever since, until it finally set off a disk free space monitoring warning a couple of days ago.

    The script from http://www.sqlservercentral.com/scripts/tempdb/72007/ does not show any consistent session that is hogging space; usually everyone in the list shows zero space.

    If I'm to believe the GUI when I ask how much I can shrink the files, something is still blocking off nearly 200GB of tempDB somehow, but when I add up all the usage reported in sys.dm_db_session_space_usage, it only comes to about 8GB.

    What else should I be checking?

    It doesn't necessarily need to be active sessions. Did you check for open transactions? You probably want to figure out if this is user objects, internal objects or version store related and then go from there. This article has a query to find those but it also goes through other things that can be using tempdb space.
    Monitoring tempdb space usage and scripts for finding queries which are using excessive tempdb space

    Sue

  • I'd start with a high level summary of all the things in tempDB before looking for specific sessions:
    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;

    Once you know if it's Version Store, Internal Objects, or User Objects then you can dig deeper.
    for version store, try sys.dm_tran_active_snapshot_database_transactions:
    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-active-snapshot-database-transactions-transact-sql?view=sql-server-2017

    for user objects or internal objects, maybe try sys.dm_db_session_space_usage:
    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-session-space-usage-transact-sql?view=sql-server-2017

  • Thanks for the replies.

    Both the query above from Chris and the queries in the thread to which Sue referred indicate that internal objects, user objects, and version store are all quite small (half a GB total). Both queries also now indicate that it is mostly free space, which is contrary to what I was seeing earlier.

    So, that relieves some of my concern that I'll exhaust the drive soon.

    I did kill off several old sessions from an automated system yesterday, and since it failed to produce the desired change within a few hours, assumed it had done no good. Perhaps one of them took an excessively long time to rollback (shame on me for not checking).

  • nhansen pcc - Tuesday, July 3, 2018 3:35 PM

    Thanks for the replies.

    Both the query above from Chris and the queries in the thread to which Sue referred indicate that internal objects, user objects, and version store are all quite small (half a GB total). Both queries also now indicate that it is mostly free space, which is contrary to what I was seeing earlier.

    So, that relieves some of my concern that I'll exhaust the drive soon.

    I did kill off several old sessions from an automated system yesterday, and since it failed to produce the desired change within a few hours, assumed it had done no good. Perhaps one of them took an excessively long time to rollback (shame on me for not checking).

    Trust what you get using queries.
    SSMS is generally fine for displaying information but it also has areas where it's a bit odd and not necessarily reliable. And other areas where you shouldn't use it. It's also easy to forget to refresh when needed. But now that you can relax about the space, do a search on: tempdb space used
    You will get a lot of scripts you can use for poking around in tempdb as well as other useful information.

    Sue

Viewing 5 posts - 1 through 4 (of 4 total)

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