Deadlock FETCH API_CURSOR0000000000001AEC

Question

Deadlock FETCH API_CURSOR0000000000001AEC

DBA328

Hall of Fame

Points: 3952
More actions
May 4, 2011 at 11:26 pm

#249911

I found a deadlock on my SQL Server. I enabled trace flag 1222 and could find deadlock information from SQL Server error log.
But i only found SQL StStatementFETCH API_CURSOR0000000000001AEC". Based on this information, i couldn't find which application caused this problem. Could anybody advise how to further troubleshooting this problem? Thanks in advance!

Grant Fritchey SSC Guru Points: 398910 More actions · Answer 1

The traceflag puts a lot more information than that into the error log. You should be able to get the database id, the spid involved, the exact resources at the source of the deadlock... But that's only after the deadlock recurs. You won't see anything until you get the deadlock again.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

WayneS SSC Guru Points: 95461 More actions · Answer 2

Can you post the deadlock graph? It will be xml, starting with "<deadlock"...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

DBA328 Hall of Fame Points: 3952 More actions · Answer 3

Yes, i was able to see the database id, the spid involved, the exact resources at the source of the deadlock, but i only found SQL statement "FETCH API_CURSOR0000000000001AEC". Based on this information, i couldn't know which application caused this problem.

And as i know, the trace flag should able to show the SQL Statement involved in the deadlock, but i only see cursor, is it normal?

As i didn't capture profiler trace, so no deadlock graph currently. But i plan to capture one when the issue occur again. But i am not sure whether i could get the applapplciatione from the profiler trace? Can you advise? In addtion, could you advise which event should i capture profiler trace?

Thanks in advance!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

here is a great guide to deadlock troubleshooting (see additional 2 linked posts): http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service