January 7, 2009 at 5:30 am
Hello,
On one of our servers with SQL Server 2005 SP2 we noticed a strange problem with our tempdb.
#temp tables are used regular, but we have problem with one of the temp tables. The #temp table should be dropped as soon as a connection closes, goes out of scope, etc., but we have one #temp table that doesn't get dropped. It just keeps building up. We have now thousands of of this #temp table in our tempdb and they have been staying there for months.
Normally you would expect that as soon as a procedure or SSIS package or whatever else is finished, it would get out of the scope and the temp table should be dropped automaticly, even if the drop table statement hasn't been used.
Our oldest spid is a few days old, is there anybody that knows how it is possible that these tables stay in the database for many months?
Is there also a way to clean up these unused tempory tables?
Any suggestions are welcome.
Kind Regards,
Sjors
January 7, 2009 at 5:39 am
SQL 2005 caches the structure of frequently used temp tables. It's an optimisation to reduce the impact of frequently creating and dropping the same table. That may be what you're seeing.
If that is the case, they'll all be taking minimal space (1 page, I think)
Is it causing problems?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 10:42 am
GilaMonster (1/7/2009)
SQL 2005 caches the structure of frequently used temp tables. It's an optimisation to reduce the impact of frequently creating and dropping the same table. That may be what you're seeing.If that is the case, they'll all be taking minimal space (1 page, I think)
Is it causing problems?
Didn't know this. Does this apply if you explicitly drop the temp tables?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2009 at 11:51 am
If you have a stored procedure that uses a temp table the common assumption is that its deleted after the SP executes because you don't have drop it explicity in your code. In SQL Server 2005 is actually truncated and the definition re-used on the next execution of the stored procedure if it meets the criteria above.
Try executing DBCC FREEPROCCACHE and see if it helps.
MJ
January 7, 2009 at 12:16 pm
This bug ( http://support.microsoft.com/kb/947204/ ) is resolved in SQL/Server 2005 CU6 ( http://support.microsoft.com/kb/946608/ ).
To see all of the bugs fixed go to:
http://support.microsoft.com/kb/946608/
DeWayne
January 8, 2009 at 1:56 am
Thanks everybody for the reactions.
An upgrade to service pack 3 is planned in the next few weeks. Hopefully this will solve our problem.
Sjors
January 8, 2009 at 4:54 am
Jack Corbett (1/7/2009)
GilaMonster (1/7/2009)
SQL 2005 caches the structure of frequently used temp tables. It's an optimisation to reduce the impact of frequently creating and dropping the same table. That may be what you're seeing.If that is the case, they'll all be taking minimal space (1 page, I think)
Is it causing problems?
Didn't know this. Does this apply if you explicitly drop the temp tables?
Not sure. I just ran across this recently in one of Kalen's books. There weren't many details given.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2009 at 4:56 am
T D McCallie (1/7/2009)
This bug ( http://support.microsoft.com/kb/947204/ ) is resolved in SQL/Server 2005 CU6 ( http://support.microsoft.com/kb/946608/ ).
That's internal objects (worktables, table spools, index spools, etc), not temp tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply