Cancelling a Long Running Operation

If you accidentally execute a long-running operation that you want to cancel, and the SSMS client doesn't seem to accept the cancellation, then the database engine is likely involved in a large rollback, to restore the original state.

Run this to identify the sessionid that your execution is using:

SELECT session_id, status, command
FROM sys.dm_exec_requests
WHERE command = 'DELETE';

NOTE: The above query looks for sessions that are performing a delete.
If you're looking for something different, remove the WHERE, and identify your operation.

With the sessionid is known, you can kill the client session.

This will force the database engine to begin a rollback of the operation.
The rollback will run under the same sessionid.

So, do this to kill the session's connection:

KILL <sessionid from above>;

Now. Once you reconnect to the SQL server instance, you can monitor the rollback operation, with this:

SELECT
    session_id,
    command,
    percent_complete,
    estimated_completion_time / 1000 AS seconds_remaining,
    wait_type,
    wait_resource
FROM sys.dm_exec_requests
WHERE session_id = <session id that was killed>;

While the rollback is running, the above query will return something like this:

It might not always indicate any percentage complete.

The Wait_Type column will indicate what the operation is waiting on.

The query will return the rollback entry until it is complete.

Once, it no longer shows an entry for the killed session, the rollback is complete.