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 database 'xxx' because it is currently in use.

5 posts in General Discussion Last posting was on 2010-05-13 03:58:57.0Z
vtpcnk Posted on 2010-05-11 07:04:33.0Z
Sender: 3511.4be900da.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Cannot drop the database 'xxx' because it is currently in use.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4be90181.3529.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 May 2010 00:04:33 -0700
X-Trace: forums-1-dub 1273561473 10.22.241.41 (11 May 2010 00:04:33 -0700)
X-Original-Trace: 11 May 2010 00:04:33 -0700, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29190
Article PK: 78425

i am trying to drop a database but getting the error that it
is currently in use.

there are nothing in sysprocesses/syslogshold for the given
db. nothing for the db in syslocks. nothing in sysreferences
either.

any idea what is happening?

appreciate the feedback.


Manish Negandhi [TeamSybase] Posted on 2010-05-11 08:40:11.0Z
Sender: 16be.4be809d4.1804289383@sybase.com
From: Manish Negandhi [TeamSybase]
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot drop the database 'xxx' because it is currently in use.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4be917eb.389c.1681692777@sybase.com>
References: <4be90181.3529.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 May 2010 01:40:11 -0700
X-Trace: forums-1-dub 1273567211 10.22.241.41 (11 May 2010 01:40:11 -0700)
X-Original-Trace: 11 May 2010 01:40:11 -0700, 10.22.241.41
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29191
Article PK: 78427


> i am trying to drop a database but getting the error that
> it is currently in use.
>
> there are nothing in sysprocesses/syslogshold for the
> given db. nothing for the db in syslocks. nothing in
> sysreferences either.
>
> any idea what is happening?
>
> appreciate the feedback.

What is your ASE version ?

Here are few things to check

(1) Many times there scripts running from crontab which
performs monitoring checks in database frequently so when
you load a db its possible that script was performing checks
in db but by the time you query the db manually, its gone.
Try setting database in single user mode and see if load
works

(2) Execute this

use master
go
dbcc traceon(3604)
go
dbcc dbtable(dbname)
go

any value greater than 0 for dbt_keep indicates that there
are some users connected to database or dbt_keep count did
not get cleanup properly. You can excute DBCC DBCACHEREMOVE
as per instructions given in the below listed CR
http://search.sybase.com/kbx/changerequests?bug_id=334132


-HTH
Manish Negandhi
[TeamSybase]


vtpcnk Posted on 2010-05-11 10:55:23.0Z
Sender: 3511.4be900da.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot drop the database 'xxx' because it is currently in use.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4be9379b.3f0d.1681692777@sybase.com>
References: <4be917eb.389c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 May 2010 03:55:23 -0700
X-Trace: forums-1-dub 1273575323 10.22.241.41 (11 May 2010 03:55:23 -0700)
X-Original-Trace: 11 May 2010 03:55:23 -0700, 10.22.241.41
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29192
Article PK: 78429

> What is your ASE version ?

Adaptive Server Enterprise/12.5.4/EBF 15432
ESD#8/P/Sun_svr4/OS 5.8/ase1254/210
5/64-bit/FBO/Sat Mar 22 14:38:37 2008

> use master
> go
> dbcc traceon(3604)
> go
> dbcc dbtable(dbname)
> go
>
> any value greater than 0 for dbt_keep indicates that

dbt_state=0x2(0x0002 (DBST_ACTIVE)) dbt_keep=1
dbt_hdeskeep=0

1> dbcc dbcacheremove(<db>)
2> go
DBCC execution completed. If DBCC printed error messages,
contact a user with
System Administrator (SA) role.

1> drop database <db>
2> go
Msg 3702, Level 16, State 4:
Server '<ds>', Line 1:
Cannot drop the database '<db>' because it is currently in
use.


1> sp_dboption <db>,"single user",true
2> go
Database option 'single user' turned ON for database '<db>'.
Running CHECKPOINT on database '<db>' for option 'single
user' to take effect.
Attempt to set '<db>' database to single user mode failed
because the usage count
is 3. Make sure that no other users are currently using this
database and rerun
CHECKPOINT.
(return status = 0)

1> select "kill",spid from sysprocesses where dbid = 24
2> go
spid
---- ------

(0 rows affected)

i am still not able to drop the db.


Vivek Kak Posted on 2010-05-13 03:58:57.0Z
Sender: 692c.4bea4318.1804289383@sybase.com
From: Vivek kak
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot drop the database 'xxx' because it is currently in use.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4beb7901.1207.1681692777@sybase.com>
References: <4be9379b.3f0d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 May 2010 20:58:57 -0700
X-Trace: forums-1-dub 1273723137 10.22.241.41 (12 May 2010 20:58:57 -0700)
X-Original-Trace: 12 May 2010 20:58:57 -0700, 10.22.241.41
Lines: 74
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29208
Article PK: 78445

Can you check if there are any orphan processes . The
dbt_keep is 1 and when you try to put database in single
user , it is showing the count as 3.

select spid from syslocks where spid not in (select spid
from master..sysprocesses)

What is the default database for the id with which you are
trying to drop the database?

If nothing works , try using below command:

dbcc dbreboot(reboot,<dbname>)
go

and then try dropping the database .

HTH,
Vivek

> > What is your ASE version ?
>
> Adaptive Server Enterprise/12.5.4/EBF 15432
> ESD#8/P/Sun_svr4/OS 5.8/ase1254/210
> 5/64-bit/FBO/Sat Mar 22 14:38:37 2008
>
>
> > use master
> > go
> > dbcc traceon(3604)
> > go
> > dbcc dbtable(dbname)
> > go
> >
> > any value greater than 0 for dbt_keep indicates that
>
> dbt_state=0x2(0x0002 (DBST_ACTIVE)) dbt_keep=1
> dbt_hdeskeep=0
>
> 1> dbcc dbcacheremove(<db>)
> 2> go
> DBCC execution completed. If DBCC printed error messages,
> contact a user with
> System Administrator (SA) role.
>
> 1> drop database <db>
> 2> go
> Msg 3702, Level 16, State 4:
> Server '<ds>', Line 1:
> Cannot drop the database '<db>' because it is currently in
> use.
>
>
> 1> sp_dboption <db>,"single user",true
> 2> go
> Database option 'single user' turned ON for database
> '<db>'. Running CHECKPOINT on database '<db>' for option
> 'single user' to take effect.
> Attempt to set '<db>' database to single user mode failed
> because the usage count
> is 3. Make sure that no other users are currently using
> this database and rerun
> CHECKPOINT.
> (return status = 0)
>
> 1> select "kill",spid from sysprocesses where dbid = 24
> 2> go
> spid
> ---- ------
>
> (0 rows affected)
>
> i am still not able to drop the db.


Luc Van der Veurst Posted on 2010-05-11 11:47:30.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4be90181.3529.1681692777@sybase.com>
Subject: Re: Cannot drop the database 'xxx' because it is currently in use.
Lines: 44
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
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be943d2$1@forums-1-dub>
Date: 11 May 2010 04:47:30 -0700
X-Trace: forums-1-dub 1273578450 10.22.241.152 (11 May 2010 04:47:30 -0700)
X-Original-Trace: 11 May 2010 04:47:30 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29193
Article PK: 78430


<vtpcnk> wrote in message news:4be90181.3529.1681692777@sybase.com...
>i am trying to drop a database but getting the error that it
> is currently in use.
>
> there are nothing in sysprocesses/syslogshold for the given
> db. nothing for the db in syslocks. nothing in sysreferences
> either.
>
> any idea what is happening?

No, but I can create at least one situation that reproduces the behaviour
you
are seeing :

connection 1 :
create database db1;
use db1;
begin transaction;
use master;

keep connection 1 open en start connection 2 :
use master;
select * from sysprocesses where dbid = db_id('db1');
(0 rows affected)
select * from syslocks where dbid = db_id('db1');
(0 rows affected)
drop database db1;
Msg 3702, Level 16, State 4
Server 'ASE1254', Line 1
Cannot drop the database 'db1' because it is currently in use.

Also syslogshold contains no tuple for database db1 :
select * from syslogshold where dbid = db_id('db1');

I'd check which connections are necessary and kill all others,
hoping I can drop the database at some point.

Or perhaps there is another way to identify the connections that
are avoiding dropping the database.

Luc.