Select = 'exec master.xp_readerrorlog ' + convert ( varchar ( 3 ), ) INSERT INTO #errors ( entrytime, source, logentry )Д¬ASE WHEN left( logentry, 3 ) = '200' THEN convert ( datetime, substring ( logentry, 0, 23 ))Д¬ASE WHEN left( logentry, 3 ) = '200' THEN substring ( logentry, 24, 10 )Д¬ASE WHEN left( logentry, 3 ) = '200' THEN substring ( logentry, 34, 300 ) Or logentry like '%dead%' or logentry like '%cannot%' or logentry like '%could%' or logentry like '%fail%' or logentry like '%not%' or logentry like '%stop%' or logentry like '%terminate%' or logentry like '%bypass%' or logentry like '%roll%' or logentry like '%truncate%' or logentry like '%upgrade%' or logentry like '%victim%' or logentry like '%recover%' or logentry like '%IO requests taking longer than%' ) AND logentry not like '%errorlog%' AND logentry not like '%dbcc%' Where ( logentry like '%err%' or logentry like '%warn%' or logentry like '%kill%' Insert into #errorlog ( logentry, continuationrow )Дelete #errorlog where log_id = max ( log_id ) Select = 'exec master.Sp_readerrorlog ' + convert ( varchar ( 3 ), ) If object_id ( 'tempdb.#errorlog' ) is not null If object_id ( 'tempdb.#errors' ) is not null Set = rtrim ( ltrim ( substring (, 22, 5 ))) ( int = 0 )Дeclare smallint varchar ( 1000 varchar ( 255 datetime varchar ( 10 ) I have found this solution to be very successful so far, and it triggers an alert when the deadlock happens, instead of having to poll the SQL Server Error Log every 5 minutes, which might be quite an intensive process, depending on how much information gets generated there. Just remember to substitute your own SQL Agent Job owner, and e-mail operator for and. It is not necessary, as it simply parses the graph from the DeadlockEvents table for information for non-DBAs who may need help reading the XML generated by the WMI. If you have satisfied all the above requirements, then you can use this script I created, posted separately, which also creates an optional stored procedure dbo.sp_GetDeadLockInfo which I used to conveniently display information generated from the deadlock graph.

This may require you to restart SQL Server Agent service for changes to take effect. You must have enabled DatabaseMail mail profile and chosen to 'Replace tokens for all job responses to alerts' in SQL Server Agent Alert System properties.

Backup exec 16 schedule and occurrences