September 6, 2012 at 8:24 am
Hi all,
it's not the first time I face the problem, now it's time to ask the pros:
Probably due to an outage something went wrong with some SQL Jobs, multiple temp tables were created and an error occoured, because
the temp table use a named PK that can only be set once per DB.
So far, so ugly, I will change the code, so that this won't happen.
But anyway, what makes me mad is, that any attempt to delete the temp tables directly from tempdb fails, even though Google says, it's doable:
USE tempdb
SELECT object_id(NAME), *
FROM dbo.sysobjects
WHERE crdate < DATEADD(hh, -12, GETDATE()) AND
type = 'U'
--AND (name LIKE '#TA%' OR name LIKE '#Int%')
shows me the tables I want to delete (12 hours or older). My questions
1. They all have no session information (object_id=NULL), how does this happen?
So: i can't drop the session, that use the temptables
2. why does a DROP TABLE #XXX_[...]__000000036870 give the information:
"Cannot drop the table '#XXX_[...]__000000036870', because it does not exist or you do not have permission."
... I tried it with sysadmin permission.
Are these phantom tables? What is happening?
Thank you very much.
September 6, 2012 at 8:39 am
Local temporary tables are only visible to the session that created them (even though you can see them in the sys.objects system view, which is what you should be using instead of dbo.sysobjects).
If you really need to clean up phantom tables, the best way to do that is schedule some down time and restart SQL Server. This will recreate tempdb.
September 6, 2012 at 8:47 am
Thanks a lot for the ultrafast reply Lynn,
I really hoped there is another way, because it's a productive server,
I have to change a SP on this and someone must be informed to restart it after working hours.
Tempdb cleaning and size issues as well as size issues regarding the Log file:
these are topics I would love to resolve without restarting the server:)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply