Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    167

    Unanswered: Can't load database because of Ghost DB users...

    We are trying to load a database but receive the following error...

    Msg 3101, Level 16, State 1:
    Server 'SYB_INVEST', Line 1:
    Database in use. A user with System Administrator (SA) role must have exclusive
    use of database to run load.

    The problem is, when we run "sp_who" and "select * from sysprocesses", we do not see any users in the database.

    1>sp_who
    2>go

    - & -

    1> select * from sysprocesses where dbid = 9
    2> go

    Yet, when we run the sp_dboption system stored procedure, we receive the following error...

    use master
    go

    sp_dboption PROD_INVESTOR, 'single user', true
    go

    Database option 'single user' turned ON for database 'PROD_INVESTOR'.
    Running CHECKPOINT on database 'PROD_INVESTOR' for option 'single user' to take effect.
    Attempt to set 'PROD_INVESTOR' database to single user mode failed because the usage count is 5. Make sure that no other users are currently using this database and rerun CHECKPOINT.
    010P4: An output parameter was received and ignored.


    What can we do?

    Thank you.

  2. #2
    Join Date
    Mar 2007
    Posts
    167

    solution...

    Looks like a ASE bounce was the safest way to go to resolve issue.


    ----------------------------------
    -- Overall Symptoms Before ASE Bounce --
    ----------------------------------

    - database load failed with a "msg 3101 - Database in use. ..."
    - Then an attempt to set the database option to "single user" failed and received error message stating, "... usage count is 5...".
    - sp_who did not show any users in the database
    - sysprocesses did not show any users / processes using the database
    - sp_lock did not show any locks taking place for that database and database objects.
    - dbcc dbtable(dbname) shows that dbt_keep = 4.



    ----------------------------------
    -- After ASE Bounce --
    ----------------------------------

    - We went ahead and bounced the ASE server. This resolved the issue.
    - Database Load succeeded.



    ----------------------------------
    -- Issue Summary --
    ----------------------------------

    - Ghost users/processes existed in database. A "shutdown with nowait" had to be performed to resolve issue.


    Hope the above information is helpful.

  3. #3
    Join Date
    Nov 2007
    Posts
    7
    I have faced the same on my NT box (loca test m/c). Probably reboot is the only option and I did the same but any other solution to this is welcome ...

  4. #4
    Join Date
    Mar 2007
    Posts
    167

    Thank you

    Thank you for your feedback. It's nice to hear that someone else has faced the same situation and followed the same steps to resolve.

    Thank you.

  5. #5
    Join Date
    Mar 2008
    Posts
    22
    Hi,

    Please check your sysservers entry.

    #select * from sysservers

    It shoud contain following entries

    0 9 <dataserver_name> <dataserver_name> 0 [NULL] 0
    1 8 SYB_BACKUP BOLD_ORIG_back 7 [NULL] [NULL]

    Here SYB_BACKUP is your backup server.

    If SYB_BACKUP does not exit then please replace your existing backup server name from sysservers.

    Regards,
    Riyaz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •