Starting a SQL Server Clustered Instance in Single User Mode
July 27, 2012 Leave a comment
One of my DBAs came to me the other day with an issue—he was trying to start an instance in single-user mode in order to do an emergency repair on a database. But he kept getting blocked when trying to use the Dedicated Administrator Connection (DAC). After a bit of research (to be honest, and I’ve worked a lot with clusters, I’ve never had to do this to a cluster), it turns out that the cluster resource, uses the 1 connection available to SQL Server in single user mode, much like the SQL Server Agent can.
So what do we do to get around this issue? Follow these steps.
Remote Desktop into the node of your cluster that currently owns the SQL Server service. You can do this either by connecting the Cluster Network Name, or by checking who the Owner Node is in Failover Cluster Manager.
If you had a –M in your startup parameters, remove it now.
Take the resource name for the cluster offline—note this is not the whole clustered service or role—just the resource. You will still need the disks and IP address that the cluster needs to run. Note—you also want to make sure the SQL Server service and SQL Agent service are stopped, if your dependencies are correct this should happen anyway, but confirms.
Launch a command prompt, and issue the following command: net start MSSQLServer /m
Launch your DAC connection and do whatever work you need to do.
Hopefully, you don’t have to use the DAC that often—it’s usually a pretty bad situation when you do, but hopefully this post will save you some hassles when trying to do it.