Press "Enter" to skip to content

Fending off Sessions while in Single-User Mode

Published 2023-04-03 by Kevin Feasel

Eitan Blumin just wants to switch the database type:

Today we had an interesting use case where a customer reported that one of the databases they just restored from a backup got stuck in “Single-User” mode in one of their environments.

To resolve it, I first tried running the following command:

ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

In response, I got deadlocked with the dreaded error 1205:

There were a few different attempts with no success until Eitan came up with the final script. Eitan’s analogy was to curling, though the first thing I thought of was Odysseus fighting off his wife’s suitors as he came back to claim his home.

Published in Locks, Blocks, and Deadlocks and T-SQL

One Comment

  1. Eitan Blumin
    Eitan Blumin2023-04-03

    Haha I loved your analogy. I had a few that I thought of until I settled on the one for which it was easiest to find a cover image .

Comments are closed.