Fix to change Dynamics 365 – database to multi user mode

Sometimes when you try to restore the AX database, it will be converted into a Single User mode. And it will now allow you to perform any task against the AX database.

b1

To rectify that, please try to execute the below query.

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

If you have any deadlock on the database, you will receive the below error.

2

To rectify this issue, we need to kill the dependent process on the database. To identify the process please use the below sql script.

  1. Get the database id

(select dbid from sysdatabases where [name] =’AxDB’)

3

  1. Find all the process on the database id

select spID from sysprocesses where dbid IN 5

Once, you find the spID of the process, you should kill the process by using below code

Kill <spID>

Now execute alter table to change the database to multi user mode using below query.

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

Alternatively, you can use the below script to find lock processes

4

SELECT * FROM   sys.dm_tran_locks WHERE  resource_database_id = DB_ID(‘AxDB’)

And kill the process with the request_session_id as shown below code

kill 53

kill 51

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s