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 VALUES(...) Exception Invalid object name '##TempA368AEA69A2F4E949CE8DC668FD7F86F' SQL:BatchCompleted INSERT INTO ##TempA368AEA69A2F4E949CE8DC668FD7F86F VALUES(...) Audit Logout
My question, is there any reason a SQL-Instance closes a session without the application disposing it/timeout