Orphan temp tables - SQL Server Forums

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

Other SQL Server Topics (2005)

Orphan temp tables

Author

Topic

xyzstarr
Starting Member

6 Posts

Posted - 2008-08-14 : 03:34:57

Hi all

I call them orphan temp tables, because processes which created these tables died days ago. Since I'm new, I don't know the correct SQL terminology.

The problem is that I can't delete these temp tables by using
DROP TABLE #TABLE_NAME

I get this message
Cannot drop the table '#TABLE_NAME', because it does not exist or you do not have permission.

I know the table is there, I just don't have the permission. Is there a way of getting rid of these tables without having to restart the service? This is out production server.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 03:58:08

Temp tables are automatically dropped when last connection referencing them closes.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 03:59:32

Try this

SELECT	*
FROM	TempDB.INFORMATION_SCHEMA.TABLES
WHERE	TABLE_NAME LIKE '#%'

E 12°55'05.25"
N 56°04'39.16"

xyzstarr
Starting Member

6 Posts

Posted - 2008-08-14 : 04:32:38

quote:
Originally posted by Peso

Temp tables are automatically dropped when last connection referencing them closes.

E 12°55'05.25"
N 56°04'39.16"

Thanks for the reply

That's true...or it should be that way. I've logged in and out of the database server about 10 times and the temporary tables are still there.

I'm trying to avoid having to reboot the machine. There must be another way around.

xyzstarr
Starting Member

6 Posts

Posted - 2008-08-14 : 04:35:57

quote:
Originally posted by Peso

Try this
SELECT *
FROM TempDB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '#%'

E 12°55'05.25"
N 56°04'39.16"

I can see all my previously created temp tables after running your code.

I can also see them when I expand Server >> Databases >> System Databases >> tempdb >> Temporary Tables under SSMS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 04:55:22

Are they local temp tables or global temp tables?
Are they prefixed with a single "#" or a double "##" ?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 04:56:33

It si not enough to log in and logout.
The query window from where you created the table need to disconnect. Either close the query window or right-click and choose disconnect.

E 12°55'05.25"
N 56°04'39.16"

xyzstarr
Starting Member

6 Posts

Posted - 2008-08-14 : 05:00:04

They are all local temp tables (single #) some created using dynamic sql like ...
exec('select * into #temp_table from table1')

and some tables were created in line sql like ...

select * into #temp_table2 from table2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 05:07:46

When the connection is closed from where the table were created, the table will be dropped.
A SQL service restart will also close all connections and thus drop all temp tables.

E 12°55'05.25"
N 56°04'39.16"

xyzstarr
Starting Member

6 Posts

Posted - 2008-08-14 : 06:12:32

quote:
Originally posted by Peso

When the connection is closed from where the table were created, the table will be dropped.
A SQL service restart will also close all connections and thus drop all temp tables.

E 12°55'05.25"
N 56°04'39.16"

All those connections which created the tables where closed long time ago. However the tables still exist.

Is there no alternative to restarting the service. It'd cause havoc if the service experienced an unplanned restart. OMG, I can already see the problems if we were to restart the service for two seconds.

Thanks Peso

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 06:17:27

this may help...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107773

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 06:29:57

Xyzstarr, even if all connections seems to be closed, the SQL Server thinks otherwise.
SQL Server keeps the connection open it is part of connection pooling, or something went wrong with a statement in the current connection.

For example, if someone was using SP_OA procs and forgot to shut the door which left connections open with their associated temp tables.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 06:53:58

This worked for me to close orphaned connections and thus orphaned tables.
It does not restart complete sql server. It "restarts" the database in the code only.

ALTER DATABASE [Foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [Foo] SET MULTI_USER
GO

E 12°55'05.25"
N 56°04'39.16"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-14 : 08:01:00

you can't drop them. They are created when TempDB is in use like :
Table variable,CheckDB,online indexing and sorting. They gets dropped automatically.Nothing to worry about.

msigal
Starting Member

1 Post

Posted - 2008-09-18 : 12:19:51

So, the only solutions I see here are restart SQL server (which rebuilds tempdb) or messing with the database. Neither sounds that appealing. I have the same problem. I've checked for orphaned connections (http://support.microsoft.com/kb/137983) and there aren't any. The temp tables are still there and can't be deleted. Using Object_ID() on the table name (using both short and long name) returns null, but I can see it in sys.tables. And Object_Name() on the id from sys.tables returns a name.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-18 : 12:45:37

TEMPDB are used by lots of operation in SQL 2005. # doesn't always mean they are created by Temp table.

xyzstarr
Starting Member

6 Posts

Posted - 2008-10-07 : 09:38:48

quote:
Originally posted by msigal

So, the only solutions I see here are restart SQL server (which rebuilds tempdb) or messing with the database. Neither sounds that appealing. I have the same problem. I've checked for orphaned connections (http://support.microsoft.com/kb/137983) and there aren't any. The temp tables are still there and can't be deleted. Using Object_ID() on the table name (using both short and long name) returns null, but I can see it in sys.tables. And Object_Name() on the id from sys.tables returns a name.

I fully agree with you msigal. Restarting the service surely gets rid of these tables. Luckily for me, the server was due for a scheduled periodical reboot. If it wasn't for the reboot, or something as critical as that, those temp tables would not have gone away.

nduckste
Starting Member

2 Posts

Posted - 2008-10-14 : 15:42:20

I'm in the same boat as well. Orphaned tables, no orphaned connections. All the tables are from the code I'm trying to run (i.e. like tempdb tables are orphaned) and I get the message the the "constraint" already exists when I try to create the table with a PK. Yet if I try to first drop the tables, I get a message that the table doesn't exist.

Looks to me like bouncing the SQL Server is the only option.

quote:
Originally posted by xyzstarr

quote:
Originally posted by msigal

So, the only solutions I see here are restart SQL server (which rebuilds tempdb) or messing with the database. Neither sounds that appealing. I have the same problem. I've checked for orphaned connections (http://support.microsoft.com/kb/137983) and there aren't any. The temp tables are still there and can't be deleted. Using Object_ID() on the table name (using both short and long name) returns null, but I can see it in sys.tables. And Object_Name() on the id from sys.tables returns a name.

I fully agree with you msigal. Restarting the service surely gets rid of these tables. Luckily for me, the server was due for a scheduled periodical reboot. If it wasn't for the reboot, or something as critical as that, those temp tables would not have gone away.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources