February 16, 2017 by Kenneth Fisher

I had an interesting problem recently. A database was stuck in single user mode. How exactly was it stuck you ask? Well, 4-5 system sessions were holding locks on the database (and blocking each other). Which meant I wasn’t able to get exclusive use of the database which is required to do an ALTER DATABASE to set it back into MULTI_USER.

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.

5 thoughts on “Deadlock on ALTER DATABASE

  1. dewitte says:

    Thanks for the post. Informative as always. I’ve always been curious about something and I think you’ve hinted to it as a system session. On SQL2000 (remember that?) we had days when the server was slow and days when it seemed optimal. You just take it for granted you’re having a slow day. In face, when I used to work on an IBM 9121 mainframe, you’d have days when it was just slower than normal, event though you are the only user. While I’ve put up with this over the years, I’m starting to wonder if just maybe SQL is updating statistics or something on those slow days. Is there any way to tell (1) if SQL 2000 is currently running statistics updates, and (2) to tell it to stop or force it to run them during a certain timeframe?

    • Oh wow, I’m sorry, I just don’t remember 2000 well enough to say. I could tell you yes to both if it was 2005+ (Some DMVs & turn off auto statistics updates on all tables and then run them manually) but for 2000 I just don’t know. Sorry.

  2. zoltan says:

    I had the same problem, but the other session was a normal user session, who had VIEW SERVER STATE rights. He was just connected to the server with SSMS, and listed the Databases.
    It seems when someone has this permission, he can perform some queries, that put a SHARED Database Lock on a Database, that is in single_user mode!
    It is quite unpleasant during an Application Release deployment…

    In deadlock report I saw the following:
    (@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000))
    create table #tmp_db_hadr_dbrs (group_database_id uniqueidentifier, synchronization_state tinyint, is_local bit)
    declare @HasViewPermission int
    select @HasViewPermission = HAS_PERMS_BY_NAME(null, null, ‘VIEW SERVER STATE’)
    if (@HasViewPermission = 1)
    insert into #tmp_db_hadr_dbrs select group_database_id, synchronization_state, is_local from master.sys.dm_hadr_database_replica_states
    SELECT AS [Database_Name],
    ‘Server[@Name=’ + quotename(CAST(
    AS sysname),””) + ‘]’ + ‘/Database[@Name=’ + quotename(,””) + ‘]’ AS [Database_Urn],
    dtb.containment AS [Database_ContainmentType],
    dtb.recovery_model AS [Database_RecoveryModel],
    ISNULL(suser_sname(dtb.owner_sid),”) AS [Database_Owner],
    when dtb.collation_name is null then 0x200
    else 0
    end |

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: