SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query

People are judged from their questions and not their answers. I received wonderful question the other day.

How sqlcmd can be used along with currently running query script posted on your blog?

Please read following two posts before continuing this article as they cover background of this article.

SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

SQL SERVER – Find Currently Running Query – T-SQL

If due to a long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to the server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is not possible. Except very extreme situations dedicated administrator connection (DAC) is always successful.

Let us see how we can use DAC using sqlcmd.

Solarwinds

Connect SQL Server using sqlcmd and -A option which will establish DAC.

C:\>sqlcmd -S localhost -U sa -P dev -d master -A

SQL SERVER - sqlcmd - Using a Dedicated Administrator Connection to Kill Currently Running Query sqlcmd_1

For our test purpose run following query which overload server and probably make it unresponsive. If your configure is not strong I strongly suggest not to run following query.

Following query is just for test purpose and not part of solution.

USE AdventureWorks
GO
SELECT *
FROM Person.Address
CROSS JOIN Person.Address a1
CROSS JOIN Person.Address a2
CROSS JOIN Person.Address a3
CROSS JOIN Person.Address a4
CROSS JOIN Person.Address a5
CROSS JOIN Person.Address a6
CROSS JOIN Person.Address a7
CROSS JOIN Person.Address a8
CROSS JOIN Person.Address a9
GO

SQL SERVER - sqlcmd - Using a Dedicated Administrator Connection to Kill Currently Running Query sqlcmd_3

Once successfully connected it will provide prompt 1> enter following T-SQL query which will give SessionID of currently running query along with its elapsed time.

SELECT
req.session_id,
req.status,
req.total_elapsed_time
FROM sys.dm_exec_requests req
WHERE status = 'running'
AND req.total_elapsed_time > 1

SQL SERVER - sqlcmd - Using a Dedicated Administrator Connection to Kill Currently Running Query sqlcmd_2

Our previously running query gave session id 52 in on my server. The session id may be different for each SQL Server. Once the session id is figured out it can be killed using KILL [SessionID] command. Always make sure to type command GO after each complete query.

KILL 52

SQL SERVER - sqlcmd - Using a Dedicated Administrator Connection to Kill Currently Running Query sqlcmd_4

Once above query has run it will kill our example long running query and give following error.

Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

SQL SERVER - sqlcmd - Using a Dedicated Administrator Connection to Kill Currently Running Query sqlcmd_5

sqlcmd is really useful utility of SQL Server and it comes in handy when server is not responsive. I strongly suggest to bookmark this article as it can come to rescue when nothing works and SQL Server is unresponsive.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQLAuthority News – Author Visit – Mumbai, India – From January 8, 2008 to January 11, 2008
Next Post
SQLAuthority News – Security White Papers

Related Posts

5 Comments. Leave new

  • Timur Alhimenkov
    January 27, 2009 11:04 pm

    Great! Thank you very much!
    I always wanted to write in my site something like that. Can I take part of your post to my site?
    Of course, I will add backlink?

    Regards, Reader

    Reply
  • How are you, I located this website by mistake when I was looking on Google then I arrived to your web site. I must tell you that your web site is cool I like your theme! I don’t possess a lot of free time at the current moment to read your website web sitebut I bookmarked it. I will be back in a day or two. Thanks for a great site.

    Reply
  • Hey Pinal,

    First of all congratulations on creating, maintaining a wonderful blog.

    I have a small question whose solution i couldn’t find anywhere on the web….i have been googling now for almost 2 days :( .

    Our application has a process which takes a lot of time. It basically calls an SP and that SP calls many other smaller SP’s. Many a times the completion of this process can take upto 1 hour.

    What i want to do is provide users with a Cancel button so that they can cancel the execution of the process, in case they plan to do it later. This should be allowed since i have used Transactions at application and even the procedural levels.

    So my question is, lets say main procedure name is “MyMainProc”. All i want to do is cancel the execution of this procedure in some way. KILL was an option but it closes the entire SQL collection and hangs up my application since the connection is broken coz of the KILL statement execution.

    How can i just kill a procedure execution rather than killing the session????

    Any help will be appreciated

    Thanks and Regards,

    Suhas Pandit S.

    Reply
  • Hi, With the first procedure I get the below and I have configured sql to allow remote connection.

    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF]. .
    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

    Reply
    • Are you able to telnet to port on which SQL is listening?
      Try hardcoding the port if SQLbrowser is not reachable.

      Reply

Leave a Reply

Menu