July 1, 2011 at 3:30 am
Hi
I have a problem on our production server. Every few weeks #temptable stops working. I get the following error : "Invalid object name '#temptable'."
Temporary table is used inside a stored procedure that gets called about 100000 times a day. Most of the time it works flawlessly, but every once in a while I get the above mentioned error. When this happens only the instance restart helps.
The stored procedure has the following logic:
CREATE TABLE #temptable ( ClientId int IDENTITY PRIMARY KEY, idA int )
INSERT INTO #temptable ( idA ) SELECT idA FROM a WHERE <some_conditions>
SELECT * FROM #temptable INNER JOIN b ON <join_predicate>
The thing is that I can't reproduce this and I can't find out why this happens. If I fetch the query from the SQL Profiler and run it in Management Studio it works fine. But in the same time it doesn't on web page.
After the restart it works fine.
Server is MS SQL Server 2005 64bit. Version 9.00.5000.00 (SP4).
For tempdb I have 4 data files. Each size of 5GB and autogrow by 10%.
Server works on:
Windows server 2003 R2
Standard x64 Edition
Service Pack 2
Intel Xeon CPU E5420 @ 2.50GHz
12GB RAM
I have searched a lot of forums, blogs.. but I haven't found anything that could help me..
Does anyone know what could be wrong? Or where should I look for error?
July 1, 2011 at 6:45 am
the few times i've read about this kind of issue, it was situations that i'm not sure apply here:
connection pooling: create a temp table, and then issue another statemetn...sometimes your connection is switched for another one, but in this case, you've issued a stored proc command, so that wouldn't apply.
if tempdb ran out of space and the table couldn't be created, but i'd expect an error related to unable to create, not the error you got.
i thought maybe parrallelism, but not sure how that might play into the mix.
no help i know but at least lets get soem thoughts flowing, right?
Lowell
July 2, 2011 at 12:05 pm
I don't have anything concrete in mind as to why it could be happening but I am curious, which statement fails with "table not found", the INSERT INTO or the SELECT?
It looks like you've obfuscated the code, which is fine, but it means I have to ask, are there any proc calls or any other operations between the CREATE TABLE and the statement causing the error? Temp tables are session based so is it possible that a trigger or something happening inside another proc could be dropping the temp table?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 11:17 pm
Wow, now thats a toughie... Some thoughts:
1] Is this sproc used by multiple applications or by multiple pages on the same application, do you see any pattern as to when you get the error, is it from a specific application or page always or is it random?
2] Can you query syscomments to find out which all sps are dependant on this table. May be you should check on all databases on your server and see if there is something weird. I guess its always better to check existance of ur temp table within the sproc before creating, just in case the same name is used by different sprocs.
July 7, 2011 at 12:04 pm
1) do you REALLY need that identity clustered PK on the temp table?? I can count on 2 hands the number of times in almost 15 years of SQL Server work where such a construct was appropriate on a temp table. It is HUGE overhead for nothing if you don't use it.
2) do you explicitly drop the temp table in the sproc?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply