How to Connect to an Unresponsive SQL Server: Direct Admin Connection
By Robert Balsman
There may be times when SQL Server stops responding to client connections but it is still
up. This can be a frustrating experience, and the first impulse is to bounce SQL Server and/or
reboot the server itself. While this may stop the symptom, this action won't tell you anything
to help you prevent the cause.
For example, let's say that you arrive on Tuesday morning to discover an unresponsive
database. The previous night, your developers added new code to the application servers. Coincidence
or cause? Without a way to see what SQL Server is doing, you'll never know. Enter the Direct
Admin Connection...
Since SQL Server 2005, SQL Server has the Direct Admin Connection (DAC) to allow a back
door for just this sort of scenario. On the server itself (no network connections unless the
remote admins option was previously configured), a single login that is a member of the sysadmin
group can connect to SQL Server via a DAC in order to get an idea of what is happening and
have an opportunity to correct the issue. There are two methods to connect to SQL Server with
a DAC.
DAC Method 1
The first method uses SQLCMD. On the server, open a command prompt and enter:
sqlcmd -S localhost -U sa -P password -d master -A
You may substitute the sa login (the -U parameter, entering the password after -P) with
another login that is a member of the sysadmin group.The parameter -A establishes the DAC,
while the -d master parameter tells SQLCMD to use the master database as the default for this
session. The master database is guaranteed to be available so long as the instance is running,
while other databases may not be available.
DAC Method 2
The second method uses SSMS. Again, on the server, open SSMS, then click 'File' ->
'New' -> 'Database Engine Query'. In the dialog box that appears (titled 'Connect to Database
Engine'), type 'ADMIN:' in front of the name of the server instance. As an example, to login
via a DAC to instance PROD\BOULDER, you would type 'ADMIN:PROD\BOULDER', then enter
the remaining authentication information (remembering to use a login with the sysadmin role),
and click 'Connect'.
Remember, neither of the above will succeed if the login you are using is not a member
of the sysadmin group or if there is another DAC already connected to the instance.
Once connected with either method, you can run queries to determine the cause of the
lock-up. I prefer method 2 above (using SSMS), as I can quickly access my standard set of
queries saved on the server and/or copy and paste them from my local machine to the server
via my RDP session.
Bob Balsman is a Systems & Database Administrator, with over 20 years of IT experience.
His career spans many areas of technical support, including data center management, network
security, system administration, disaster recovery, and database administration. Bob blogs
at [http://www.singingdba.com Sorry, the website www.singingdba.com cannot be found]
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
