Oracle OLEDB Connections hang in SQL Server: Can I restart Instance?

Question

There is a killed/rolled back query on this instance that was originally attempting to execute a SELECT on a remote Oracle Server via OLEDB. This query was running for over two days before it finally got cancelled.

SPID 69: transaction rollback in progress. 
Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Using cports I have found all open connections to the 1521 port on the remote server. In cports you can close the connection.

After trying to close the connection, the killed/rollback process is still preventing other processes from taking a schema lock on one of the tables.

How can I determine if the database, upon restarting, won't go into RECOVERY PENDING?

Could I kill the processes using Task Manager, without also killing SQL Server?

EDIT

Here is the query anonymized:

   INSERT  INTO sometable 
            ( field1 ,  
              field2 ,  
              field3 ,  
              ...
            )  
            SELECT  some_fields
            FROM    someothertable f ( NOLOCK )  
                    INNER JOIN OPENQUERY([MYORACLE_LINKED_SERVER],

Answer 1

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.

Answer 2

You are most likely hitting this issue: https://connect.microsoft.com/SQLServer/feedback/details/187192/

If this is indeed the case, the ROLLBACK isn't actually doing anything, it is just hung and will only clear by either killing the surrogate host or restarting SQL Server (which will cause any surrogates to be recycled, although that depends on a DCOM timeout and may not actually happen- the DCOM system tries to keep them around for reuse).

It is not clear at this time if this is a SQL Server or Oracle OLEDB Driver issue (although I am more inclined to think the latter because of the sheer amount of reports about Oracle).

If your OLEDB Driver is not allowed InProcess:

it will run in a dllhost surrogate as you show, in which case you can kill it- SQL Server will gracefully rollback any uncommitted transactions and restart the OLEDB Driver when a new query tries to access it. If however it is allowed to run InProcess, the OLEDB Driver DLL gets loaded by the SQL Server executable in its own address space and becomes impossible to kill (without killing the SQL Server which no sane person would do). Running InProcess is risky no less because a bug in the OLEDB Driver can mess up SQL Server causing it to crash or even worse- cause silent data corruption that can go unnoticed for a long time causing much damage to data (I had suffered that). Considering the performance of today's hardware and the efficiency of today OS's inter-process communication, running out of process has negligible performance penalty and provides critical safety.