Can’t close a cursor or delete a temp table

Question

Post reply

Can’t close a cursor or delete a temp table

Tom Morman

SSC-Addicted

Points: 433
More actions
October 3, 2006 at 2:20 pm

#174608

We executed an sp which contains a cursor to loop through dates to process. After opening the cursor, the sp creates a temp table. Unfortunately, it didn’t drop the table, so during the next loop it fails because it tries to create a table which already exists. Fixing the code is easy enough, but it has left tempdb with some issues. The temp table still exists in tempdb and didn’t drop even after the session ended. In fact, the table still exists even after the client went so far as to reboot! I’m thinking that the cursor still exists and the temp table will not drop until the cursor is closed. Does this make sense? Does anyone have an idea how to close and deallocate the cursor short of restarting the sql service? Obviously the spid that created the cursor is long gone so the typical close and deallocate sequence doesn’t apply. I don’t see any open spids, locks or transactions. (There were no transactions used in this sp) Any help is greatly appreciated!
Thanks in advance!

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the sp code please?

The only thing that imediately comes to mind is that it was a real table created, not a temp table

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

Vera Ivanova SSC Rookie Points: 28 More actions · Answer 2

If you have some errors the statement will be terminated and temp table will be not deleted.

Thanks,

Vera

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 3

If it's a proper temp table (name starts with #) it will be deleted when the connection is closed or goes out of the scope that created it (can't remember which).

Siona Wilbur SSC Journeyman Points: 89 More actions · Answer 4

We have had similar experiences here where a number of temp tables are stranded even after all connections have been closed or terminated. Our experience with this was in dev edition. Our research has lead us to believe there is a bug and the only remedy is to cycle sql.