unable to kill SQL server job, status stays killed/rollback

Question

I am unable to kill some SQL Server agent jobs. The task state continues to be running and the command stays in KILLED/ROLLBACK. The job executes queries against OSI's PI system via OLEDB linked server and Oracle. The only way I have found so far to kill these jobs is by restarting SQL server (not a preferred method).

Answer 1

I found following article https://connect.microsoft.com/SQLServer/feedback/details/187192/openquery-to-linked-server-hangs-leaving-spid-with-open-tran-that-cannot-be-killed-then-templog-ldf-grows-without-limit-requires-sql-server-restart-on-production-servers

Apparently several people have this issue using openquery through a linked server that is not SQL Server. I'm reposting the work-around that BReuter posted on above article:

posted by BReuter on 1/30/2007 at 2:21 PM *I have experianced the exact behavior and have found a combination of software which stablized our environment.

There were three key ingredients I found:

1) Make sure you do not have ANY linked servers using Microsoft OLEDB Provider for Oracle, instead use Oracle Provider for Oracle(version 9.2.0.4 is what I have in production).

2) Do not allow the linked server to run "in process". This took some research, but it is possible to run the linked server out of the SQL memory space by following the directions below.

3) I'm running SQL 2005 SP1 on W2K3, but I believe the OLEDB Provider is the key and not the OS or DB version. The default security settings are too tight to run the Oracle OLEDB provider (OraOLEDB) out-of-process. Further, the default settings for MS DTC do not allow network communication.

1. Control Panel-> Administrative Tools-> Component Services

2. Drill to Component Services-> Computers

   a. Right-click My Computer-> Properties

3. MSDTC tab -> Security Configuration button (screenshot below)

   a. Network DTC Access – checked.

   b. Allow Inbound / Outbound – checked.

   c. No Authentication Required – This simulates the windows 2000 security settings.

   d. Enable XA transactions – the type of transaction implemented by OraOLEDB provider.

4. Drill to Component Services-> Computers-> My Computer-> DCOM Config

   a. Right-click MSDAINITALIZE-> Properties

5. Security tab (screenshot below)

   a. Access Permissions -> Customize.

   b. Press “Access Permissions” Edit button.

   c. Give the SQL Server Service account “Local Access” permission.

   d. Repeat for “Launch and Activation”.*