At this point you may have a couple of questions so let me try to answer (some of) them:

System sessions are those created by SQL itself. They typically (but not always) have session IDs under 50. The best way to tell is that the is_user_process flag in the sys.dm_exec_sessions DMO will be a 0. These sessions include the LOG WRITER, RECOVERY WRITER, TASK MANAGER etc. In my case the problem sessions were all TASK MANAGER sessions.

Why does it matter that they were system sessions? The important thing to remember here is that these sessions can not be KILLed. So because they were holding locks on the database (And somehow even though it was in single user there were multiple sessions with locks in the database. Don’t ask me how.) I wasn’t able to get that exclusive access I needed.

Interestingly when I tried to do the ALTER instead of just hanging I immediately got a deadlock error. I spent a little while trying various things and searching through forums before I went for help on twitter using the #SQLHELP hashtag. After some discussion, it was starting to feel like we were going to have to reboot. Now I don’t know about you but I hate rebooting a server because of a problem like this. It feels like I’m giving up. Finally, though, Robert Davis (b/t) sent me to this link on dba.stackexchange. From what they said, their problem, and quite possibly mine, was caused by the system trying to do an auto statistics update. One of the answers suggested changing the deadlock priority before running a series of ALTERs to set the database OFFLINE, back ONLINE, and then finally back to MULTI_USER mode. Now I decided to simplify things and just changed my deadlock priority then went straight to MULTI_USER. I was able to make the change without any problems and everything moved along smoothly from there.

So what’s can you take out of this? Well, aside from running into my specific problem, which is presumably pretty rare (first time in 20 years for me), it’s an interesting view in resolving a problem. I consider myself reasonably good at T-SQL. And in fact, I did know about deadlock priority ahead of time. But for whatever reason I didn’t put the two together until someone else suggested it. So next time you find yourself stuck on a problem, and you’ve been working on it for a while with no progress, don’t be afraid to reach out for some help.

Update:  I just needed this again and realized that I had to skim through a lot of text to find the link to get the answer.  In the interest of brevity and not to take anything away from the original answerer (please go upvote their answer, it’s proved HIGHLY useful). Here is the solution:

SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [YourDBName] SET MULTI_USER WITH ROLLBACK IMMEDIATE

In addition on my latest run through of this I had to find and kill the one user connection before any of the above would work. I personally don’t like using sp_who so I used this little query to find the connection.

SELECT db_name(resource_database_id) AS DB_Name, request_session_id
FROM sys.dm_tran_locks l
WHERE resource_type = 'DATABASE'
  AND EXISTS (SELECT * FROM sys.dm_exec_sessions s 
				WHERE l.request_session_id = s.session_id
				  AND s.is_user_process = 1)
  and resource_database_id = db_id('[YourDBName]')

Or on the other hand, I’m running an update that absolutely has to happen right now. It’s going to take a bit and I can’t afford the time for it to be started over. A deadlock would be a disaster. What do I do?

That’s where deadlock priority comes into play.

SET DEADLOCK_PRIORITY HIGH; -- This is the same as a priority of 5.
SET DEADLOCK_PRIORITY LOW; -- This is the same as a priority of -5.
SET DEADLOCK_PRIORITY 6;

This setting doesn’t affect anything other than deadlocks. Basically, the higher deadlock priority wins and the lower becomes the deadlock victim. Now everything starts out with a deadlock priority of NORMAL or 0. So, if necessary, with a little planning, you can easily control who the victim will be.

I do want to say, though, that this setting should be used sparingly. If you decide to set every connection to HIGH then you may as well not have bothered. In order for DEADLOCK_PRIORITY to have any effect, there have to be different priorities.

Blocking and Deadlocking are based on lock requests and locking is handled differently depending on the TRANSACTION ISOLATION LEVEL. Because of this the transaction isolation level is going to have a major effect on blocking and deadlocking. The famous (infamous) query hint NOLOCK for example forces a READ UNCOMMITTED transaction isolation level so it doesn’t take or honor certain types of locks, making it much less likely to be blocked (or deadlocked). This does not mean you should use it to avoid blocking! For all of these examples I’m going to assume the default transaction isolation level READ COMMITTED.

Block

Blocking occurs when a process requests a lock on a resource and has to wait because that resource is already being locked by another process.

Creating a block is pretty simple. All you have to do is lock a resource that your process is going to want. Since we aren’t trying to optimize anything here we can just use a query hint to force an exclusive table lock and an explicit transaction to force the lock to be held until we release it.

-- In session 1
BEGIN TRANSACTION

-- Top 1 because I don't want to get much back (or do much work) 
-- and since I'm using WITH (TABLOCKX) I'm going to get an 
-- exclusive table lock anyway. 
SELECT TOP (1) * FROM Table1 WITH (TABLOCKX)

So far all we have is a lock. In order to have blocking we need another process trying to access the same resource.

-- Run in session 2
-- Again a TOP 1 because I don't really care about what I'm 
-- getting back.
SELECT TOP (1) * FROM Table1

At this point session 2 should appear hung and is in fact blocked. We can prove this by looking at the blocking_session_id column of sys.dm_exec_requests.

SELECT blocking_session_id, * FROM sys.dm_exec_requests
WHERE session_id > 50

You can see here that session 53 (session 2 from above) is blocked by session 51 (session 1 from above). You will also notice that session 51 is not on the list from sys.dm_exec_requests. This is simply because the request has ended even though the transaction is still open.

We can take a look at the what’s going on under the hood by doing this:

SELECT request_session_id, request_mode, request_status, 
	-- Unfortunately using the object_name function here  
	-- causes this query to be blocked too
	-- object_name(resource_associated_entity_id, resource_database_id)
	Test2012.sys.objects.name AS object_name
-- Yes I know I'm using NOLOCK but I have to or this query get's 
-- blocked as well.  In this case NOLOCK won't hurt us and it's
-- a reasonable use of it.
FROM sys.dm_tran_locks WITH (NOLOCK)
JOIN Test2012.sys.objects WITH (NOLOCK)
	ON sys.dm_tran_locks.resource_associated_entity_id = 
			Test2012.sys.objects.object_id
WHERE resource_type = 'OBJECT'
  AND resource_database_id = db_id('Test2012')

You can see that one of our queries is actually holding a number of system locks but the important ones for our test are the ones on Table1. Resource_session_id 51 (still session 1 from above) and you will notice that the request_status is GRANT. The request_status for resource_session_id 53 (session 2) is still WAIT and will be until the lock from session_id 51 is released or the request from session_id 53 is canceled.

Deadlock

Deadlocking is similar to blocking, but in this case two (or more) processes are holding locks and each is being blocked by the other. This makes it impossible for the situation to be resolved and SQL Server picks a victim, kills and rolls back that process, and allows the other to move forward.

Since we are using table locks to make this easy we will need more than one table to cause deadlocking. It is possible to deadlock on the same table (and even deadlock with a single session) but that’s more difficult and not important for our simple example here. Leaving the transaction open in the first session, kill the query in the second session then run the following:

USE Test2012
GO

BEGIN TRANSACTION
SELECT TOP 1 * FROM Table2 WITH (TABLOCKX)
GO
SELECT TOP 1 * FROM Table1

We now have a blocking situation again. This time however, we have an additional table involved. As it happens it’s pretty obvious in our example that it’s Table2 but let’s pretend that we don’t know this. In fact this whole example comes from a case where we had a process blocked but needed to deadlock it (we were testing some new error handling). We had no idea what tables were currently being locked beyond the one that I was using to create the block. FYI the process we wanted to deadlock was session 2, the one I was using to deadlock it was session 1.

So I ran the following script again:

SELECT request_session_id, request_mode, request_status, 
	-- Unfortunately the object_name function here get's blocked too
	-- object_name(resource_associated_entity_id, resource_database_id)
	Test2012.sys.objects.name AS object_name
-- Yes I know I'm using NOLOCK but I have to or this query get's 
-- blocked as well.  In this case NOLOCK won't hurt us and it's
-- a reasonable use of it.
FROM sys.dm_tran_locks WITH (NOLOCK)
JOIN Test2012.sys.objects WITH (NOLOCK)
	ON sys.dm_tran_locks.resource_associated_entity_id = 
			Test2012.sys.objects.object_id
WHERE resource_type = 'OBJECT'
  AND resource_database_id = db_id('Test2012')

And lo and behold a lock on Table2 is also being held by session 2 (53). So now to create a deadlock all I need to do is go into session 1 (51) and try to query Table2.

SELECT TOP (1) * FROM Table2

And on session 2 I received the following error:

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

One last important concept here. We want to be certain that session 2 is the one that get’s the error. Typically SQL makes that decision. I believe it’s whatever transaction it feels can be re-run the quickest but I’m not certain. So in order to force the issue we add a simple piece of code to session 1. SET DEADLOCK_PRIORITY

SET DEADLOCK_PRIORITY 10

By setting the deadlock_priority to the maximum (10) it forces any other session with a deadlock priority lower to be the victim. I do want to point out that like many other settings in SQL Server this should not be used to try to out think SQL. I can understand having the thought: “If I set my process to a deadlock_priority of 10 then I don’t have to worry about deadlocks”. I really do. However as soon as you start this type of behavior then you open a gate and other people are going to start doing the same thing. And very quickly you are right back where you started from. This isn’t to say that it shouldn’t ever be used. If you have a process that actually is more important than the average ad-hoc query and you are having a problem with deadlocks this might be a good solution. Of course so would speeding your process up, making shorter transactions etc (all the normal solutions to blocking and deadlocking).