When do Global Temporary Tables get destroyed in SQL Server?

Question

I am confused about the usage of global temporary table. I have created a global temporary table

//Session 52 : Creator Session

Create Table ##temp(i int) 
Insert Into ##temp(i) Values(1),(2),(3)
Select * From ##temp

//Session 56

Select * From ##temp

//Session 57 : last session which holds the refference of ##temp

Select * From ##temp

Now if I close the Session 52, the ##temp table destroys.

I believe global temp table released in the following cases

1. It's explicitly removed by any sessions
2. The creator session closed and there is no other sessions referencing this session.

I am confused about sessions referencing this session(creator Session) what does this mean ?

Answer 1

From CREATE TABLE:

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended

Although as a personal nit-pick, I'd have used after instead of when in that last sentence.

So, per your example, session 57 keeps the global temporary table around so long as it's actually executing a statement that relies upon the table. As soon as that statement is complete - so the session has moved onto another statement or it's sitting idle - the global temp table will be dropped.

Answer 2

Global temporary tables are only destroyed when the current user is disconnected and all the sessions referring to it are closed. This means that the global temp table will not be destroyed unless all the sessions related to the user(session creator) are not closed.