Starting a SQL Server Clustered Instance in Single User Mode

Spread the love

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.

When you are completed stop the SQL Server service from your command window.

Go back to failover cluster manager and bring your Cluster Resource back online. Then restart SQL Server and SQL Agent. Green is good—if you have any services that don’t restart check the cluster and application logs in Event Viewer.

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.