Skip to Content
author's profile photo Former Member
Former Member

Run-away connections!

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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 05, 2015 at 04:55 PM

    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
    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.