• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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.

Thanks
0
TimSweet220
Asked:
TimSweet220
2 Solutions
 
JestersGrindCommented:
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.  

Greg

0
 
sarabhaiCommented:
can u show the code of that store procedure?

or u can use the

DROP TABLE #tempTable
or
DROP TABLE ##tempTable

0
 
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.  

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now