SQL locked in single user mode now

Question

A pair of databases this morning made an error while working in single user mode. Due to the following error, I can not do anything :(

Msg 1205, Level 13, State 68, Line 1 Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 

I get this error when I try to do the following (using the main database as a Sys administrator):

 ALTER DATABASE dbname SET MULTI_USER; GO 

For the sake of this, I tried to restart SQL Server, I tried to kill any processes, and I even tried to reset a single user:

 ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO 

The launched task was designed to copy the database and immediately transfer it to single-user mode in order to try to make it faster.

Anyway, can I remove the locks?

Answer 1

Well, I will answer my own.

I had to use the following:

 sp_who 

in which information about the currently connected users and sessions was displayed, I then remembered the Activity Monitor, which shows the same material ... In any case, this led me from my desk to someone who supported database connections against mine ...

In any case, when I shut down the computer (turning it off ... deserved it), I could run SQL to change it to MULTI_USER mode (using the system administrator user):

 USE Master GO ALTER DATABASE dbname SET MULTI_USER; GO 

FYI for those concerned, this can be used to immediately set the database to SINGLE_USER :

 ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO 

For more information, if you know the process ID, you can use kill pid :

 kill 62 

At the same time, SSMS creates a process for your user, in my case it was rejected due to another.

EDIT: Like Bobby's recommendations, we can use:

 sp_Who2 

This may show us which process is blocked by another process.

Answer 2

There was the same problem. This worked for me:

 set deadlock_priority high; -- could also try "10" instead of "high" (5) alter database dbname set multi_user; -- can also add "with rollback immediate" 

From ideas / explanations:

http: //myadventuresincoding.wordpress.com/2014/03/06 ...

http: //www.sqlservercentral.com/blogs/pearlknows/2014/04/07 / ...