0

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.

pso
  • 819
  • 12
  • 25

2 Answers2

3

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

Anuj Tripathi
  • 2,251
  • 14
  • 18
  • Thanks for the detailed info Anuj, it was really informative. The temp db in my case was growing while running a script that had a join statement with a table where there were duplicate entries. It must have increased the number of internal objects that you mentioned. – pso Sep 25 '15 at 04:33
0

Perhaps you can use following SQL command on temp db files seperately

DBCC SHRINKFILE

Please refer to https://support.microsoft.com/en-us/kb/307487 for more information

Eralper
  • 6,461
  • 2
  • 21
  • 27
  • Thanks Eralper, but I would like to know the sizes of each temporary tables first and also what is creating those tables. – pso Sep 14 '15 at 11:25
  • Please check the following tutorial http://www.kodyaz.com/articles/sql-server-exec-sp_msforeachdb-sp_msforeachtable-example-tsql-code.aspx There is already built code which executes sp_MSForEachTable with sp_spaceused for all tables in a database. But I'm not sure you can find what causes temp tables to be created – Eralper Sep 14 '15 at 13:02