How to Kill/Stop a Long SQL Query Immediately

How to kill/stop a long SQL query immediately?

What could the reason be

A query cancel is immediate, provided that your attention can reach the server and be processed. A query must be in a cancelable state, which is almost always true except if you do certain operations like calling a web service from SQLCLR. If your attention cannot reach the server it's usually due to scheduler overload.

But if your query is part of a transaction that must rollback, then rollback cannot be interrupted. If it takes 10 minutes then it needs 10 minutes and there's nothing you can do about it. Even restarting the server will not help, it will only make startup longer since recovery must finish the rollback.

To answer which specific reason applies to your case, you'll need to investigate yourself.

How to stop a running query?

I've just stumbled upon the odbc package. It allows to interrupt a query at any time.

Basic usage goes like this:

library(DBI)

myconnection <- dbConnect(odbc::odbc(),
                          driver = "SQL Server",
                          server = "my_server_IP_address",
                          database = "my_DB_name",
                          uid = "my_user_id",
                          pwd = "my_password")

dbGetQuery(myconnection, myquery)

I don't have a deep understanding of what happens behind the scenes, but for what I've seen so far in my personal use this package has other advantages over RODBC:

• really faster
• get the column types from the DB instead of guessing them (see here)
• no stringsAsFactors and as.is arguments necessary

How to find current long running queries in SQL Server and how to kill them instantly?

I always use sp_WhoIsActive from Adam Machanic for finding long running queries.
sp_WhoIsActive is described in detail on dba.stackexchange.com.

Although you can also write your own script or use sp_who2 for example.

Update

You are interested in the first 2 columns of the output of sp_WhoIsActive.
The first column defines how long the query is running. The second column is the session_id (or SPID) of the query.

You can use KILL 60 to kill session_id 60 for example.

Have a look over here for a detailed explanation of the stored procedure.

How to kill/Terminate all running process on Sql Server 2008

If you want to force every other connection to disconnect, and you have suitable permissions, you can bounce the database in and out of single user mode:

alter database current set single_user with rollback immediate;
go
alter database current set multi_user;
go

Any other connection to the same database will be terminated.

How to kill a running SELECT statement

As you keep getting pages of results I'm assuming you started the session in SQL*Plus. If so, the easy thing to do is to bash ctrl + break many, many times until it stops.

The more complicated and the more generic way(s) I detail below in order of increasing ferocity / evil. The first one will probably work for you but if it doesn't you can keep moving down the list.

Most of these are not recommended and can have unintended consequences.

1. Oracle level - Kill the process in the database

As per ObiWanKenobi's answer and the ALTER SESSION documentation

alter system kill session 'sid,serial#';

To find the sid, session id, and the serial#, serial number, run the following query - summarised from OracleBase - and find your session:

select s.sid, s.serial#, p.spid, s.username, s.schemaname
     , s.program, s.terminal, s.osuser
  from v$session s
  join v$process p
    on s.paddr = p.addr
 where s.type != 'BACKGROUND'

If you're running a RAC then you need to change this slightly to take into account the multiple instances, inst_id is what identifies them:

select s.inst_id, s.sid, s.serial#, p.spid, s.username
     , s.schemaname, s.program, s.terminal, s.osuser
  from Gv$session s
  join Gv$process p
    on s.paddr = p.addr
   and s.inst_id = p.inst_id
 where s.type != 'BACKGROUND'

This query would also work if you're not running a RAC.

If you're using a tool like PL/SQL Developer then the sessions window will also help you find it.

For a slightly stronger "kill" you can specify the IMMEDIATE keyword, which instructs the database to not wait for the transaction to complete:

alter system kill session 'sid,serial#' immediate;

2. OS level - Issue a SIGTERM

kill pid

This assumes you're using Linux or another *nix variant. A SIGTERM is a terminate signal from the operating system to the specific process asking it to stop running. It tries to let the process terminate gracefully.

Getting this wrong could result in you terminating essential OS processes so be careful when typing.

You can find the pid, process id, by running the following query, which'll also tell you useful information like the terminal the process is running from and the username that's running it so you can ensure you pick the correct one.

select p.*
  from v$process p
  left outer join v$session s
    on p.addr = s.paddr
 where s.sid = ?
   and s.serial# = ?

Once again, if you're running a RAC you need to change this slightly to:

select p.*
  from Gv$process p
  left outer join Gv$session s
    on p.addr = s.paddr
 where s.sid = ?
   and s.serial# = ?

Changing the where clause to where s.status = 'KILLED' will help you find already killed process that are still "running".

3. OS - Issue a SIGKILL

kill -9 pid

Using the same pid you picked up in 2, a SIGKILL is a signal from the operating system to a specific process that causes the process to terminate immediately. Once again be careful when typing.

This should rarely be necessary. If you were doing DML or DDL it will stop any rollback being processed and may make it difficult to recover the database to a consistent state in the event of failure.

All the remaining options will kill all sessions and result in your database - and in the case of 6 and 7 server as well - becoming unavailable. They should only be used if absolutely necessary...

4. Oracle - Shutdown the database

shutdown immediate

This is actually politer than a SIGKILL, though obviously it acts on all processes in the database rather than your specific process. It's always good to be polite to your database.

Shutting down the database should only be done with the consent of your DBA, if you have one. It's nice to tell the people who use the database as well.

It closes the database, terminating all sessions and does a rollback on all uncommitted transactions. It can take a while if you have large uncommitted transactions that need to be rolled back.

5. Oracle - Shutdown the database ( the less nice way )

shutdown abort

This is approximately the same as a SIGKILL, though once again on all processes in the database. It's a signal to the database to stop everything immediately and die - a hard crash. It terminates all sessions and does no rollback; because of this it can mean that the database takes longer to startup again. Despite the incendiary language a shutdown abort isn't pure evil and can normally be used safely.

As before inform people the relevant people first.

6. OS - Reboot the server

reboot

Obviously, this not only stops the database but the server as well so use with caution and with the consent of your sysadmins in addition to the DBAs, developers, clients and users.

7. OS - The last stage

I've had reboot not work... Once you've reached this stage you better hope you're using a VM. We ended up deleting it...

If I stop a long running query, does it rollback?

no, sql server will not roll back the deletes it has already performed if you stop query execution. oracle requires an explicit committal of action queries or the data gets rolled back, but not mssql.

with sql server it will not roll back unless you are specifically running in the context of a transaction and you rollback that transaction, or the connection closes without the transaction having been committed. but i don't see a transaction context in your above query.

you could also try re-structuring your query to make the deletes a little more efficient, but essentially if the specs of your box are not up to snuff then you might be stuck waiting it out.

going forward, you should create a unique index on the table to keep yourself from having to go through this again.

---

Related Topics