removing system table junk in 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

removing system table junk in 2005

Author

Topic

DavidD
Yak Posting Veteran

73 Posts

Posted - 2007-11-27 : 16:56:14

Dear all,

I have some junk temp tables that have found their way into the sysobjects table. They are:
#tmp_tblClientProd__________________________________________________________________________________________________00000000012A
#tmp_tblClientProdWant______________________________________________________________________________________________000000000126
#tmp_tblClientWant__________________________________________________________________________________________________000000000127
#tmp_final_tblClient________________________________________________________________________________________________000000000128
#tmp_tblClient______________________________________________________________________________________________________000000000129

I cannot remove them with a drop table as it says they do not exist, and cannot directly edit the tables as ad hoc updates are not allowed in 2005. Unfortunately I cannot ignore them either as they are interfering with other processes.
Any ideas on how to clean them out?????

Thanks in advance
David

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-27 : 17:03:12

Temp tables are available only in the session that created them. So if you want to get rid of them, you'll need to KILL the spids that created them.

How are these temp tables interfering with other processes though?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

DavidD
Yak Posting Veteran

73 Posts

Posted - 2007-11-27 : 17:23:27

Hi Tara,

I rebooted the server which killed the relevant spids. the junk has now gone.

Thanks for your help
David

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-27 : 17:29:33

quote:
Originally posted by DavidD

I rebooted the server

That's a bit extreme for something that had an easy fix. Make sure to perform more analysis next time so that you do not need to take downtime for your database server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

DavidD
Yak Posting Veteran

73 Posts

Posted - 2008-01-24 : 21:40:43

OK - it has happened again, and this time on the production server. (the previous occasion was a test server so re-booting was not a problem)
I do not wish to re-boot, so there must be another way of removing this temp table junk. I have killed the spids involved, but the records remain.
The process is it interfering with is the statement:

if exists(select * from tempdb..sysobjects where name like '#tmp_tblCallWant%')
begin
drop table #tmp_tblcallWant
end

As the legacy junk is in there, the code tries to drop a table that is not there and errors, resulting in automatic e-mail all over the place (this code is part of a major process that runs daily). I know that I can change the code to check for the temp tables id rather than name, but this is a legacy system from 2000 (where it worked fine) and that code or similar is in many hundreds of places, so I would much rather find a way of deleting the system table information.

Thanks in advance
David

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-24 : 23:27:22

Temp table is created by sql sessions and will be dropped once the session ends like Tara said, you don't have to do anything.

DavidD
Yak Posting Veteran

73 Posts

Posted - 2008-01-25 : 20:13:47

Thats's the theory. It ain't the fact....

Not in 2005 anyway - I never had this problem when we were on 2000.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-25 : 23:45:23

Because sql2k5 has more features like page versioning, that uses tempdb. The fact is local temp table will be dropped when the session that created it ends.

DavidD
Yak Posting Veteran

73 Posts

Posted - 2008-01-28 : 17:01:44

Hi rmiao
Thanks for replying

I have already killed every spid on the server it could possibly be through the activity monitor. If it is an open session that does not show up there how do I find it and end it?

Regards
David

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-28 : 20:22:15

Take look at this one first: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032286964&CountryCode=US

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources