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
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
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
if above query shows,
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