MS SQL Multiple temp tables - clean up

I have some massive Stored Procs that create temp tables by the 10's.  What is the best way to ensure those are all clean up in a timely manner with in the proc?

We are having resouces issues in the SQL 2000 databases and my job is to find a way to tweak it, but for I go out on the limb and say  - Move 2008.

Who is Participating?
JestersGrindConnect With a Mentor Commented:
If you want to clean the temp tables up in the stored procedure that is creating them, you need to execute a DROP TABLE #YourTempTableName for each temp table created at the end of the stored procedure.  


sarabhaiConnect With a Mentor Commented:
can u show the code of that store procedure?

or u can use the

DROP TABLE #tempTable
DROP TABLE ##tempTable

TimSweet220Author Commented:
Most of the table are dropped found a few that were.

If a proc is created 10 temp tables, how resource intense are they, some are dropped right way some are queried against later on in the procedures.  Some with as many fields a 30 and 100's of records.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.