CODEWITHSUNDEEP
sql-serverexceloledb
Carlos Garces
Carlos Garces

Reputation: 833

Locked by wait type OLEDB with SQL server 2012

I have a batch process that generate one linked server over huge Excel files to fetch data into SQL Server 2012

Sometimes the process is locked by a wait type "OLEDB"

I can't find the root ause but my biggest problem is that I can't kill the process that has the wait_type OLEDB

I have try

KILL spid

Not works, never kill the spid.

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

ALTER DATABASE [DataMigration] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

But never finish (due PRINT_ROLLBACK_PROGRESS)

If I look into the process, I see the file that cause the block, but I don't know how solve the issue

SELECT * FROM SYSPROCESSES where spid=90

-There is any way to kill this process without restart the servers? -How can avoid the wait type OLEDB? the same file in the same location usually works fine, the process hangs only some times.

Upvotes: 2

Views: 3794

Answers (1)

Ionic
Ionic

Reputation: 3935

This is quite simple. OLEDB as a wait type often indicates some wait on another Server. This is also the reason why you can't kill the process. OLEDB is often (not always) used as a wait type for connections between SQL Server instances.

You kill the connection on your server, but if the process is running on another instance/linked server it will run there too. It will kill the process after the process on the other instance is finished.

So much to the bad news. The good news for you is, that you easily can find the query which runs on another linked server using this query:

SELECT spid, waitresource
FROM sys.sysprocesses
WHERE spid = <yourKilledSpid>

Just filter it for the spid you try to kill. The waitresource will indicate the remote server including the spid on the remote server. Go to the remote server and kill the spid there too. Your connection will immediately be killed/rolledback. Hopefully this solves your issue.

You can additionally try to take a look at the waiting_tasks. Maybe you'll see something helpful like a blocking resource in there.

SELECT *
FROM sys.dm_os_waiting_tasks

Upvotes: 1

Related Questions