Error message in Business Portal: "Error 5070: Database state cannot be changed while other users are using the database"

Symptoms

You receive an error message when you perform any of the following actions in Microsoft Business Solutions - Business Portal:

• You try to restore a database.

• You try to take a database offline.

• You try to configure a database for single-user access or for read-only access.

You receive the following error message:

This issue occurs even though all users have exited the Microsoft Dynamics GP or Microsoft Dynamics SL.

Cause

Business Portal uses connection pooling to take advantage of performance benefits. This behavior occurs because of the way in which connection pooling functions and because of the design of Business Portal. The Business Portal users maintain their connection to the databases after the users close their browser sessions.

Resolution

To resolve this issue, follow these steps:

1. Display the active connections to the databases, the Microsoft SQL Server 2000 server process ID (SPID), the status, and the logon name. To do this, follow these steps:

   1. Start SQL Query Analyzer.

   2. Run the following script.
      

      sp_who

2. Reset the active connections. To do this, use one of the following methods.
   
   
   
   Method 1
   Manually disconnect the SPIDs for the Business Portal users. This method is the recommended method because it affects the fewest processes and users.
   
   Note Before you follow these steps, have all users exit Business Portal.

   1. Start SQL Query Analyzer.

   2. Run the following script against the database.

      KILL <SPID number>

      Note Replace the <SPID number> placeholder with the SPID number from step 1.

   Method 2
   Restart SQL Services by using SQL Server Management Studio or Microsoft SQL Server Manager:
   
   

   1. Have all users exit Business Portal and any other programs that connect to Microsoft SQL Server.

   2. Restart SQL Services by using Microsoft SQL Server Management Studio or by using SQL Server Enterprise Manager.
      
      When you restart SQL Services, all the SPIDs and all the SPID connections are reset. Method 2 affects more users and more processes than Method 1.

More Information

For more information about the KILL command, click the following article number to view the article in the Microsoft Knowledge Base:

171224 Understanding how the Transact-SQL KILL command works