How to Resolve Dynamics 365 Database Single-User Mode Issue and Switch to Multi-User Mode

When attempting to restore an AX (Dynamics 365) database, you may encounter an issue where the database is automatically set to Single-User Mode. In this mode, you won't be able to perform any tasks or operations against the database. If you find yourself in this situation, follow the steps below to resolve it and switch the database back to Multi-User Mode.

Step 1: Set Database to Multi-User Mode

To change the database from Single-User Mode to Multi-User Mode, run the following SQL query:

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;        

This command should immediately release any exclusive locks and set the database to Multi-User Mode. However, if there are any active deadlocks or processes blocking the change, you might encounter an error.

Step 2: Identify and Kill Blocking Processes (If Applicable)

If you receive an error indicating that there are deadlocks or blocking processes, you will need to identify and terminate those processes. Follow these steps to resolve the issue:

1. Find the Database ID: First, run the following query to get the database ID for your AX database:

          SELECT dbid FROM sysdatabases WHERE [name] = 'AxDB';        

2. Identify the Blocking Processes: Use the database ID obtained in the previous step to find all processes associated with the AX database:

select spID from sysprocesses where dbid = 5        

3. Kill the Blocking Processes: Once you identify the spid (process ID) of the blocking sessions, terminate them with the KILL command. For example:

KILL <spid>;        

Step 3: Reattempt to Set the Database to Multi-User Mode

After terminating the blocking processes, execute the following query again to switch the database to Multi-User Mode:

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;        

(OR)*

Alternative Method: Find and Kill Locking Processes

Alternatively, you can identify locking processes using the following query, which will show all active locks on the AX database:

SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('AxDB');        

SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('AxDB');        

Once you identify the sessions causing the locks, use the KILL command to terminate them. For example:

KILL <session_id>;        

After killing the necessary processes, re-run the ALTER DATABASE query to switch back to Multi-User Mode:

ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;        

Conclusion

By following these steps, you should be able to resolve any issues related to Single-User Mode and restore your AX database to Multi-User Mode. Always make sure to check for blocking processes and deadlocks that might interfere with the process.

Feel free to share your experiences or questions about troubleshooting AX/D365FO database issues in the comments below!