Deadlock info from XEvents XML

Here's a handy script I developed to take advantage of the built-in XEvents System Health session in SQL 2008.

XEvents is the new infrastructure for diagnostics data collection, set to usurp SQL Traces in utility for this purpose.  XEvents are already up and running in SQL 2k8 and 2k8R2 but without an official GUI.  The Denali RC's currently sport an XEvents interface in Management Studio, which will officially get the ball rolling.

Right now though, you can still use XEvents, and the pre-packaged System Health trace session that is running by default.

Here's a great way to get some fairly-easy to read (for XML) diagnostic info on deadlocks that happened on your server... without enabling a Trace flag or a SQL Trace.

declare @xml xml

select @xml = CAST(t.target_data as xml)

from sys.dm_xe_session_targets t

inner join sys.dm_xe_sessions s

     on s.address = t.event_session_address

inner join sys.server_event_sessions es

     on s.name = es.name

     where s.name = 'system_health'

     and t.target_name = 'ring_buffer'

select @xml.query('/RingBufferTarget/event [@name="xml_deadlock_report"]')

You can clearly see the victim and its blocking, the InputBuffer strings for each (which won't see through a stored procedure unfortunately) and the same stuff you'd be used to seeing in a Trace - spid, hostname, clientapp, date/time.  Use any XML viewer to view the output, including SSMS's native ability to view XML.

Slides and sample code from DMV Overview presentation at SQL Saturday Houston, January 2011

Thanks to a great room at 8:30AM at SQL Saturday Houston in Bammel, TX on January 29, 2011.

I had to leave town before lunch but it was a pleasure meeting you all.

Click here to download a .zip file with the slidedeck and all sample files - even the ones we didn't get to during the presentation.

Please hit me with a rating at SpeakerRate: http://speakerrate.com/speakers/11375

"The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020."

I know this doesn't make a lot of sense so far, but from what I can tell, the Fast Load OLE DB Destination mode can fail if an unconnected ADO destination in the same Data Flow also fails.  

Got this error in SSIS with an OLE DB Destination:

"The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020."

This error only popped up when an error in an unrelated transformation in the same data flow had an error.  I stopped receiving this error when I changed from a "Table or View - fast load" to "Table or View".

That's the short of it.  Here's more:

This is a puzzling error apparently caused by an error with a transformation with an ADO NET Destination, that runs concurrently and in parallel - that is, unconnected - to my OLE DB Destination.

I don't really have the time to figure out all the different permutations that allow this error to keep coming up, but safe to say its not really an error... it seems to be caused by other errors, but the important thing to note is that an error in one transformation was able to allow an unrelated transformation to fail.  This can be very misleading when trying to debug a screen full of red blocks.

The error that came up was a run of the mill aggravating data type error caused by SSIS being picky -

[SLRP ADO [4902]] Error: An exception has occurred during data insertion, the message returned from the provider is: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.

Will keep an eye out for more stuff like this in SSIS, open to feedback.