July 3, 2014 at 10:10 pm
Hi there,
Wondering if anyone can offer some insight. We have (running SQL 2012 Std) extended events capturing deadlock information and I can see when some ETL processes that run overnight are kicked off a bunch of deadlocks occur...well the ETL continues to run (doesn't fail, and does what it should do), but I see over a thousand xml_deadlock_report entries in the Extended events.
What is weird is that when I go and have a look at the graph - it says "Failed to initialize deadlock control. Key cannot be null. Parameter name: key"
Even stranger is that there are no respective lock_deadlock events
When looking in the lock_deadlock_chain events I can see some events here but they all say:
namelock_deadlock_chain
timestamp36:54.2
componentNULL
stateNULL
dataNULL
idNULL
process_utilizationNULL
system_idleNULL
user_mode_timeNULL
kernel_mode_timeNULL
page_faultsNULL
working_set_deltaNULL
memory_utilizationNULL
call_stackNULL
error_numberNULL
severityNULL
user_definedNULL
categoryNULL
destinationNULL
is_interceptedNULL
messageNULL
callstackNULL
database_id0
session_id0
typeNULL
sourceNULL
os_errorNULL
sni_errorNULL
sni_consumer_errorNULL
sni_providerNULL
local_portNULL
remote_portNULL
tds_input_buffer_errorNULL
tds_output_buffer_errorNULL
tds_input_buffer_bytesNULL
tds_flagsNULL
total_login_time_msNULL
login_task_enqueued_msNULL
network_writes_msNULL
network_reads_msNULL
ssl_processing_msNULL
sspi_processing_msNULL
login_trigger_and_resource_governor_processing_msNULL
connection_idNULL
connection_peer_idNULL
local_hostNULL
remote_hostNULL
error_codeNULL
api_nameNULL
calling_api_nameNULL
resource_typeUNKNOWN_LOCK_RESOURCE
modeNL
owner_type0
transaction_id0
lockspace_workspace_id0
lockspace_sub_id0
lockspace_nest_id0
resource_00
resource_10
resource_20
deadlock_id1269239
object_id0
associated_object_id0
resource_owner_typeEXCHANGE
resource_description
database_name
xml_reportNULL
durationNULL
wait_typeNULL
opcodeNULL
signal_durationNULL
sql_textNULL
node_idNULL
schedulerNULL
workerNULL
yieldsNULL
worker_utilizationNULL
So not really saying anything.
Can anyone assist in pointing me in the right direction for getting some understanding of what might be going on here?
Thanks in advance
Troy
July 7, 2014 at 8:01 pm
Anyone? no ideas? :ermm:
July 9, 2014 at 1:43 pm
Where can I find that tumbleweed emoticon?
October 23, 2015 at 11:57 am
Hi Troy..I am facing the exact issue, almost to the T, that you describe. I wonder since this post is a few months old if you ever found a solution? The strangest thing I can't solve is when I run the sql profiler to capture the deadlock graph, it never detects any deadlocks. However my sql server error log is flooded with them after enabling trace flags 1204 and 1222, and the extended event for deadlocks I have running is capturing them constantly. Over 4k in a 5 hour plan. And as with you, the corresponding job we have running is not failing.
March 15, 2018 at 7:04 am
I've tried to look at some deadlocks in Management Studio but I get the same error that mentions "key cannot be null".
In my case, I've noticed a few interesting things when I open the xdl in a text editor.
* The resource list is full of "exchangeEvent" elements (which has to do with parallelism maybe)
* The "processes" are all the same process, just different threads (spids are the same ecids are different)
* Most importantly, there is no victim here. Our application that issued the query didn't receive an error from the database.
My guess is that the deadlock monitor detects this kind of deadlock cycle, but knows how to resolve it without choosing a victim (???)
I'm also guessing that the control that displays deadlocks assumes that there would be a victim to display and breaks with that error.
It's also interesting that Sentry One's plan explorer has no problem displaying these kind of deadlock.
That's my experience. I'm not sure whether other people encountering "key cannot be null" will have deadlocks that follow this pattern.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy