Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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:

Error 5070: Database state cannot be changed while other users are using the database ‘database_name’ ALTER DATABASE statement failed. sp_dboption command failed.

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

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

Keywords: kbtshoot, kbexpertiseinter, kbexpertiseadvanced, kberrmsg, kbgreatplainsbp, kbmbspartner, kbmbsmigrate, kbprb, kb

↑ Back to the top

Article Info
Article ID : 923382
Revision : 3
Created on : 3/30/2017
Published on : 3/30/2017
Exists online : False
Views : 91