Wednesday, May 25, 2011

Cannot access database in single user mode

SQL Server database can be changed to single user mode by the below command.

ALTER DATABASE SET SINGLE_USER
GO

Keep the session from where you ran the ALTER DATABASE alive and open a new session.
Try to use the database from the new session and it throws the below error.

Error 924 - The database is already open and can only have one user at a time.

When you run the below query to find out any session using this database context it will return zero results.

SELECT spid, DB_NAME(dbid) FROM sys.sysprocesses WHERE DB_NAME = 'mydb'
GO

But when you run the below query you will see the locks on the database from the session which altered the database to single user mode.

SELECT * FROM sys.dm_tran_locks WHERE DB_ID('mydb') = resource_database_id
GO

So you will be able to use the database in single user mode from the session where you ran the ALTER statement or close that session and then try to use it from other session.

This is by design of SQL Server. The whole intention of holding locks on the database after altering the database to single user mode even though the database is not associated with any session is very simple. The session which changed the database to single user mode should have the capability to change it back to multi user. No other session should change back the database to multi user while this session is doing some operations on the database in single user mode.

So now you know, use a single session while you are working with single user database. If the session is orphaned the locks remain and you will never be able to get back the database to multi-user. The only solution then is to stop the services, delete the log file and start the services. Drop the database and restore from the most recent backup available which is not at all recommended as a DBA. Prevention is better than data loss and downtime.

Happy Troubleshooting,
Manu

5 comments:

  1. Hi Manu,

    Can you breif on orphaned connections, i =have read ....http://support.microsoft.com/kb/137983
    but i am not able to get clear idea....can u please explain on this....

    ReplyDelete
  2. This post excellently highlights what the author is trying to communicate. Nonetheless, the article has been framed excellently well and all credits to the author. For more information on how to load balance your web servers, please visit ..
    http://serverloadbalancing.biz/wordpressbiz/,
    http://serverloadbalancing.info/wordpressinfo/

    ReplyDelete
  3. Hi Cherry,

    The KB article you have provided is an excellent description to understand what is an orphaned session/connection. Without going deep into how the orphaned sessions are created, I can give you a brief idea of what exactly is an orphaned session.

    Imagine you opened the management studio and opened a new query and connected to an instance of SQL Server. The session is the root for all the operations that you are performing in that session. In our case changing the database into single user mode. So the sessions hold locks on several resources. For some reason say the management studio crashed and the session was not closed properly, which means the connection between the locks the session is holding and the session itself is broken. The locks are left on the resources and we cannot release them. Which means the session still has all the locks to the resources but the session itself is not available to manage them.

    I hope this explains what is an orphaned session and your query. Please let me know if you still have any questions.

    -Manu

    ReplyDelete
  4. ------------------------------
    (In English)

    Hi Manu,

    I wonder if you could help me with a problem in SQL Server 2008. I am using an application that works with two databases, recently i made ​​a password change of "sa" login, and I have since had the following problem, which I'm still unable to solve:

    In the event viewer of SO, in event of applications, shows me every minute of the following:

    Login failed for user 'AAAAA_user'. Reason: Failed to open the Explicitly specified database. [CLIENT: 192.168 ....]

    My questions are:

    1) Is there any char that the key problems in SA?.
    2) What are the steps to change the sa password, (I did it only from the object browser (tree) -> (local) ---> security ---> logins ---> sa - -> properties.
    3) What passwors must be stored in the next logins, eg (MS_PolicyEventProcessingLogin # # # # / # # # # MS_PolicyTsqlExecutionLogin / Pepito \ Administrator / NT AUTHORITY \ SYSTEM / NT SERVICE \ MSSQLSERVER / NT SERVICE \ SQLSERVERAGENT)
    4) That I can do to solve it?

    I hope you can answer since I have many problems with this!

    Thanks!!

    --------------------------
    (In Spanish)

    Hola Manu,

    Quisiera saber si me podrías ayudar con un problema de SQL Server 2008, ya que utilizo una aplicación que a su vez funciona linkeada a dos bases de datos, hace poco hice un cambio de clave del "sa", y creo que desde entonces tuve el siguiente inconveniente, que aun sigo sin poder solucionar:

    En los eventos de las aplicaciones del Event Viewer me muestra cada un minuto lo siguiente:

    Login failed for user 'AAAAA_user'. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168....]

    Mis preguntas son:

    1) Hay algun caracter que de problemas en la clave del SA?.
    2) Cuales son los pasos para cambiar de clave del sa?, (ya que solo lo hice desde el explorador de objetos (arbol) --> (local) ---> security ---> logins ---> sa ---> properties.
    3) que claves llevan en las propiedades los otros logins, ej: (##MS_PolicyEventProcessingLogin## / ##MS_PolicyTsqlExecutionLogin## / Pepito\Administrador / NT AUTHORITY\SYSTEM / NT SERVICE\MSSQLSERVER / NT SERVICE\SQLSERVERAGENT)
    4) Que puedo hacer para resolverlo?

    Ojalá puedas responderme ya que tengo muchos problemas con esto!

    Graciass!!!!!!!

    ReplyDelete
  5. Hi,
    The error clearly says that the login AAAAA_user does not have permissions on the database you are trying to connect using it. I did not get the relation between sa login and the AAAAA_user login. Ideally you should not be using sa login in applications as it has sysadmin previliges by default. And for password changes when you change it in SQL server you need to update your application connection string with the updated password.

    You can mail me if you have further questions to manu0417@gmail.com

    ReplyDelete