Exclusive Lock on the database in SQL Server when attempting to login to IBM i2 iBase
This document applies only to the following language version(s):
When trying to log into IBM i2 iBase or iBase Designer, users may experience an error which reports that there is an 'Exclusive Lock' on their database.
Can occur when IBM i2 iBase or iBase Designer is improperly shutdown.
Resolving the problem
Ensure that you have a backup copy of your SQL database(s) before attempting the steps below.
To resolve the issue, follow these steps:
- Login to Microsoft SQL Server Management Studio
- Expand the Database folder
- Expand Tables folder
- Look for the entries 'dbo._Configuration_Def', 'dbo._Configuration_Text' and 'dbo._Configuration_Binary'
- Open the table 'dbo._Configuration_Def' and look for the entry SQLServer:Exclusive" and check the value against it. If the value is 1 then it means the same value also exist in the 'dbo._Configuration_Binary' table. However, if the value is 0 then it means the same value also exist in the 'dbo._Configuration_Text' table.
- Delete the entry (row) from table 'dbo._Configuration_Text' or 'dbo._Configuration_Binary'
- Then delete the value from 'dbo._Configuration_Def' table
This should remove the 'Exclusive Lock' from the database.
The "select * from ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command is not something iBase issues explicitly. This is a consequence of iBase not being able to put the database into single user mode. The "ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command, which iBase is issuing explicitly, is failing.
This command fails when there is an active connection to the iBase database.
Re-start SQL Server and then open SQL Server Management Studio and open a new query window (while connected to the master database, not the iBase database) then run this command:
This will display details of all the active database connections. In theory there will be a connection to their iBase database... the one that is causing the 'SET SINGLE USER' command to fail. Kill this connection by executing the following command:
where <SPID> is the SPID in the results table for the row which shows a connection to the iBase database.
Note: the ProgramName column will indicate what program/process is connected to the iBase database. This is the process that is causing the problem.
If there are no connections to the iBase database then try opening iBase in Designer. If it fails then try the above again.
If you are still unable to open the database in iBase Designer then run the "ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command in a query window (while connected to the master database, not the iBase database.) Replace <dbname> with the name of the iBase database.) Run this command "ALTER DATABASE <dbname> SET MULTI_USER WITH NO_WAIT" and then try opening the database in iBase Designer.
Furthermore, execute the following command to view what triggers are set to in SQL Server that may be connecting to the iBase database:
SELECT * FROM sys.server_triggers;