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.

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.

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 ...

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.

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