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.

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