identifying and cleaning up tempdb objects
-
Recently I had a server that had ~ 1.5 million temporary objects in tempdb (select count(*) from tempdb.dbo.sysobjects where name like '#%'. That didn't sound right and it was much higher than my other servers which had ~ 5,000 objects. Here are several questions that I found myself asking...
- What spids are using these temporary objects?
- Is there a way to query the contents, the structure, or the space used by a temporary table?... one that was not created by you of course.
- Is it possible for a temporary table to remain in tempdb even though it's source connection has been closed?
- Is it possible to drop a temporary table that has not been created by you without restarting SQL?
Thanks in advance.
Regards,
Rubes -
Ok lets see if this helps:
1. *if* those objects are tables you should be able to know the spids that created them by using "sys.dm_tran_locks" or "sp_lock" (check dbid = 2 )
2. Only global temporary tables can be queried from another spid
3. Under normal conditions no. If there is a bug on SQL Server code you could get stuck with something there
4. Again global temporary tables can be deleted from different spids
Note Tempdb also contains objects, version-store related space etc.
You can examine the 'xtype' column of the tempdb.dbo.sysobjects table.
Hope this helps,
* Noel -
Hmmm... it helps but it's not what I wanted to hear. And it is what I assumed... I was hoping that there was a silver bullet that I was thinking. Thanks for the input.
Regards,
Rubes -
Well there's always those 2 commands
net stop server
net start server.
But that might cause some minor problems
. -
and SHUTDOWN always cleanup very well
* Noel - You guys are hilarious. I'm trying to *avoid* downtime.
Actually, I'm wondering if it has anything to do with the max server memory setting. For the server mentioned above, I had max server memory set too high and the page file was being used. After dropping max server memory and restarting SQL, it has behaved and the tempdb object counts have fluctuated nicely between 100 and 3000. Other servers have continued to climb despite dropping the max server memory. Perhaps it's still using the page file? It's a stab in the dark, but that's what I'm seeing. I will restart sql on the others and see what happens.
Regards,
Rubes
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply