2

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],
4
  • 1
    The roll-back will likely take as long or longer than the amount of time the query was running. Having said that, was the query an INSERT ... SELECT, or just a SELECT? A simple SELECT on a remote server shouldn't cause any transactions in the local database. 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.
    – Hannah Vernon
    Oct 22 '14 at 13:28
  • Hi @MaxVernon. Thanks for the advice. That's what I thought. Its an INSERT on the MSSQL pulling data from Oracle. How can I figure out if a rollback is taking place, if the ROLLBACK completion stays at 0%? Oct 22 '14 at 13:42
  • What version of SQL Server?
    – Hannah Vernon
    Oct 22 '14 at 13:46
  • @MaxVernon Microsoft SQL Server 2008 (SP3) ENTERPRISE Oct 22 '14 at 13:47
2

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.

2
  • Thank you Max. Turned out I needed to run cports as administrator. After that, I was able to kill the connection. Is it at all possible to see the roll-back activity happening in real time? In other words, what a specific rollback process is actually doing. Oct 22 '14 at 15:10
  • You could potentially use sys.dm_tran_database_transactions to see the status of the rolling-back transaction. I'm uncertain how you could see the specific point-in-time that is being processed.
    – Hannah Vernon
    Oct 22 '14 at 16:34
1

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:

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.