Suddenly Cannot Drop Temporary Tables in 2008 R2

  • I just encountered an odd behavior on one of my 2008 Servers and I cannot figure out what is different. I cannot drop a temp table if it exists.

    This is a stored procedure that has been in place and working since January of this year, but it has just started giving me an error about dropping temp tables.

    The procedure has "housekeeping" sections at the beginning and end of the procedure, which look for temp tables and drop them if they exist.

    --Housekeeping

    If Exists(Select 1 from TempDB.sys.Objects where name like '#Temp2%')

    Drop Table #Temp2

    If Exists(Select 1 from TempDB.sys.Objects where name like '#Temp%')

    Drop Table #Temp

    On my local box, which is SQL 2008 Developer, there is no problem. It works perfectly.

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

    On the Prod box, version:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

    I get the following errors:

    Msg 3701, Level 11, State 5, Line 57

    Cannot drop the table '#Temp2', because it does not exist or you do not have permission.

    Msg 3701, Level 11, State 5, Line 59

    Cannot drop the table '#Temp', because it does not exist or you do not have permission.

    Since the versions of both boxes are the same, the only difference being Developer 32 bit vs Enterprise 64 bit, this behavior must be due to some kind of change in the SQL Server configuration, but I have not found what the difference is between these two versions, and no one has owned up to changing anything on the server, although I do not know what change could make this happen on any server.

    Am I missing a configuration setting?

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • You've had this working on dev for 9 months or on prod??

    This code will fail in multi-user execution.

    Try this, create a temp table in connection 1 in ssms and either do a waitfor or start a tran and don't close it

    then run your select in connection 2. You will see a temp table but you SHOULDN'T.

    This is what I use : IF OBJECT_ID ('tempdb..#tbl', 'U') > 0 DROP TABLE #tbl

  • The code's just wrong.

    1) the physical name of the object in tempdb is not documented, so shouldn't be relied upon

    2) It won't work for multiple users

    3) It only checks what the table name starts with, not the absolute name of the table. If another session had a table called #tempAnotherTableName it would return true and try to drop the table

    Use Object_ID as Ninja's suggested.

  • Excellent. When I looked for the right way to determine existence of tempdb objects last year that was all I could find that worked, and it did work until today. I'm all for a better way if there is one.

    Oh, and it was working in Prod for 9 months. It still works in Dev just fine.

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • Consider you have a hung transaction. This may hold the temp table open for a long time.

    True, we still need the correct way to drop temp tables.

    The dirty way is to stop and start the SQL service, this should give a new tempdb without the table that is holding you.

  • Please note, 4 year old thread.

    Restarting a prod SQL Server to remove a temp table is a tad overkill....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply