August 1, 2013 at 9:06 am
Hi All
I run the following query to get all tempdb tables:
SELECT *
FROM tempdb.sys.sysobjects
WHERE name LIKE '#%'
ORDER BY crdate
I can see around 50 tables that are between one and two months old, and another 70 that are between one week and one month old.
I'm guessing these are created by application components that maintain a constant connection, and are never dropped or deallocated. Is there a way I can find out what procedure, application, or even connection is creating these tables? I'd like to go into whatever is creating them and add the DROP or SET = NOTHING.
Thank you
Be still, and know that I am God - Psalm 46:10
August 1, 2013 at 3:48 pm
Hi,
How about the refdate dates? are they two months ago, or fresher?
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
August 2, 2013 at 1:36 am
Is the # followed by eight hex digits? In that case, they are temp-table definitions that SQL Server has cached. There is no reason to meddle with them.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 2, 2013 at 6:46 am
Thank you for the replies.
The refdate field is equal to the crdate field in most, if not all, of the records.
Most of the records are #[eight digit hex], but there are some from about two weeks ago where it looks like this:
#CMTMPCOPYIDS_______________________________________________________________________________________________________000000058082
Does SQL Server leave temp tables in the cache for 2+ months? Is there some setting that could possibly be adjusted clean this up?
Be still, and know that I am God - Psalm 46:10
August 2, 2013 at 7:58 am
The temp objects are not deleted. They are just truncated and then reused for other purposes, hence you cannot know which temp object from which user object is created/used. The db engine cares about it.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
August 2, 2013 at 1:14 pm
david.gugg (8/2/2013)
Most of the records are #[eight digit hex], but there are some from about two weeks ago where it looks like this:#CMTMPCOPYIDS_______________________________________________________________________________________________________000000058082
The latter is a live table. The others are cached temp-table definitions. (Or live table variables.) Note that the live table may not have existed with that name for two months, but the stored procedure that uses this table might have been running when you checked.
Does SQL Server leave temp tables in the cache for 2+ months?
Say that you have a stored procedure that creates five temp tables, and this procedure is run regularly and the server has been up for two months. Then you should expect the temp-table definitions to hang around.
Is there some setting that could possibly be adjusted clean this up?
Well, why would you? Do you perceive any problems with them?
The purpose of the cache is to enhance performance when procedure that creates temp tables or define table variables are run heavily, and possibly in parallel in a busy enviroment.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 7, 2013 at 7:44 am
Your responses make sense, thank you both.
Be still, and know that I am God - Psalm 46:10
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply