Why are temporary tables not removed from tempdb in SQL Server?

sql sql-server temp-tables tempdb
Brijesh Patel picture Brijesh Patel · Jul 8, 2011 · Viewed 22.7k times · Source

I have created one stored procedure with 7 temporary tables and each temp table is dropped at the end of their own work.

I am calling the SP from one web service and same web service we are used for different instance.

I have dropped every temp table forcefully but when SP executes it will not delete any of the temporary table which are located in "tempdb/Temporary Table". And, when I open new instance of my application and try to execute same SP it will modify same temp tables.

This creates problem for me. it will lock the tables when SP execute simultaneously it will lock the table and my sp is not able to produce result and throw exception.

So I want to drop my temporary tables at the end of my operation. please help.

Answer

Jeremy Gray picture Jeremy Gray · Jul 8, 2011

I can't tell you why this is happening, but I have dealt with it before as well. Try cleaning up your tables at the beginning or end of the SP or using table variables.

IF object_id('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName

Related questions

Inserting data into a temporary table

After having created a temporary table and declaring the data types like so; CREATE TABLE #TempTable( ID int, Date datetime, Name char(20)) How do I then insert the relevant data which is already held on a physical table within the …

Read More
SQL Server: Is it possible to insert into two tables at the same time?

My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record. I want to copy the data from …

Read More
Temporary table in SQL server causing ' There is already an object named' error

I have the following issue in SQL Server, I have some code that looks like this: DROP TABLE #TMPGUARDIAN CREATE TABLE #TMPGUARDIAN( LAST_NAME NVARCHAR(30), FRST_NAME NVARCHAR(30)) SELECT LAST_NAME,FRST_NAME INTO #TMPGUARDIAN FROM TBL_PEOPLE When I …

Read More