UPDATE test_timeout SET age = age + LOCK_TIMEOUT 5000 Note that a typical output of the create stored proc looks like: Note that the DMV query is time aware, you should execute the query as soon as possible after you suffer a timeout/blocking. To find out the exact line which is being blocked, you can open the stored procedure in SMSS, right click the stored procedure, and select script to new query window, from that windows, you can use the line number to locate the SQL statement which is blocked. The most useful part is the line attribute and sqlhandle attribute.īy using the following dmv query, you can get the whole sql text, most likely the stored procedure. Any help or pointers would be much appreciated. I ran into this issue on SQL Server 2008 R2 SP1 + CU6 this afternoon. I am not sure if this is the same bug that was fixed in CU1 for SQL 2008. I am definitely looking at the system_health extended events xml_deadlock_report event for the deadlock graph and this is what I see. I had the same issue on my shiny new extended events deadlock capture automation I have been working on.
Was wondering if you managed to find a solution to this. I've absolutely verified that I'm looking at an encrypted proc. I did get back the statement information as part of the XML deadlock graph from the extended events: I encrypted a proc and made a deadlock occur with it. Note: The SP's are created "WITH ENCRYPTION" option since there are too many lines of code inside SP's. But i want to determine the actual SQL statements that are involved. I can figure the stored procedures names using object ID's.
0 kommentar(er)
