I thought the only way to get Deadlock info out of SQL was to enable trace flags 1204 and 1222 (see http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx) but this isn’t actually the case . ..  behold  .  . .

SELECT
 --first resource
 DB_NAME((CAST (XEventData. XEvent.value ('(data/value)[1]' , 'varchar(max)' ) AS XML)).value ('(deadlock/process-list/process/@currentdb)[1]' , 'int') ) as process1,
 --lockmode
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/@lockMode)[1]' , 'varchar(1)') as lockmode,
 --what sql was running
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/inputbuf)[1]' , 'varchar(max)') AS text ,
 
 
 --second resource
 DB_NAME((CAST (XEventData. XEvent.value ('(data/value)[1]' , 'varchar(max)' ) AS XML)).value ('(deadlock/process-list/process/@currentdb)[2]' , 'int') ) as process2,
 --lockmode
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/@lockMode)[2]' , 'varchar(1)') as lockmode,
 --what sql was running
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/inputbuf)[2]' , 'varchar(max)') AS text ,
 
 
 XEventData .XEvent. value('(@timestamp)[1]' , 'datetime' ) as Timestamp,
 
 
 --the xml
 CAST(XEventData .XEvent. value('(data/value)[1]' , 'varchar(max)' ) AS XML) AS DeadlockGraph
 
 
FROM
 (
 SELECT
 CAST(target_data AS XML ) AS TargetData
 FROM
 sys.dm_xe_session_targets st
 JOIN sys .dm_xe_sessions s
 ON s. address = st. event_session_address
 WHERE
 name = 'system_health'
 ) AS Data
 CROSS APPLY TargetData.nodes ('//RingBufferTarget/event' ) AS XEventData ( XEvent )
WHERE
 XEventData .XEvent. value('@name' , 'varchar(4000)' ) = 'xml_deadlock_report'

 Try it 😀

I wrote a piece last week which described out mirroring could be disabled automatically on failure (http://www.electricwaffle.co.uk/2013/04/disabling-mirroring-automatically/). Part of the inspiration for this was to allow replication to continue in an environment where replication and mirroring were  implemented on the same 2005 instance. By default, when mirroring breaks, any unmirrored changes will not be grabbed by the Log Agent. Therefore a mirror failure stops replication :-(.

However  <<drum roll>> I found a trace flag which when enabled (with hotfixes) allow unmirrored changed to be grabbed for use in replication by the Log Agent. 1448

See the following KB for more information http://support.microsoft.com/kb/937041

Glorious 😀