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 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!