Exclusive access could not be obtained because the database is in use.

by ·Comments Off on Exclusive access could not be obtained because the database is in use.

Product:
Cognos Controller 10.4.2
Microsoft SQL 2019 database

Problem:
When from inside Microsoft SQL Management Studio do a restore on top of a existing database (ControllerTest) from a backup of a other database (ControllerProd) you get an error.

Restore of database “ControllerTest” failed.
Additional information:
System.Data.SQLClient.SQLError: Exclusive access could not be obtained because the database is in use.

Solution:
On the Cognos Controller server, stop the IBM Cognos Controller Batch Service. When that is stopped, then normally there should not be any sessions from the Cognos Controller server to the Database server – as long there is no person inside Cognos Controller Client working.

More information:
https://www.ibm.com/support/pages/how-backup-and-restore-db2-databases-use-cognos-controller
https://www.ibm.com/support/pages/how-migrate-upgrade-upsize-existing-database-new-later-version-controller

To check if there are missing SQL logins inside the restored controller database run below command from Query window in Microsoft SQL Management studio for that particular database.
EXEC sp_change_users_login ‘Report’;

If the SQL login for controller is listed, run below command to connect it.
EXEC sp_change_users_login ‘Auto_Fix’, ‘cognos’;

http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users