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.

spid for #tables

5 posts in General Discussion Last posting was on 2009-10-07 10:39:02.0Z
vtpcnk Posted on 2009-10-06 12:34:11.0Z
Sender: 13c.4acb386f.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: spid for #tables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4acb3943.163.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Oct 2009 05:34:11 -0700
X-Trace: forums-1-dub 1254832451 10.22.241.41 (6 Oct 2009 05:34:11 -0700)
X-Original-Trace: 6 Oct 2009 05:34:11 -0700, 10.22.241.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28409
Article PK: 77652

how do you figure out which spid created #tables in tempdb?

also i have a situation. we get alert mails when tempdb
fills up. i got one. it identified a spid as filling up
tempdb's log. i log into the server but the spid doesn't
exist anymore. tempdb's log is full or nearly so. there is
one other process doing an insert which status 'running' -
so it doesn't show LOG SUSPEND. no other process show
rollback or anything like that.

the free space in the log segment doesn't increase or
decrease.

what is happening?

appreciate the feedback.


"Mark A. Parsons" <iron_horse Posted on 2009-10-06 14:20:32.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: spid for #tables
References: <4acb3943.163.1681692777@sybase.com>
In-Reply-To: <4acb3943.163.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091004-0, 10/04/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4acb5230$1@forums-1-dub>
Date: 6 Oct 2009 07:20:32 -0700
X-Trace: forums-1-dub 1254838832 10.22.241.152 (6 Oct 2009 07:20:32 -0700)
X-Original-Trace: 6 Oct 2009 07:20:32 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28411
Article PK: 77653


vtpcnk wrote:
> how do you figure out which spid created #tables in tempdb?

Each #temp table has a 17-digit suffix which includes, among other things, the spid of the process that created the table.

I don't have the exact 17-digit format handy at the moment, but you shouldn't have too much of a problem figuring out
which of the 5 digits represent the spid if you perform the following a few times from connections with different @@spid
values:

=====================
select @@spid -- what is your spid
go
use tempdb
go
create table #t1 (a int)
go
select name from sysobjects where name like '#t1%'
order by 1
go

-- look for your @@spid value in the sysobjects.name column

=====================

> also i have a situation. we get alert mails when tempdb
> fills up. i got one. it identified a spid as filling up
> tempdb's log. i log into the server but the spid doesn't
> exist anymore. tempdb's log is full or nearly so. there is
> one other process doing an insert which status 'running' -
> so it doesn't show LOG SUSPEND. no other process show
> rollback or anything like that.

I'm not aware of any ASE-supplied method of stating which spid is filling up a database, and there's certainly no
ASE-supplied method of sending any info via an email.

Net result is that you need to track down the process that's generating the email and find out why/how it's coming up
with a particular spid as the culprit.

> the free space in the log segment doesn't increase or
> decrease.
>
> what is happening?


J Posted on 2009-10-06 16:31:29.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: spid for #tables
Reply-To: J@bogusemailAddress.com
Message-ID: <4acb709d.5453651@forums.sybase.com>
References: <4acb3943.163.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 6 Oct 2009 09:31:29 -0700
X-Trace: forums-1-dub 1254846689 10.22.241.152 (6 Oct 2009 09:31:29 -0700)
X-Original-Trace: 6 Oct 2009 09:31:29 -0700, vip152.sybase.com
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28413
Article PK: 77656

On 6 Oct 2009 05:34:11 -0700, vtpcnk wrote:

You could issue:

in tempdb
issue select * from sysobjects where name like "#xxxxx%" where xxxxx
represents unique part of the name.

Jay

>how do you figure out which spid created #tables in tempdb?
>
>also i have a situation. we get alert mails when tempdb
>fills up. i got one. it identified a spid as filling up
>tempdb's log. i log into the server but the spid doesn't
>exist anymore. tempdb's log is full or nearly so. there is
>one other process doing an insert which status 'running' -
>so it doesn't show LOG SUSPEND. no other process show
>rollback or anything like that.
>
>the free space in the log segment doesn't increase or
>decrease.
>
>what is happening?
>
>appreciate the feedback.


Sherlock, Kevin [TeamSybase] Posted on 2009-10-06 18:13:04.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4acb3943.163.1681692777@sybase.com>
Subject: Re: spid for #tables
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4acb88b0$1@forums-1-dub>
Date: 6 Oct 2009 11:13:04 -0700
X-Trace: forums-1-dub 1254852784 10.22.241.152 (6 Oct 2009 11:13:04 -0700)
X-Original-Trace: 6 Oct 2009 11:13:04 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28414
Article PK: 77657

depends on which version of ASE you are on.
Pre ASE-15 stores this info in bytes 16-20 of the name, ASE 15 stores that
in the 5 bytes of the name starting 14 bytes from the end of the name.

Try this sql which attempts to be version agnostic:

select * into #t1 from sysobjects
go
select ase_version = @@version_as_integer
,myspid = @@spid
,table_name = name
,creator_spid = convert(int,
case
when @@version_as_integer < 15000
then substring(name,16,5)
else substring(right(name,17),3,5)
end)
,nesting_level = convert(int,
case
when @@version_as_integer < 15000
then substring(name,14,2)
else substring(right(name,17),1,2)
end)
from tempdb..sysobjects
where name like "#%"
go
drop table #t1
go

<vtpcnk> wrote in message news:4acb3943.163.1681692777@sybase.com...
> how do you figure out which spid created #tables in tempdb?
>
> also i have a situation. we get alert mails when tempdb
> fills up. i got one. it identified a spid as filling up
> tempdb's log. i log into the server but the spid doesn't
> exist anymore. tempdb's log is full or nearly so. there is
> one other process doing an insert which status 'running' -
> so it doesn't show LOG SUSPEND. no other process show
> rollback or anything like that.
>
> the free space in the log segment doesn't increase or
> decrease.
>
> what is happening?
>
> appreciate the feedback.


Rob V [ Sybase ] Posted on 2009-10-07 10:39:02.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4acb3943.163.1681692777@sybase.com>
Subject: Re: spid for #tables
Lines: 41
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4acc6fc6@forums-1-dub>
Date: 7 Oct 2009 03:39:02 -0700
X-Trace: forums-1-dub 1254911942 10.22.241.152 (7 Oct 2009 03:39:02 -0700)
X-Original-Trace: 7 Oct 2009 03:39:02 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28415
Article PK: 77658

See www.sypron.nl/temptab.html .

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"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

<vtpcnk> wrote in message news:4acb3943.163.1681692777@sybase.com...
> how do you figure out which spid created #tables in tempdb?
>
> also i have a situation. we get alert mails when tempdb
> fills up. i got one. it identified a spid as filling up
> tempdb's log. i log into the server but the spid doesn't
> exist anymore. tempdb's log is full or nearly so. there is
> one other process doing an insert which status 'running' -
> so it doesn't show LOG SUSPEND. no other process show
> rollback or anything like that.
>
> the free space in the log segment doesn't increase or
> decrease.
>
> what is happening?
>
> appreciate the feedback.