SQL Server – Restarting Instance for Oracle OLEDB Connections Hang

oledbrollbacksql serversql-server-2008

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.

enter image description here

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.

enter image description here

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.

enter image description here

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?

enter image description here

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],

Best Answer

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.

Related Question