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.

Cannot drop the procedure 'xxx' because it is currently in use

5 posts in General Discussion Last posting was on 2011-05-20 19:18:37.0Z
Manuel Espinoza Posted on 2011-05-16 13:46:46.0Z
Sender: 4721.4dd129d8.1804289383@sybase.com
From: Manuel Espinoza
Newsgroups: sybase.public.ase.general
Subject: Cannot drop the procedure 'xxx' because it is currently in use
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dd12ac6.473f.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 May 2011 06:46:46 -0700
X-Trace: forums-1-dub 1305553606 10.22.241.41 (16 May 2011 06:46:46 -0700)
X-Original-Trace: 16 May 2011 06:46:46 -0700, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30169
Article PK: 72349

Hi

We're using ASE 15.0.2 for Linux.

I'm trying to modify an SP I just created last week, however
I just see the message "Cannot drop the procedure 'XXX'
because it is currently in use" but nobody else is in that
database. How can I "unlock" this SP?

Thanks in advance.


Cory Sane [TeamSybase] Posted on 2011-05-17 07:13:34.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4dd12ac6.473f.1681692777@sybase.com>
In-Reply-To: <4dd12ac6.473f.1681692777@sybase.com>
Subject: Re: Cannot drop the procedure 'xxx' because it is currently in use
Lines: 18
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18263
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dd2201e@forums-1-dub>
Date: 17 May 2011 00:13:34 -0700
X-Trace: forums-1-dub 1305616414 10.22.241.152 (17 May 2011 00:13:34 -0700)
X-Original-Trace: 17 May 2011 00:13:34 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30170
Article PK: 72348

It truly means that the procedure is in use when you attempted to delete it.


--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Manuel Espinoza" wrote in message news:4dd12ac6.473f.1681692777@sybase.com...
> Hi
>
> We're using ASE 15.0.2 for Linux.
>
> I'm trying to modify an SP I just created last week, however
> I just see the message "Cannot drop the procedure 'XXX'
> because it is currently in use" but nobody else is in that
> database. How can I "unlock" this SP?
>
> Thanks in advance.


Manuel Espinoza Posted on 2011-05-17 13:36:50.0Z
Sender: 4aab.4dd1554c.1804289383@sybase.com
From: Manuel Espinoza
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot drop the procedure 'xxx' because it is currently in use
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dd279f2.6030.1681692777@sybase.com>
References: <4dd2201e@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 May 2011 06:36:50 -0700
X-Trace: forums-1-dub 1305639410 10.22.241.41 (17 May 2011 06:36:50 -0700)
X-Original-Trace: 17 May 2011 06:36:50 -0700, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30175
Article PK: 72353

Well, I'm sure nobody else was using the database. I tried
to delete it many times yesterday and today too, with no
luck.

> It truly means that the procedure is in use when you
> attempted to delete it.
>
>
> --
> Cory Sane
> [TeamSybase]
> Certified Sybase Associate DBA for ASE 15.0
> "Manuel Espinoza" wrote in message
> > news:4dd12ac6.473f.1681692777@sybase.com... Hi
> >
> > We're using ASE 15.0.2 for Linux.
> >
> > I'm trying to modify an SP I just created last week,
> > however I just see the message "Cannot drop the
> > procedure 'XXX' because it is currently in use" but
> > nobody else is in that database. How can I "unlock" this
> > SP?
> > Thanks in advance.


Bret Halford Posted on 2011-05-17 17:27:24.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot drop the procedure 'xxx' because it is currently in use
References: <4dd2201e@forums-1-dub> <4dd279f2.6030.1681692777@sybase.com>
In-Reply-To: <4dd279f2.6030.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: <4dd2affc$1@forums-1-dub>
Date: 17 May 2011 10:27:24 -0700
X-Trace: forums-1-dub 1305653244 10.22.241.152 (17 May 2011 10:27:24 -0700)
X-Original-Trace: 17 May 2011 10:27:24 -0700, vip152.sybase.com
Lines: 109
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30180
Article PK: 72358


On 5/17/2011 7:36 AM, Manuel Espinoza wrote:
> Well, I'm sure nobody else was using the database. I tried
> to delete it many times yesterday and today too, with no
> luck.
>
>
>> It truly means that the procedure is in use when you
>> attempted to delete it.
>>
>>
>> --
>> Cory Sane
>> [TeamSybase]
>> Certified Sybase Associate DBA for ASE 15.0
>> "Manuel Espinoza" wrote in message
>>> news:4dd12ac6.473f.1681692777@sybase.com... Hi
>>>
>>> We're using ASE 15.0.2 for Linux.
>>>
>>> I'm trying to modify an SP I just created last week,
>>> however I just see the message "Cannot drop the
>>> procedure 'XXX' because it is currently in use" but
>>> nobody else is in that database. How can I "unlock" this
>>> SP?
>>> Thanks in advance.

Procedures in a database can be executed from other databases
with a fully qualified name:

exec mydb..my_proc


But assuming the procedure isn't actually in use, the simplest way to
clear condition is to reboot ASE.

We can try to get some more information, though.
Try this - save the output of dbcc procbuf to a file

dbcc traceon(3604)
go
dbcc procbuf
go

search the output for the name of your procedure (it may be in there
multiple times).

As an example, I create and execute a procedure named "my_proc"
1> create procedure my_proc as print "hi"
2> go
1> execute my_proc
2> go
hi

The output is MRU (most recently used), so the entry for this proc
is at the top. Looks like this:

PROCEDURES (in MRU to LRU order):




PROCBUF:
address=0xfa72f3b0 id=1731596492 pbdbid=2
pbplannum=34
pbuid=1 pbihash=0x0 pbnhash=0x0 pbprev=0xfa72ea20
pbtimestamp: May 17 2011 11:08AM
pbnext=0xf701514c pbprochdr=0xfbf7f800 pbparam=0x0 pbtype=4
pbstate=1 pbstat=0x0 pbusecnt=0 pbspid=0 pbdes=0xfa2cc2f8
pbxdes=0x0
pbname='my_proc' pbprocnum=1


PROC_HDRs:
Memory usage for all PROC_HDRs starting at 0xfbf7f800
--------------------------------------------------------
PROC_HDR at 0xfbf7f800
p_hdrstep=0xfbf7fd78 p_hdrseq=0x0 p_hdrcrt=0x0
p_hdrpbuf=0xfa72f3b0 p_hdrtmps=0x0 p_hdrcaller=0xfbf31000
p_hdrelease=0 p_hdrtabid=1731596492 p_hdrsub=0x0
p_hdrsubcnt=0 p_css=0x0 p_csscnt=0
p_hdrstatus=1025 ((0x00000400 (SACREATED), 0x00000001 (CACHED)))

p_hdrstat2=128 ((0x00000080 (OWNER_OPT_PERM_CHECK)))
p_lastpg=0 p_lastoff=1 p_procnum=0 p_hdrdbid=2 p_tmpdbid=0
p_lostcnt=33 p_hdrtype=2 p_pmctrlp=0xfbeb6f30

Memory Allocation Map:

[snip]

You can see the name in field "pbname". pbspid would contain
the spid number for the session executing this instance of the
procs query plan.

How many entries do you find for your procedure, do any of them
have a non-0 spid? If so, is that spid missing in sysprocesses?
(that would be called a phantom process, more commonly seen
with locks). If missing, any serious errors involving that spid
in the errorlog indicating a perhaps untidy death of the session?

Most lines in the errorlog have a header that looks like this:
00:00:00000:00017:2011/05/17 10:21:37.44
even if the error message doesn't mention a spid, the header contains
the spid in the 4th filed (here it is 17).

Cheers,

-bret


jobless Posted on 2011-05-20 19:18:37.0Z
Sender: 5853.4dd69fe3.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot drop the procedure 'xxx' because it is currently in use
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dd6be8c.5d65.1681692777@sybase.com>
References: <4dd12ac6.473f.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 20 May 2011 12:18:37 -0700
X-Trace: forums-1-dub 1305919117 10.22.241.41 (20 May 2011 12:18:37 -0700)
X-Original-Trace: 20 May 2011 12:18:37 -0700, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30190
Article PK: 72368


> Hi
>
> We're using ASE 15.0.2 for Linux.
>
> I'm trying to modify an SP I just created last week,
> however I just see the message "Cannot drop the procedure
> 'XXX' because it is currently in use" but nobody else is
> in that database. How can I "unlock" this SP?
>
> Thanks in advance.

You can do this workaround:
- rename the procedure XXX to XXX9876543210
Now you can create your procedure. Any new proc execution
will pick up the new XXX proc.

- At a later date(once you are back from vacation) you can
safely drop XXX9876543210 (it would not be in use or your
ASE would have been rebooted).