Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 16 '10 at 0:53

This question came from our site for professional and enthusiast programmers.

    
You're probably better off asking this on ServerFault.com where all the sysadmins and DBAs hang out.... –  marc_s May 15 '10 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 '10 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. –  Jason Cumberland Oct 29 '10 at 14:25
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.