We have an application running on IIS 5, connecting to a SQL Server SP2 back-end (W2K everywhere.)
We have a bunch of stored procedures using a lot of small local temporary tables. To save connection time, we use a (growing) pool of connections to SQL Server.
I thought that the life scope of the temporary table was the connection (at least, that's what all of my readings say!), but I noticed the temporary table's name disappears from tempdb..sysobjects as soon as the stored procedure exits, even though the connection is still there. So, is there a narrower scope of the temporary tables, at the stored procedure level? Or do I need to drop the temporary tables manually as the stored procedure finishes?
Actually, my main concern is that we have an impressive amount of memory leaks: the sqlserver process keeps on demanding more memory, even though the activity decreases (but the pool of connections is not shrinked immediately...) So I'd rather be sure that the memory space used by the temporary tables is recycled as soon as the stored procedures end.
Thanks in advance,