db stuck in single-user mode with sa account holding deadlock (on cluster)

Question

db stuck in single-user mode with sa account holding deadlock (on cluster)

sarahb3

SSC Enthusiast

Points: 113
More actions
August 29, 2019 at 3:16 pm

#3675457

I'm in a pickle and I've googled for a solution until my eyes are crossed.
I have SQL Server 2014 running in Windows Authentication mode on a 2-node cluster. The SQL instance is for one of our (non-prod, thankfully) Sharepoint environments. Yesterday I went to restore a database via a script. The first step in the script was to set the db to single-user. The restore failed and I started getting deadlock errors. The db is stuck in single-user mode and the sa account is holding the locks. I can't kill a process that is owned by sa. The instance is on a cluster so I can't stop the SQL Server engine via Config Manager. I tried taking the whole service offline via Failover Cluster Manager but when I bring the service back online the locks are still there. I'm thinking of going nuclear: take the service offline via FCM, then deleting the physical database files, then bringing the service back online.
Does anyone have anything else in their bag of tricks I can try before the nuclear option? I'm really tearing my hair out here!
Thanks in advance!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125521 More actions · Answer 1

Whatever service (I'm guessing something like monitoring, backup, or replication) is holding locks on the database, perhaps you can temporarily stop that service.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

sarahb3 SSC Enthusiast Points: 113 More actions · Answer 2

Thanks Eric. The cmd is "TASK MANAGER" - i don't know how to figure out what that means, specifically, except I'm guessing it's part of the db restore that failed.

Do you see any problem with just deleting the underlying physical files?

Thanks again!

Eric M Russell SSC Guru Points: 125521 More actions · Answer 3

sarahb3 wrote:

Thanks Eric. The cmd is "TASK MANAGER" - i don't know how to figure out what that means, specifically, except I'm guessing it's part of the db restore that failed.
Do you see any problem with just deleting the underlying physical files?
Thanks again!

Yes, delete the files if you then want to restore from backup. But before doing that, see if the following solution (seting AUTO_UPDATE_STATISTICS_ASYNC = OFF) will help unlock your database.

https://dba.stackexchange.com/questions/20714/task-manager-process-is-taking-over-a-single-user-mode-database-what-is-it

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

sarahb3 SSC Enthusiast Points: 113 More actions · Answer 4

I had to get moving with this situation so I've already deleted the files and am doing the restore. I'll keep that suggestion in my back pocket if I ever run across this situation again. Thanks again for your help.

Steve T SSCommitted Points: 1994 More actions · Answer 5

This is not to fix the initial issue, but just tip in case you didn't already do this. Run the restore statement immediately after the ALTER statement in the same query batch.

ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE <DatabaseName> SET MULTI_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE <blah...blah...>

I do this to disconnect all connections and then disconnect again when setting back to multi_user. If the backup never actually starts and fails for whatever reason, the database is still in a accessible state.

If the backup actually did start, the database would be in restoring mode and notable can access it regardless.