SQl: Tempdb reserved space not being released
Hi All,
We have a stored proc that seem to execute but never deallocate the space it reserves in tempdb. During the stored proc it creates 7 temp tables each with about 100,000 - 200,00 rows and end of the proc it drops those tables and does a truncate of another table. As this stored proc runs a number of times a day the space increases..and then our monitoring system starts making noise.
Just to make matters more interesting there is a network device between the application servers and the sql server. This device does mutliplexing so there is not a 1:1 relationship between the application server connection pool, and the number of connections on the sql server. So this rules out issuing a hard connection close at the application end, which they are doing anyway.
The tempdb is fairly large, and to date it has not grown. This store proc would need to run for about 8 days with out intervention before the temp started growing. We pre-grew the tempdb.
Any suggestion on this one? I understand that as a stop gap we could check the DMV's and kill off any process matched a predefined profile, but I see that more as a temp work around not as the proper solution.
Cheers
JL
We have a stored proc that seem to execute but never deallocate the space it reserves in tempdb. During the stored proc it creates 7 temp tables each with about 100,000 - 200,00 rows and end of the proc it drops those tables and does a truncate of another table. As this stored proc runs a number of times a day the space increases..and then our monitoring system starts making noise.
Just to make matters more interesting there is a network device between the application servers and the sql server. This device does mutliplexing so there is not a 1:1 relationship between the application server connection pool, and the number of connections on the sql server. So this rules out issuing a hard connection close at the application end, which they are doing anyway.
The tempdb is fairly large, and to date it has not grown. This store proc would need to run for about 8 days with out intervention before the temp started growing. We pre-grew the tempdb.
Any suggestion on this one? I understand that as a stop gap we could check the DMV's and kill off any process matched a predefined profile, but I see that more as a temp work around not as the proper solution.
Cheers
JL
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey David,
Will give that a go, looks good. Stay tuned.
Cheers
JL
Will give that a go, looks good. Stay tuned.
Cheers
JL
ah
Some of my last comment should be
... explicit if exists drop.
Regards
David
Some of my last comment should be
... explicit if exists drop.
Regards
David
ASKER
I can confirm it is the stored proc. Our monitoring solution is about to show what SPID is using what in tempdb down to the individual file level.
I did however run the command just to be 100% sure..and use the monitoring tool and query match up.
Cheers
JL