SQL Server Transactions – Ways an Open Transaction Will Rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

The roll-back will likely take as long or longer than the amount of time the query was running.

Almost certainly, if you restart the instance, whatever database is affected by the ongoing transaction will need to run recovery, which will take as long or longer than the rollback that is currently happening.

Advice? Don't kill SQL Server.

Also, killing the dllhost.exe process won't stop the rollback, since that process is no longer required by SQL Server to complete the rollback.

You can see if activity is happening against the database in question by checking the following:

SELECT [Database] = d.name
    , FileName = mf.name
    , num_of_reads
    , [io_stall_read_ms]
    , AvgStallPerRead = CASE WHEN num_of_reads > 0 THEN io_stall_read_ms / num_of_reads ELSE 0 END
    , num_of_writes
    , [io_stall_write_ms]
    , AvgStallPerWrite = CASE WHEN num_of_writes > 0 THEN io_stall_write_ms / num_of_writes ELSE 0 END
    , [io_stall] 
    , sample_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) iovfs
    INNER JOIN master.sys.master_files mf WITH (NOLOCK) ON iovfs.database_id = mf.database_id AND iovfs.file_id = mf.file_id
    INNER JOIN master.sys.databases d WITH (NOLOCK) ON iovfs.database_id = d.database_id
WHERE d.name = 'database_name_in_question';

Running this several times in a row should allow you to see num_of_writes increasing.

This won't help you know the full progress of the rollback, but will allow you to see if there is disk activity against the database.

You could check the following query to see activity against the database in question:

SELECT login_time
    , host_name
    , program_name
    , start_time
    , r.status
    , command
    , wait_type
    , last_wait_type
    , wait_time
    , wait_resource
    , percent_complete
    , estimated_completion_time
FROM sys.dm_exec_sessions S 
    INNER JOIN sys.dm_exec_requests R ON S.session_id = r.session_id
WHERE r.database_id = DB_ID('database_name_in_question');

Look for the session where the rollback is occurring by looking for "ROLLBACK" (or something similar) in the command column. Unfortunately, the percent_complete and estimated_completion_time columns won't be useful for looking at rollbacks.

If you can determine the SPID for the rollback activity, you can use the following command to check progress on the rollback (new to me!)

KILL xx WITH STATUSONLY;

Where xx is the SPID.

See http://msdn.microsoft.com/en-us/library/ms173730.aspx for further details about that!

sys.dm_tran_database_transactions has very detailed information about ongoing transactional activity, including roll-backs.