The Phantom Temp Table, bane of Database Admin everywhere. It won’t drop, it won’t disappear, and it won’t let you get your work done. It took me an hour to figure out a solution, so I thought I’d share just in case anyone else had a similar problem.
Let’s start off with the details. At work, we have a stored procedure which creates a temp table with a Primary Key. Here’s the scrubbed version:
IF OBJECT_ID(‘tempdb..#tmpMP’) IS NOT NULL
DROP TABLE #tmpMP;
CREATE TABLE [dbo].[#tmpMP](
[Orderid] [int] NOT NULL,
[Ordernumber] [varchar](19) NOT NULL,
[BatchNumber] [int] NOT NULL,
[Cost] [money] NULL,
[OrderDate] [datetime] NULL,
[SalesID] [int] NULL,
CONSTRAINT [PK_tmpMP] PRIMARY KEY CLUSTERED
( [Orderid] ASC,
[Ordernumber] ASC) );
The proc is run by an SSIS Execute SQL Task. When I came in this morning, I found the job had failed. The job log gave me the following detail:
Error: 2011-01-05 04:44:00.34
Code: 0x00000000
Source: My SSIS Task
Description: There is already an object named ‘PK_tmpMP’ in the database.
End Error
Error: 2011-01-05 04:44:00.37
Code: 0xC002F210
Source: My SSIS Task Execute SQL Task
Description: Executing the query “Exec dbo.uspMyProc ” failed with the following error: “Could not create constraint. See previous errors.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
Well, that’s lovely. Given that we always drop the table, I got concerned. So first I checked the job monitor to make sure the job had stopped executing. It had and there were no other jobs executing either. So then I started nosing around TempDB.
Select * from tempdb.sys.objects
where Object_ID(‘tempdb..#tmpMP’) is not NULL –This got me nothing.
Select * from tempdb.information_schema.tables –This got me a very long temp table name
I expanded the column to see what #tmpMP_____________________… meant and found the table name. So then I checked the columns and constraints on that table. NOTE: I’ve broken the line in the middle for readability purposes. The original name did not have a carriage return in it.
Select * from tempdb.information_schema.columns
where table_name = ‘#tmpMP__________________________________________________________
____________________________________________________000000000434′
Select * from tempdb.information_schema.CONSTRAINT_COLUMN_USAGE
where table_name = ‘#tmpMP__________________________________________________________
____________________________________________________000000000434′
In the results of the constraint_column_usage, I got a constraint name of PK_tmpMP. So that’s why my proc won’t create the constraint. But the kicker is, when I tried to drop the above table, I ended up with the famous “Table either does not exist or you do not have permission” error. That was a bit annoying. I tried all sorts of variations on the DROP TABLE statement and could not get it to drop. So I searched the net and found Dave Pinal’s blog http://blog.sqlauthority.com/2009/03/29/sql-server-fix-error-msg-2714-level-16-state-6-there-is-already-an-object-named-temp-in-the-database/. I copied the below code and altered it accordingly.
IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE ‘#tmpMP%’)
Select *
FROM sys.tables
WHERE name LIKE ‘#tmpMP%’
The result was NULL. Now I was really frustrated. So I did a SELECT Object_ID() on the temp table name and found an object ID. So the table did exist, I just wasn’t able to drop it. I went back and forth, checking jobs to make sure nothing was running, check the Information_Schema views again, changing the databases on my SSMS window in the hopes it would kill the table. I even tried to do a DROP TABLE Object_Name(1526296497), which was silly, but I wanted to see if it would work. It didn’t.
Finally, I did an sp_who2 and found almost 100 sleeping connections to the database by various people. I pulled up my Kill User cursor, ran it, and received a “Only user processes can be killed” error. But when I returned to my Select code to check the table existence, it was gone. Poof.
So, now I know for the next time I run into a phantom temp table. Check the sleeping connections first. BTW, I’m using SQL Server 2005
