23

Does SQL server automatically purge these out after a given length of inactivity or do I need to worry about purging them automatically? If so, how do I query for a list of tables to purge?

Jeff Atwood
  • 63,320
  • 48
  • 150
  • 153
Jeff
  • 8,020
  • 34
  • 99
  • 157

4 Answers4

27

In spite of the fact that temporary tables are dropped when a connection terminates, it is often still considered good practice to explicitly delete such tables in order to be absolutly certain clean up has taken place.

For example, if your platform makes use of connection pooling, i.e. the connections may never be dropped, then do your temporary tables therefore too still exist?

In order to check for the existence of a temporary table you can use the following statement/check.

if object_id('tempdb..##temptbl') is not null
begin
    drop table ##temptbl
end
John Sansom
  • 41,005
  • 9
  • 72
  • 84
16

Local temporary tables are destroyed when you close your connection to SQL Server. There is no need to manually purge them under normal circumstances. If you maintain a persistent connection, or connection pooling, you may want to get in the habit of dropping temporary tables immediately after use.

Global temporary tables, on the other hand, since they are visible to all users in a given database, are destroyed along with the last connection which references them.

vezult
  • 5,185
  • 25
  • 41
  • I assume reporting services would not be a persistent connection so I'd be OK? – Jeff Mar 27 '09 at 05:21
  • 7
    I believe this to be incorrect for global temp tables; I believe global temp tables (##MyTempTable) needs to be purged whereas for normal temp tables (#MyTempTable) what you said would apply.... Right? – Frank V Jul 30 '09 at 16:23
  • 1
    I'm finding ## global temporary tables don't do what it says on the tin. If I make 3 instances of a windows program, all with an open SQL DB connection, and they all create and/or use a temp table called ##hello - what do you expect to happen as the programs get shut down? I expected ##hello to be dropped when they had all closed. In fact, the table is dropped when, and only when, the program that created the table closes. That is not what it says in the MS docs, unless it changed since 2012 – Cato Feb 20 '18 at 16:52
4

In answer to the second part of your question, to query for the list of tables to purge, try this query:

SELECT * FROM tempdb..sysobjects

That will list out all the current global temporary tables

PaulStock
  • 11,053
  • 9
  • 49
  • 52
1

Since most viewers are here via search engines for the answer to the title of the question: To delete Global Temporary tables in SQL Server 2016 and higher:

DROP TABLE IF EXISTS ##tempTable;
BassGod
  • 151
  • 1
  • 11