Starting a SQL Server Clustered Instance in Single User Mode

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.

About jdanton1
A DBA, cyclist, cook (who likes to play chef occasionally)

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

Join 1,894 other followers

%d bloggers like this: