Have you ever encountered a situation where your SQL Server becomes unresponsive due to a long running or resource hogging query? In such cases, it can be challenging to regain control of the server and resolve the issue. However, there is a handy utility called sqlcmd that can help you in such scenarios.
Before we dive into the details, let’s quickly recap two previous articles that provide the background for this discussion:
- SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor
- SQL SERVER – Find Currently Running Query – T-SQL
Now, let’s explore how sqlcmd can be used to connect to a SQL Server instance and kill a long running query.
First, you need to establish a Diagnostic Connection (DAC) using sqlcmd. The DAC is a special diagnostic connection that allows administrators to access SQL Server when a standard connection is not possible. To connect using sqlcmd and enable DAC, use the following command:
sqlcmd -S localhost -U sa -P dev -d master -A
Once connected, you can run a query to identify the SessionID of the currently running query and its elapsed time. This information will help you pinpoint the query that is causing the issue. Use the following T-SQL query:
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
Based on the results of the above query, you can determine the SessionID of the offending query. To kill the query, use the KILL command followed by the SessionID. For example:
KILL [SessionID]
After executing the KILL command, the long running query will be terminated. However, please exercise caution when using this command, as it can have unintended consequences if not used properly.
It’s important to note that sqlcmd is a powerful utility that can be extremely useful in situations where the SQL Server is unresponsive. I highly recommend bookmarking this article for future reference, as it can come to your rescue when nothing else seems to work.
Remember, it’s always better to ask questions and seek solutions rather than being judged solely on the answers. If you have any further questions or need assistance, feel free to reach out to me.