ASE Database forum - Single User Mode problem

Author

Single User Mode problem

Al. savop

2005-04-19, 11:24 am

We are facing a strange behaviour concerning the "single
user" Mode (ASE 12.5.2/EBF 12060).

Our DB is left in "single User" Mode by somekind of
transaction, and each try to access doesn't have any
success.
Despite some efforts we can't force, as an sa User, the
Change of our DB to Multi User Mode (eg. via sp_dboption
<DBNAME>, 'single user', false).
A look on the sysprocesses TABLE didn't reveal any
transaction upon it (via the dbid Field) and we also
couldn't identify any strange Status bit set on the
sysdatabases TABLE.
Our DB still remains on "single user" Mode and we are
wondering if there exists a way (hardway???) of altering the
present Mode without having to restart our ASE.

Thanks
Al. savop

Mark A. Parsons

2005-04-19, 8:24 pm

What happens when you run "sp_dboption <DBNAME>, 'single user', false" ?

Do you get any error messages and if so, what are those error messages?

Al., savop wrote:

> We are facing a strange behaviour concerning the "single
> user" Mode (ASE 12.5.2/EBF 12060).
>
> Our DB is left in "single User" Mode by somekind of
> transaction, and each try to access doesn't have any
> success.
> Despite some efforts we can't force, as an sa User, the
> Change of our DB to Multi User Mode (eg. via sp_dboption
> <DBNAME>, 'single user', false).
> A look on the sysprocesses TABLE didn't reveal any
> transaction upon it (via the dbid Field) and we also
> couldn't identify any strange Status bit set on the
> sysdatabases TABLE.
> Our DB still remains on "single user" Mode and we are
> wondering if there exists a way (hardway???) of altering the
> present Mode without having to restart our ASE.
>
> Thanks
> Al. savop

Al. savop

2005-04-20, 3:24 am

The according errornumber is 924 ("Database '<DBNAME>' is
already open and can only have one user at a time").

> What happens when you run "sp_dboption <DBNAME>, 'single
> user', false" ?
>
> Do you get any error messages and if so, what are those
> error messages?

Luc Van der Veurst

2005-04-20, 3:24 am

<Al. savop> wrote in message news:42651db8.648b.1681692777@sybase.com...
> We are facing a strange behaviour concerning the "single
> user" Mode (ASE 12.5.2/EBF 12060).
>
> Our DB is left in "single User" Mode by somekind of
> transaction, and each try to access doesn't have any
> success.
> Despite some efforts we can't force, as an sa User, the
> Change of our DB to Multi User Mode (eg. via sp_dboption
> <DBNAME>, 'single user', false).
> A look on the sysprocesses TABLE didn't reveal any
> transaction upon it (via the dbid Field) and we also
> couldn't identify any strange Status bit set on the
> sysdatabases TABLE.

Are there any tuples in master..syslocks whith dbid = <DBID> ?

Luc.

Mark K

2005-04-20, 7:24 am

The reason you can't take the database out of single user mode is because
sp_dboption needs to "use" the database and it already has its single user,
so sp_dboption fails. If you haven't recycled yet, you can run
dbcc traceon(3604)
go
dbcc dbtable(<dbname> )
go
and look in the first few lines for dbt_keep=x. You will probably see this
is 1. That means something is still active in that database. There isn't
much you can do except recycle. Efforts are underway to try and fix these
issues where the dbt_keep count becomes what appears to be incorrect. In
reality, there is usually some open descriptor that prevents the dbt_keep
value from returning to 0. There is a new dbcc command in 12.5.3 that helps
in some situations, but that doesn't help you on 12.5.2.

The bigger question is how your database keeps getting into single user
mode. From the dbcc dbtable output above, you should find (near the bottom)
dbt_singleuser=<address>. This is the address of the PSS that is the single
user. You can take that address and run
dbcc bytes(<address>,0,PSS)
There will be a bit of output here, but you'll want to look near the
beginning for pspid (spid), psuid (suid), and pnetbufp (network buffer). OK,
so from the suid you should know WHO did this, from spid you'll know
esentially WHERE, and for HOW, run
dbcc bytes(<network buffer address>, 100)
which will print 100 bytes from the network buffer (SQL statement). Note
that if the SQL statement is less than 100 bytes, there will be other
information from previous statements, but it should be pretty obvious.

One important point is that the WHO, WHERE, and HOW applies to the current
single user, which may not be the same as the user who actually put the
database in single user mode.

Finally, are there any locks on sysdatabases table (object id 30) in master
database?

Mark Kusma
http://www.sybase.com/support/about...t/otherservices

<Al. savop> wrote in message news:4265ed2c.6ccb.1681692777@sybase.com...[color=darkred]
> The according errornumber is 924 ("Database '<DBNAME>' is
> already open and can only have one user at a time").
>

Sponsored Links