April 8, 2009 at 9:55 am
My application created a global temporary table and few minutes later disconnected from SQL Server. The global temp table should have been deleted rigth then.
But it was not.
How do I find which user is accessing a global temporary table.
April 8, 2009 at 9:58 am
Global temporary tables (##temptable) stay around until SQL Server is restarted. Local temporary tables (#temptable) will dropped when the session that creates them ends.
April 8, 2009 at 9:59 am
According to MS the table should be gone:
Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
April 8, 2009 at 10:27 am
Okay, my testing confirms that it should have gone away. How did the application disconnect from the database? Was it a clean connection or is it possible that the server thinks it is still connected, in other words the session still exists on the server?
April 8, 2009 at 10:30 am
Hi Tanya
Global temporary tables are dropped when last session accessing the tables is closed. It is always good practice to drop the temporary tables in the same scope, once we are done with it.
April 8, 2009 at 11:09 am
Thank you for confirming.
The clint machines' process, that created temp table, has finished. As far as I know, it was the clean exit. The application does not delete the temp table (I know, it should have). So SQL Server "thinks" that someone still using it. Is there a way to figure "who".
April 8, 2009 at 11:23 am
Only suggestion I can come up with at this time is try using the Activity Monitor and look at locks by object.
I'll need to do some more research to see if there is anything else.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply