Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Not Able to drop The #temp Table---urgent

10 posts in General Discussion Last posting was on 2011-01-25 02:08:45.0Z
Sridhar Posted on 2011-01-24 07:11:23.0Z
Sender: 60bc.4d3d2438.1804289383@sybase.com
From: Sridhar
Newsgroups: sybase.public.ase.general
Subject: Not Able to drop The #temp Table---urgent
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d3d261b.60fb.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Jan 2011 23:11:23 -0800
X-Trace: forums-1-dub 1295853083 10.22.241.41 (23 Jan 2011 23:11:23 -0800)
X-Original-Trace: 23 Jan 2011 23:11:23 -0800, 10.22.241.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29890
Article PK: 79119

Hi All,

I try to drop few of #temptables from tempdb(which are
filling the tempdb log) but I was not able to drop the
table,I switched the user to guest(as the owner of table)
with setuser.. and tried to drop the table but I am getting
the error as below... plz help me out... Thanks in
advance...

details
========
1> drop table guest.#XXX_ids______00014690022262853
2> go
Msg 3701, Level 11, State 1:
Server 'abc_lnprd_Dataserver', Line 1:
Cannot drop the table
'guest.#XXX_ids______00014690022262853', because it doesn't
exist in the system catalogs.
1> select user_name()
2> go

------------------------------
guest

1> select name from sysobjects where name like "%XXX_ids%"
2> go
name
------------------------------
#XXX_ids______00014690022262853

1> select db_name()
2> go

------------------------------
tempdb

(1 row affected)


Rob V [ Sybase ] Posted on 2011-01-24 08:02:42.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Not Able to drop The #temp Table---urgent
References: <4d3d261b.60fb.1681692777@sybase.com>
In-Reply-To: <4d3d261b.60fb.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d3d3222$1@forums-1-dub>
Date: 24 Jan 2011 00:02:42 -0800
X-Trace: forums-1-dub 1295856162 10.22.241.152 (24 Jan 2011 00:02:42 -0800)
X-Original-Trace: 24 Jan 2011 00:02:42 -0800, vip152.sybase.com
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29891
Article PK: 79120


On 24-Jan-2011 08:11, Sridhar wrote:
> Hi All,
>
> I try to drop few of #temptables from tempdb(which are
> filling the tempdb log) but I was not able to drop the
> table,I switched the user to guest(as the owner of table)
> with setuser.. and tried to drop the table but I am getting
> the error as below... plz help me out... Thanks in
> advance...
>
> details
> ========
> 1> drop table guest.#XXX_ids______00014690022262853
> 2> go
> Msg 3701, Level 11, State 1:
> Server 'abc_lnprd_Dataserver', Line 1:
> Cannot drop the table
> 'guest.#XXX_ids______00014690022262853', because it doesn't
> exist in the system catalogs.
> 1> select user_name()
> 2> go
>
> ------------------------------
> guest
>
> 1> select name from sysobjects where name like "%XXX_ids%"
> 2> go
> name
> ------------------------------
> #XXX_ids______00014690022262853
>
> 1> select db_name()
> 2> go
>
> ------------------------------
> tempdb
>
> (1 row affected)

You cannot drop #temp tables owned by other users than your own session:
these tables are specific to that session only, and others cannot drop
them with 'drop table', nor access their contents in any way.
Your table #XXX_ids______00014690022262853 is owned by the session with
session ID ('spid') 14 (see www.sypron.nl/temptab for the reason why).
If session 14 is one of yours, then run 'drop table #XXX_ids' in that
session. The only other way is to kill session 14 from another session
with 'kill 14' (this requires sa_role).

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Bret Halford Posted on 2011-01-24 16:34:26.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Not Able to drop The #temp Table---urgent
References: <4d3d261b.60fb.1681692777@sybase.com> <4d3d3222$1@forums-1-dub>
In-Reply-To: <4d3d3222$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d3daa12@forums-1-dub>
Date: 24 Jan 2011 08:34:26 -0800
X-Trace: forums-1-dub 1295886866 10.22.241.152 (24 Jan 2011 08:34:26 -0800)
X-Original-Trace: 24 Jan 2011 08:34:26 -0800, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29894
Article PK: 79123


On 1/24/2011 1:02 AM, Rob V [ Sybase ] wrote:
> You cannot drop #temp tables owned by other users than your own session:
> these tables are specific to that session only, and others cannot drop
> them with 'drop table', nor access their contents in any way.
> Your table #XXX_ids______00014690022262853 is owned by the session with
> session ID ('spid') 14 (see www.sypron.nl/temptab for the reason why).
> If session 14 is one of yours, then run 'drop table #XXX_ids' in that
> session. The only other way is to kill session 14 from another session
> with 'kill 14' (this requires sa_role).
>

There is one exception to this worth noting, which is when the spid
that owns the table no longer exists in sysprocesses. This happens
sometimes when a killed spid isn't cleaned up properly, leaving
temp tables behind. These are called "orphaned" temp tables.

Running

dbcc orphantables

will list any orphaned tables in tempdb

dbcc orphantables ("drop")

will try to clean them up.

-bret


Carl Kayser Posted on 2011-01-24 19:45:35.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4d3d261b.60fb.1681692777@sybase.com> <4d3d3222$1@forums-1-dub> <4d3daa12@forums-1-dub>
Subject: Re: Not Able to drop The #temp Table---urgent
Lines: 37
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d3dd6df$1@forums-1-dub>
Date: 24 Jan 2011 11:45:35 -0800
X-Trace: forums-1-dub 1295898335 10.22.241.152 (24 Jan 2011 11:45:35 -0800)
X-Original-Trace: 24 Jan 2011 11:45:35 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29895
Article PK: 79126


"Bret Halford" <bret@sybase.com> wrote in message
news:4d3daa12@forums-1-dub...
> On 1/24/2011 1:02 AM, Rob V [ Sybase ] wrote:
>> You cannot drop #temp tables owned by other users than your own session:
>> these tables are specific to that session only, and others cannot drop
>> them with 'drop table', nor access their contents in any way.
>> Your table #XXX_ids______00014690022262853 is owned by the session with
>> session ID ('spid') 14 (see www.sypron.nl/temptab for the reason why).
>> If session 14 is one of yours, then run 'drop table #XXX_ids' in that
>> session. The only other way is to kill session 14 from another session
>> with 'kill 14' (this requires sa_role).
>>
>
>
> There is one exception to this worth noting, which is when the spid
> that owns the table no longer exists in sysprocesses. This happens
> sometimes when a killed spid isn't cleaned up properly, leaving
> temp tables behind. These are called "orphaned" temp tables.
>
> Running
>
> dbcc orphantables
>
> will list any orphaned tables in tempdb
>
> dbcc orphantables ("drop")
>
> will try to clean them up.
>
> -bret
>
>

Ahhh, but the above dbcc commands are still unsupported, correct?


Bret Halford Posted on 2011-01-24 21:38:08.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Not Able to drop The #temp Table---urgent
References: <4d3d261b.60fb.1681692777@sybase.com> <4d3d3222$1@forums-1-dub> <4d3daa12@forums-1-dub> <4d3dd6df$1@forums-1-dub>
In-Reply-To: <4d3dd6df$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d3df140$1@forums-1-dub>
Date: 24 Jan 2011 13:38:08 -0800
X-Trace: forums-1-dub 1295905088 10.22.241.152 (24 Jan 2011 13:38:08 -0800)
X-Original-Trace: 24 Jan 2011 13:38:08 -0800, vip152.sybase.com
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29897
Article PK: 79125

Correct. DBCC ORPHANTABLES isn't documented in the manuals, may not
work in all cases, and there should be no particular
expectation that we will fix the command should it not work
as hoped. Use of the command may cause cancer in the
state of California.

hm. Can you tell I was recently listening to Tom Smith's
"Everything is Dangerous" on the Mad Music Podcast?
http://www.themadmusicarchive.com/playlist.aspx?Show=MMP-3

So far my experiences with the command have been postive.

Cheers,
-bret

On 1/24/2011 12:45 PM, Carl Kayser wrote:
> "Bret Halford"<bret@sybase.com> wrote in message
> news:4d3daa12@forums-1-dub...
>> On 1/24/2011 1:02 AM, Rob V [ Sybase ] wrote:
>>> You cannot drop #temp tables owned by other users than your own session:
>>> these tables are specific to that session only, and others cannot drop
>>> them with 'drop table', nor access their contents in any way.
>>> Your table #XXX_ids______00014690022262853 is owned by the session with
>>> session ID ('spid') 14 (see www.sypron.nl/temptab for the reason why).
>>> If session 14 is one of yours, then run 'drop table #XXX_ids' in that
>>> session. The only other way is to kill session 14 from another session
>>> with 'kill 14' (this requires sa_role).
>>>
>>
>>
>> There is one exception to this worth noting, which is when the spid
>> that owns the table no longer exists in sysprocesses. This happens
>> sometimes when a killed spid isn't cleaned up properly, leaving
>> temp tables behind. These are called "orphaned" temp tables.
>>
>> Running
>>
>> dbcc orphantables
>>
>> will list any orphaned tables in tempdb
>>
>> dbcc orphantables ("drop")
>>
>> will try to clean them up.
>>
>> -bret
>>
>>
>
> Ahhh, but the above dbcc commands are still unsupported, correct?
>
>


jobless Posted on 2011-01-24 21:39:51.0Z
Sender: 7346.4d3d8c87.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: Not Able to drop The #temp Table---urgent
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d3df1a7.c65.1681692777@sybase.com>
References: <4d3dd6df$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Jan 2011 13:39:51 -0800
X-Trace: forums-1-dub 1295905191 10.22.241.41 (24 Jan 2011 13:39:51 -0800)
X-Original-Trace: 24 Jan 2011 13:39:51 -0800, 10.22.241.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29898
Article PK: 79127


> These are called "orphaned" temp tables. >
> > Running
> >
> > dbcc orphantables
> >
> > will list any orphaned tables in tempdb
> >
> > dbcc orphantables ("drop")
> >
> > will try to clean them up.
> >
> > -bret
> >
> >
>
> Ahhh, but the above dbcc commands are still unsupported,
> correct?
>
>

This DBCC command is supported by Sybase Tech Support. I
recall using them after consulting with Tech support after
the server ran out of locks resulting in many spids aborting
and leaving orphan #tables. Infact tech support did send me
an email to run these, since i insisted getting something in
email.


Sridhar Posted on 2011-01-25 02:08:45.0Z
Sender: 13f6.4d3e270b.1804289383@sybase.com
From: Sridhar
Newsgroups: sybase.public.ase.general
Subject: Re: Not Able to drop The #temp Table---urgent
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d3e30ad.1524.1681692777@sybase.com>
References: <4d3df1a7.c65.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Jan 2011 18:08:45 -0800
X-Trace: forums-1-dub 1295921325 10.22.241.41 (24 Jan 2011 18:08:45 -0800)
X-Original-Trace: 24 Jan 2011 18:08:45 -0800, 10.22.241.41
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29899
Article PK: 79128

Thanks all for your support... killed the #tmp table
spid.... now tempdb got cleared...

> > These are called "orphaned" temp tables. >
> > > Running
> > >
> > > dbcc orphantables
> > >
> > > will list any orphaned tables in tempdb
> > >
> > > dbcc orphantables ("drop")
> > >
> > > will try to clean them up.
> > >
> > > -bret
> > >
> > >
> >
> > Ahhh, but the above dbcc commands are still unsupported,
> > correct?
> >
> >
>
> This DBCC command is supported by Sybase Tech Support. I
> recall using them after consulting with Tech support after
> the server ran out of locks resulting in many spids
> aborting and leaving orphan #tables. Infact tech support
> did send me an email to run these, since i insisted
> getting something in email.


Sridhar Posted on 2011-01-24 16:05:21.0Z
Sender: 762f.4d3da261.1804289383@sybase.com
From: Sridhar
Newsgroups: sybase.public.ase.general
Subject: Re: Not Able to drop The #temp Table---urgent
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d3da341.764c.1681692777@sybase.com>
References: <4d3d3222$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Jan 2011 08:05:21 -0800
X-Trace: forums-1-dub 1295885121 10.22.241.41 (24 Jan 2011 08:05:21 -0800)
X-Original-Trace: 24 Jan 2011 08:05:21 -0800, 10.22.241.41
Lines: 74
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29892
Article PK: 79124

Thanks Rob V....

> On 24-Jan-2011 08:11, Sridhar wrote:
> > Hi All,
> >
> > I try to drop few of #temptables from tempdb(which are
> > filling the tempdb log) but I was not able to drop the
> > table,I switched the user to guest(as the owner of
> > table) with setuser.. and tried to drop the table but I
> > am getting the error as below... plz help me out...
> > Thanks in advance...
> >
> > details
> > ========
> > 1> drop table guest.#XXX_ids______00014690022262853
> > 2> go
> > Msg 3701, Level 11, State 1:
> > Server 'abc_lnprd_Dataserver', Line 1:
> > Cannot drop the table
> > 'guest.#XXX_ids______00014690022262853', because it
> > doesn't exist in the system catalogs.
> > 1> select user_name()
> > 2> go
> >
> > ------------------------------
> > guest
> >
> > 1> select name from sysobjects where name like
> > "%XXX_ids%" 2> go
> > name
> > ------------------------------
> > #XXX_ids______00014690022262853
> >
> > 1> select db_name()
> > 2> go
> >
> > ------------------------------
> > tempdb
> >
> > (1 row affected)
> You cannot drop #temp tables owned by other users than
> your own session: these tables are specific to that
> session only, and others cannot drop them with 'drop
> table', nor access their contents in any way. Your table
> #XXX_ids______00014690022262853 is owned by the session
> with session ID ('spid') 14 (see www.sypron.nl/temptab
> for the reason why). If session 14 is one of yours, then
> run 'drop table #XXX_ids' in that session. The only other
> way is to kill session 14 from another session with 'kill
> 14' (this requires sa_role).
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>


Rob V [ Sybase ] Posted on 2011-01-24 16:22:34.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Not Able to drop The #temp Table---urgent
References: <4d3d3222$1@forums-1-dub> <4d3da341.764c.1681692777@sybase.com>
In-Reply-To: <4d3da341.764c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d3da74a$1@forums-1-dub>
Date: 24 Jan 2011 08:22:34 -0800
X-Trace: forums-1-dub 1295886154 10.22.241.152 (24 Jan 2011 08:22:34 -0800)
X-Original-Trace: 24 Jan 2011 08:22:34 -0800, vip152.sybase.com
Lines: 81
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29893
Article PK: 79121


On 24-Jan-2011 17:05, Sridhar wrote:
> Thanks Rob V....
>
>
>> On 24-Jan-2011 08:11, Sridhar wrote:
>>> Hi All,
>>>
>>> I try to drop few of #temptables from tempdb(which are
>>> filling the tempdb log) but I was not able to drop the
>>> table,I switched the user to guest(as the owner of
>>> table) with setuser.. and tried to drop the table but I
>>> am getting the error as below... plz help me out...
>>> Thanks in advance...
>>>
>>> details
>>> ========
>>> 1> drop table guest.#XXX_ids______00014690022262853
>>> 2> go
>>> Msg 3701, Level 11, State 1:
>>> Server 'abc_lnprd_Dataserver', Line 1:
>>> Cannot drop the table
>>> 'guest.#XXX_ids______00014690022262853', because it
>>> doesn't exist in the system catalogs.
>>> 1> select user_name()
>>> 2> go
>>>
>>> ------------------------------
>>> guest
>>>
>>> 1> select name from sysobjects where name like
>>> "%XXX_ids%" 2> go
>>> name
>>> ------------------------------
>>> #XXX_ids______00014690022262853
>>>
>>> 1> select db_name()
>>> 2> go
>>>
>>> ------------------------------
>>> tempdb
>>>
>>> (1 row affected)
>> You cannot drop #temp tables owned by other users than
>> your own session: these tables are specific to that
>> session only, and others cannot drop them with 'drop
>> table', nor access their contents in any way. Your table
>> #XXX_ids______00014690022262853 is owned by the session
>> with session ID ('spid') 14 (see www.sypron.nl/temptab
>> for the reason why). If session 14 is one of yours, then
>> run 'drop table #XXX_ids' in that session. The only other
>> way is to kill session 14 from another session with 'kill
>> 14' (this requires sa_role).
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>

Actually, I was wrong about the spid number owning that table. It wasn't
'14', but '1469'. Typed too fast...
Sorry for that.

Rob V.


"Mark A. Parsons" <iron_horse Posted on 2011-01-24 20:04:16.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Not Able to drop The #temp Table---urgent
References: <4d3d261b.60fb.1681692777@sybase.com>
In-Reply-To: <4d3d261b.60fb.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d3ddb40$1@forums-1-dub>
Date: 24 Jan 2011 12:04:16 -0800
X-Trace: forums-1-dub 1295899456 10.22.241.152 (24 Jan 2011 12:04:16 -0800)
X-Original-Trace: 24 Jan 2011 12:04:16 -0800, vip152.sybase.com
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29896
Article PK: 79122

Assuming ...

a - you're not referring to an orphan table [otherwise see Bret's post]

b - there are no outstanding locks on the #temp table (eg, the parent process doesn't have an open transaction holding
locks on the #temp table) [the table typically can't be dropped by anyone until the locks have been released]

c - you really, really, really need to drop the #temp table and you don't care what effect this may have on the process
that owns the #temp table

... you can try sqldbgr.

With sqldbgr you can attach to an active session/spid and do all sorts of devious things, eg, modify the values of the
session's @variables, manipulate the data in a #temp table, force a stored proc to run in debug mode (ie, allow you to
manually step through statements), drop #temp tables, etc.

Unless you're attaching to a session that's running under your login, you'll most likely need permissions to run the
'set proxy/session authorization' command.

sqldbgr is distributed with the ASE dataserver software and is located in the ${SYBASE_ASE}/bin directory.

Peter Sap has a pretty good sqldbgr write-up with examples on how to accomplish the basic stuff [
http://www.petersap.nl/SybaseWiki/index.php?title=SQL-Debugger_-_DBA_usage ]

On 01/24/2011 02:11, Sridhar wrote:
> Hi All,
>
> I try to drop few of #temptables from tempdb(which are
> filling the tempdb log) but I was not able to drop the
> table,I switched the user to guest(as the owner of table)
> with setuser.. and tried to drop the table but I am getting
> the error as below... plz help me out... Thanks in
> advance...
>
> details
> ========
> 1> drop table guest.#XXX_ids______00014690022262853
> 2> go
> Msg 3701, Level 11, State 1:
> Server 'abc_lnprd_Dataserver', Line 1:
> Cannot drop the table
> 'guest.#XXX_ids______00014690022262853', because it doesn't
> exist in the system catalogs.
> 1> select user_name()
> 2> go
>
> ------------------------------
> guest
>
> 1> select name from sysobjects where name like "%XXX_ids%"
> 2> go
> name
> ------------------------------
> #XXX_ids______00014690022262853
>
> 1> select db_name()
> 2> go
>
> ------------------------------
> tempdb
>
> (1 row affected)