I am using Temporary table to delete multiple row at the same time in the database. Every time I try to use temporary table on a specific Sql-Server-2016 it fails.

I did try to reproduce the issue on a different instance and it is not happening.

The trace from the server showing the issue shows that just after successfully creating the Temporary Table the session is closed. The same session is then used to try to insert data in the Temp table but it failed since the Temp table only last as long as the session that created it. The Connection to the database is not pooled (and it says so in the traces too).

I compared the configuration between a fresh installation and the server showing the issue and all the configuration from sys.configurations are the same.

Here are the traces I collected. Every Audit Login have the same parameters command so I will only put put it once.

Audit Login
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

SQL:BatchStarting   CREATE TABLE ##TempA368AEA69A2F4E949CE8DC668FD7F86F (...)
SQL:BatchCompleted  CREATE TABLE ##TempA368AEA69A2F4E949CE8DC668FD7F86F (...)
Audit Logout

Audit Login
Exception   Invalid object name '##TempA368AEA69A2F4E949CE8DC668FD7F86F'
SQL:BatchStarting   INSERT INTO ##TempA368AEA69A2F4E949CE8DC668FD7F86F 

Exception   Invalid object name '##TempA368AEA69A2F4E949CE8DC668FD7F86F'
SQL:BatchCompleted  INSERT INTO ##TempA368AEA69A2F4E949CE8DC668FD7F86F 
Audit Logout

My question, is there any reason a SQL-Instance closes a session without the application disposing it/timeout

  • 1
    something in the client code is disconnecting and reconnecting, or creating a new session. Without seeing the client code, it is impossible to tell what the problem is. – Max Vernon Aug 13 '18 at 17:20
  • 1
    I don't see any attentions in there, so it looks like the client app connects and does the create then disconnects. Then connects again to try to insert... then logs out... then connects again... Looks like an app issue. – Sean says Remove Sara Chipps Aug 13 '18 at 17:27
  • I had ruled out the idea that the Client application was disconnecting from the SQl-Server since the SPID in the trace does not change. It is 576 at the first LogIn and at the 2nd and 3rd connection too. – boombalabo Aug 13 '18 at 17:27
  • 2
    What are you getting from a global temp table that you couldn't get from a permanent table? Anyway a global temp table will go out of scope when the last session referencing it disconnects. If your app creates it, is the only one that touches it, then disconnects, poof. SPIDs are not unique and they definitely get reused. Don't assume that the same SPID means the same connection / session (connection pooling). You have it right there, Audit Logout after the CREATE TABLE. You need to check your application code because it is obviously not maintaining a session for these separate commands. – Aaron Bertrand Aug 13 '18 at 17:30
  • 1
    I'm not sure why it was done in temp table, from what I can see it could have been done in local temp table, I think the permanent table was not chosen since the dev did not want to have to clean up the permanent table if the application crashed. – boombalabo Aug 13 '18 at 17:47

As noted in the comments, if your session disconnects for any reason, you're going to have problems with even a global temp table disappearing.

Here's a neat workaround: create real user tables in TempDB. This works perfectly fine:

USE tempdb;
CREATE TABLE dbo.persistent(ID INT);

And then you can access that table from any session (via tempdb.dbo.persistent.)

I'm not saying this is a good idea - it's a bad one in the same way that global temp tables are a bad idea, like contention across multiple sessions, having problems when you have an AG failover or a service restart, or filling up your TempDB drives - but as long as you're trying to use global temp tables, this would be a workaround.

  • I never create temp tables in this fashion as it just feels so dirty, but does this approach require an explicit DROP statement or service restart for the table to be cleaned up or will garbage collection eventually come around when no active sessions are using it? I ask in case it requires an explicit DROP or service restart which may be a worthwhile addition to your answer. – John Eisbrener Aug 13 '18 at 18:18
  • Just like a real table, there is no garbage collection. (Which is what the questioner was aiming for, since garbage collection was backfiring.) – Brent Ozar Aug 13 '18 at 18:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.