FETCH API_CURSOR0000000000005BE4 Blocking Several Transactions

One fine morning, users started complaining my SQL server instance performance…They sent me a screenshot which indirectly says, their queries have timed out. I quickly logged in and checked if there are any blocks.

SELECT *

FROM   sysprocesses

WHERE  blocked <> 0 

There were several blocks caused by a naughty spid 80.

When I checked DBCC INPUTBUFFER(80), To my surprise, I got below scary result.

EventType | Parameters | EventInfo
Language Event | 0 | FETCH API_CURSOR0000000000005BE4

I started googling and came across a blog http://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/

Below query in that forum helped me identify the process and query causing the blocks. It was a select query.
SELECT c.session_id,

       c.properties,

       c.creation_time,

       c.is_open,

       t.text

FROM   sys.Dm_exec_cursors (80) c

       CROSS apply sys.Dm_exec_sql_text (c.sql_handle) t 

The value 80 was my blocking process spid here. You will have to replace that with your blocking spid. Share the output with the application developers to help them prevent this from happening in future.

session_id – properties – creation_time – is_open text
80 – API | Dynamic | Scroll Locks | Global (0) – 2013-06-18 20:34:19.357 – 1 – (@1 int)SELECT * FROM [xxxxxxx] WHERE [aid]=@1

Author: Sherbaz

Being a gadget enthusiast, He always felt good to try new programming languages and techniques. But never goes in-depth anywhere. www.SplitExpense.in was found when he tried php, html, javascript and mysql. Being an electronics engineer, he is also interested to build small gadgets and tools in embedded and digital electronics. As a profession, he handles microsoft sql server database and calls himself a database administrator

Leave a Reply