2

Here is a weird situation we experienced with a SQL Server 2008 Database Mirroring Failover.

We have a pair of mirrored databases running in high-availability mode and both the principal and mirror showed as synchronized. As part of some maintenance I triggered a manual failover of the principal to the mirror. However after the failover the principal was now in single-user mode instead of the expected "Principal/Synchronized" state we usually get. The database had been in multi-user mode on the previous principal before this had happened. We ended up stopping all applications, restarting the SQL Server instances, and executing "ALTER DATABASE ... SET MULTI_USER" to bring the database back to the expected "Principal/Synchronized" state in a multi-user mode.

Question.

Does anyone know where SQL Server stores information about whether a database should be in single-user mode or not? I'm wondering if there is some system database or table that has this setting recorded somewhere. In particular we had an incident once with the database on the original principal (the one I was failing over to) where when trying to detach the database it was put into single-user mode. I'm wondering if that setting is cached somewhere and is the reason that SQL Server put it back into single-user mode after a failover.

3
  • You're probably better off asking this on ServerFault.com where all the sysadmins and DBAs hang out....
    – marc_s
    May 15, 2010 at 7:21
  • The status can be determined by examining the user_access column in the sys.databases catalog view or the UserAccess property of the DATABASEPROPERTYEX function. SELECT DATABASEPROPERTYEX('AdventureWorks2008R2', 'useraccess');
    – DaniSQL
    Aug 4, 2010 at 13:48
  • What maintenance, were you applying a SQL service pack? If so it may have been running the upgrade scripts after the failover, how long did you wait before putting it in multi user mode? Also, what method are you using to fail it over, using the GUI or ALTER DATABASE...SET PARTNER FAILOVER? Peter is right about the view, and since it's instance specific it could be set to simgle user on one server and multi on the other. Oct 29, 2010 at 14:25

1 Answer 1

0

The database view for whether a database is in single or multi user, for SQL Server 2005 or later, is sys.databases - it's the user_access_desc column.

You must log in to answer this question.