BUG: SEM Fails to Reset DB Option to Single User If >1 User

• Microsoft SQL Server, version 6.0

SYMPTOMS

When you use SQL Enterprise Manager (SEM) to change the database option to single user, if more than one user is using the database, the CHECKPOINT command will fail and the following error is shown by SEM:

   Error 3508: Attempt to set 'databasename' to single user mode failed
   because the usage count is 3.  Make sure that no other users are
   currently using this database and rerun CHECKPOINT.

WORKAROUND

To reset the 'single user' database option, you must use the system stored procedure sp_dboption.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

This problem does not occur in SQL Server version 6.5.

MORE INFORMATION

If you clear the single user option and click OK again, the same message is shown. In order to exit the "Edit Database" window, you must click the Cancel button. If you "edit" the tempdb database again, and look at the database option, the single user option is still checked. You are unable to clear the single user option and click OK to reset the change. The above error is shown again until you click Cancel to exit.

SEM was able to change the database option to single user, however, in order to implement the database option changed, a CHECKPOINT command must be executed in the database affected. If the database has more than one user using it at the time, then the CHECKPOINT command would fail. The problem is that Enterprise Manager would not allow you to reset the single user database option after this.