Run-away connections!

[Former Member]

When I try to load a standby database from the active one, the load fails saying that :

Database in use.  A user with System Administrator (SA) role must have exclusive use of database to run load.

although I have killed all the connection to the database before hand:

spid   status

lg_name

program_name

hostprocess

cmd

------ ------------ ------------------------------ ------------------------------ ------------------------------               -----------

and the following query shows that there is no orphan spid:

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

The load does not go through and we have to reboot the server. A colleague told me that is due to run-away in memory spid which can not be detected, I have not formerly heard about it, please help me understand what is going on. If what he says is true, then SAP Sybase must find a solution to the problem. Imagine if this happens on my production server which is 24/24 7/7 server, no explanation can convince the our worldwide users; why technically a reboot is the only solution.

In resume, I would like to know if there is a way to detect hidden spid? is there a way to get rid of it without a reboot?

[Former Member · Accepted solution]

Forgot to talk about ASE version which is:

1    Adaptive Server Enterprise/15.7/EBF 21342 SMP SP101 /P/Solaris AMD64/OS 5.10/ase157sp101/3439/64-bit/FBO/Thu Jun  6 16:33:15 2013

[former_member188958 · Accepted solution]

Hi Admad,

The memory structure for each database (called a dbtable) contains a counter named "dbtkeep" which tracks the current number of users in the database.  This error is raised if the counter, for any reason, is non-zero when you try to load a database.

The primary purpose of the second query (from syslocks) is to identify normal processes that have some other database as their current database but are performing a cross-database transaction with the database you are trying to load into.  It also identifies phantom spids (badly terminated spids that leave locks behind), but this is a much less common situation.

Another possible cause for this error is the use of dbcc tune(des_bind,...), which is sometimes used for performance reasons. Each bound des increments the dbtkeep counter by one.   I don't think it is used by SAP Applications on ASE, though, and certainly shouldn't be if SAP Applications don't explicitly call for it to be used.  However, you can check to see if it has been used with

set switch on 3604

dbcc tune(hotdes, <dbid>)

If no DES structures have been bound, this should return empty output, like this:

1> dbcc tune(hotdes,4)

2> go

DES chain (bind state) for all objects in dbid 4:

Current database (4) testdb:

DBCC execution completed. If DBCC printed error messages, contact a user with

System Administrator (SA) role.

Objects can be unbound using dbcc tune(des_unbind, dbid, objectname), but the database must not be in use to run this (older manuals say the database must be in single-user mode, but this isn't correct - rather, ASE checks that the dbtkeep value is no larger than the number of bound des).

Yet another possible cause is a spid that terminated abnormally and, while not leaving behind any locks, did not decrement the dbtkeep value.  SAP engineering does fix such cases as they are identified, but they can be tricky to track down.  This might be what your colleague means by a "runaway in-memory spid", which otherwise doesn't mean anything to me.

In this case (or any other unknown cases with unknown cause), rebooting is the only way to reset the dbtkeep counter.   However, you do not have to reboot the entire server, current versions of ASE have a method to reboot individual databases.

set switch on 3604 -- send dbcc output to client
go
dbcc dbreboot("shutdown", dbname)

go

dbcc dbreboot("reboot", dbname)

go

Example:'

1> set switch on 3604

2> go

Switch 3604 ('print_output_to_client') is turned on.

All supplied switches are successfully turned on.

1> dbcc dbreboot('shutdown', testdb)

2> go

---------- Shutting Down Database 'testdb' ----------

---------- Operation on Database 'testdb' Completed Successfully ----------

1> dbcc dbreboot('reboot', testdb)

2> go

---------- Shutting Down Database 'testdb' ----------

---------- Re-starting Database 'testdb' With Recovery ----------

Recovering database 'testdb'.

Started estimating recovery log boundaries for database 'testdb'.

Database 'testdb', checkpoint=(1019, 12), first=(1019, 12), last=(1019, 12).

Completed estimating recovery log boundaries for database 'testdb'.

Started ANALYSIS pass for database 'testdb'.

Completed ANALYSIS pass for database 'testdb'.

Started REDO pass for database 'testdb'. The total number of log records to

process is 1.

Completed REDO pass for database 'testdb'.

Recovery of database 'testdb' will undo incomplete nested top actions.

Started filling free space info for database 'testdb'.

Completed filling free space info for database 'testdb'.

Started cleaning up the default data cache for database 'testdb'.

Completed cleaning up the default data cache for database 'testdb'.

Recovery complete.

Checking external objects.

Database 'testdb' is now online.

---------- Operation on Database 'testdb' Completed Successfully ----------

If you are worried that the log take a long time to recover, you could just do the first set to shutdown the database, then drop it and use CREATE DATABASE...FOR LOAD to rebuild so you can load the dump quickly.

-bret

[Former Member · Accepted solution]

Thanks Bret, you are very helpful and that as usual. Please be advised, this is not something that I normally do manually, it is a batch job which does it and if happens often it requires lots of man time and resource to do what you suggest. My expectation is a resolution to the problem by Sybase SAP.

Please report the case if my case is clear and make sense to you.

Thx.

[former_member188958 · Accepted solution]

Thinking on this a little more, there is a variation of dbcc dbreboot that will resolve the problem easily because you are just trying to load a dump.  It will just take a very small addition to your batch file.

Presumably your batch job already has some logic in it to kill any regular user connections currently active in the database.  This will do the same thing, but also handles cases where the dbtkeep counter is off.

You can use
dbcc dbreboot("shutdown_load", <dbname>)
to kill any current processes in the database (just the database, not the whole server) and reboots the database, placing it in a "for load" state.  The database can't be used by a process, but a dump can be loaded into it.

Example:

1> set switch on 3604

2> go

Switch 3604 ('print_output_to_client') is turned on.

All supplied switches are successfully turned on.

1> dbcc dbreboot("shutdown_load", test)

2> go

---------- Shutting Down Database 'test' ----------

---------- Operation on Database 'test' Completed Successfully ----------

1> use test

2> go

Msg 930, Level 14, State 1:

Server 'rel1572sp130_bret_sun2', Line 1:

Database 'test' cannot be opened because either an earlier system termination

left LOAD DATABASE incomplete or the database is created with 'for load' option.

Load the database or contact a user with System Administrator (SA) role.

1> load database test from "/work/test.dmp"

2> go

Backup Server session id is: 12. Use this value when executing the

'sp_volchanged' system stored procedure after fulfilling any volume change

request from the Backup Server.

Backup Server: 6.28.1.1: Dumpfile name 'test153090D1FE   ' section number 1

mounted on disk file '/work/test.dmp'

Backup Server: 4.188.1.1: Database test: 2760 kilobytes (13%) LOADED.

Backup Server: 4.188.1.1: Database test: 20486 kilobytes (100%) LOADED.

Backup Server: 4.188.1.1: Database test: 20496 kilobytes (100%) LOADED.

Backup Server: 3.42.1.1: LOAD is complete (database test).

Caution:  You have set up this database to include space on disk 2 for both data

and the transaction log.  This can make recovery impossible if that disk fails.

Started estimating recovery log boundaries for database 'test'.

Database 'test', checkpoint=(2625, 1), first=(2625, 1), last=(2625, 14).

Completed estimating recovery log boundaries for database 'test'.

Started ANALYSIS pass for database 'test'.

Completed ANALYSIS pass for database 'test'.

Started REDO pass for database 'test'. The total number of log records to

process is 14.

Redo pass of recovery has processed 1 committed and 0 aborted transactions.

Completed REDO pass for database 'test'.

Use the ONLINE DATABASE command to bring this database online; ASE will not

bring it online automatically.

-bret