SQL Judo’s Monthly Challenge – February 2014 – KILL with the DAC

1

January 26, 2015 by Kenneth Fisher

Every month SQL Judo (Russ Thomas) (b/t) challenges us to do his Monthly DBA Challenge. I’ve decided it would be fun (and good practice) to do them. Another major benefit is that it will force me to move out of my normal comfort zone. For example in the last year he has had challenges on Powershell and Heketon, neither of which I’ve worked with yet.

February 2014 – KILL with the DAC

The challenge this month is to use the dedicated admin connection and sqlcmd to log in to a server, identify a blocking session, and kill that session. I’m looking for actual command line sqlcmd – not SSMS in sqlcmd mode.

This one is fairly straightforward. Just to be clear the DAC is the Dedicated Admin Connection and SQLCMD is a command line tool to run T-SQL commands.

  1. Make sure that our instance has the DAC set up correctly.
  2. Since we are using SQLCMD you have to open a command window (DOS if you are 40+).
  3. Open SQLCMD and connect to the DAC.
    sqlcmd -S admin:ServerName -E

    -S ServerName is the server to connect to
    -E uses a trusted connection or you can use -UUserName and -PStrongPassword
    admin:ServerName or -A mean connect to the DAC.

  4. The hardest part of the whole process is to find out what connection you want to kill. Typically you are going to be querying sys.dm_exec_requests where blocking_session_id is not equal to 0 to find blocking sessions. I wrote about this in more detail in my post Transactions: Who, What and Where and my SQL Server Central article Kill SPID (SQL Spackle).

    When running a command in SQLCMD you have to put GO on it’s own line at the end of the batch. So for example:

    SELECT DISTINCT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id !=0;
    GO
  5. Last but not least the easy part. Actually killing the connection. This is just a simple kill command (or several as the case may be).
    Again remember the GO on it’s own line at the end.

    KILL 75;
    KILL 105;
    KILL 126;
    GO
  6. Exit SQLCMD by putting EXIT (again no 😉 on it’s own line.
  7. Use the EXIT command one more time to close the command window.

Remember that you can use the DAC in an SSMS query window by connecting to admin:ServerName\Instance. You can NOT connect to the DAC using the object explorer. Also just in case follow this link to <a href="https://sqlstudies.com/2014/05/28/who-has-the-dac-open/"tell who has the DAC open.

I’m glad this one was relatively easy (ie I’ve studied up on it and done it more than once). Particularly since the next one is March 2014 – PowerShell Something. I’ve never touched PowerShell so wish me luck!

One thought on “SQL Judo’s Monthly Challenge – February 2014 – KILL with the DAC

  1. […] SQL Judo’s Monthly Challenge – February 2014 – KILL with the DAC […]

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: