October 16, 2006 at 2:42 pm
We seem to be experiencing an excess of stranded objects in TempDB on high volume datasets in SQL 2005 Developer Edition. Any one else experiencing this? Any thoughts on how to clean up these objects without cycling the service?
October 19, 2006 at 8:00 am
This was removed by the editor as SPAM
October 19, 2006 at 9:08 am
When I create a temp table or other temp objects then when after writing Create Table #TableName line as a part of the statement I add on the next line: Drop Table #TableName. Then I make a space between these 2 lines and write more code in between. This way you will not forget to drop the table. Another way is to use local temp tables ##TableName. They go away when the connection is closed.
Regards,Yelena Varsha
October 19, 2006 at 12:44 pm
We actually are already following best practice and using explicit drop statements but have experienced some unusual behavoir such that under certain circumstances where an error or other disconnect occurs, the object becomes stranded. Even after all connections are closed or terminated.
October 20, 2006 at 4:17 pm
I've also experienced this issue. We have several temp tables that do not get dropped when the scope of the procedure is finished. And we are using local temp tables (#temp) not global temp tables.
A.J.
DBA with an attitude
August 10, 2007 at 3:40 pm
For anyone who stumbles upon this, this is a known issue and is addressed in SP2 for SQL 2005.
August 10, 2007 at 4:25 pm
Thanks for the info.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply