Only user processes can be killed. Database is already open and can only have one user at a time.
Question
Thursday, March 24, 2016 1:26 PM
During restoration of database in test environment we face below issue once database gets set too single user mode.
Only user processes can be killed. Database is already open and can only have one user at a time.
Nothing worked from below steps.
-We tried to kill all session.
-We tried to put database offline.
-We tried to put database in multiuser mode.
-We tried database files attach-detach.
-We tried to wait till system process gets released still sessions not getting released.
-Last option : Restart database server which is not feasible each time.
Hoping for better assistance as issue seems not getting resolved with any available option except restart.
All replies (4)
Thursday, March 24, 2016 11:54 PM âś…Answered
As I understand it you have a process that sets the database to SINGLE_USER and then you receive the error. If this is so then you should make sure you posses the single connection by issuing the ALTER DATABASE command from within the database you want to put into single user as follow:
USE <DBA>;
ALTER DATABASE <DBA> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
If you do so from any other database then the database gets into single user but any other connection can also grab that single user before your process.
Yaniv Etrogi
site | blog | linked in | mail
Please click the Mark as Answer button if a post solves your problem! or
Vote As Helpful
Thursday, March 24, 2016 1:28 PM
During restoration of database in test environment we face below issue once database gets set too single user mode.
Only user processes can be killed. Database is already open and can only have one user at a time.
Nothing worked from below steps.
-We tried to kill all session.
-We tried to put database offline.
-We tried to put database in multiuser mode.
-We tried database files attach-detach.
-We tried to wait till system process gets released still sessions not getting released.
-Last option : Restart database server which is not feasible each time.
Hoping for better assistance as issue seems not getting resolved with any available option except restart.
Thursday, March 24, 2016 1:50 PM
hi Mayur S, we'd need answers to your own declarations in order provide you a correct solution (err codes, messages and so on) Thanks
Thursday, March 24, 2016 2:09 PM
Let me be clear again.
Our refresh job start sometime at 5AM , it puts database into single user mode before refresh. Once it goes to restore step it fails with below error .
Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)
-We tried to kill all session. : Error - Only user processes can be killed.
-We tried to put database offline. : Error - Database is already open and can only have one user at a time.
-We tried to put database in multi user mode. Error - Database is already open and can only have one user at a time.
-We tried database files attach-detach. Error - Database is already open and can only have one user at a time.
and then all the steps mentioned above are useless till system process gets released automatically. We had to restart many times.