sql-servertempdb

Unallocate unused space in tempdb sql server


any script in sql server to find space used by temporary tables + the database name where that temp table was created in tempdb?

The size of my tempDb has grown up to 100 gb and i am not able to recover the space and am unsure what is occupying so much of space.

Thanks for any help.


Solution

  • Temporary tables always gets created in TempDb. However, it is not necessary that size of TempDb is only due to temporary tables. TempDb is used in various ways

    1. Internal objects (Sort & spool, CTE, index rebuild, hash join etc)
    2. User objects (Temporary table, table variables)
    3. Version store (AFTER/INSTEAD OF triggers, MARS)

    So, as it is clear that it is being use in various SQL operations so size can grow due to other reasons also

    You can check what is causing TempDb to grow its size with below query

    SELECT
     SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
     SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
     SUM (version_store_reserved_page_count)*8  as version_store_kb,
     SUM (unallocated_extent_page_count)*8 as freespace_kb,
     SUM (mixed_extent_page_count)*8 as mixedextent_kb
    FROM sys.dm_db_file_space_usage
    

    if above query shows,

    • Higher number of user objects then it means that there is more usage of Temp tables , cursors or temp variables
    • Higher number of internal objects indicates that Query plan is using a lot of database. Ex: sorting, Group by etc.
    • Higher number of version stores shows Long running transaction or high transaction throughput

    based on that you can configure TempDb file size. I've written an article recently about TempDB configuration best practices. You can read that here