Send email when DeadlockHappens and track Queries

CAUTION, as this is going to use the error log , please make sure you test in test environment before implementing in Production.

/*Capture deadlocks

CREATE Alert Deadlock ERROR 1205>SQL SERVER Performance Condition Alert>Locks>Number OF deadlocks/sec>_total>raises above 0
Response> CREATE a JOB WITH below script amd notify dba >options email


---To ensure the deadlock related errors are logged
EXEC master.sys.sp_altermessage 1205, 'WITH_LOG', TRUE;
GO
EXEC master.sys.sp_altermessage 3928, 'WITH_LOG', TRUE;
GO

--DBCC TRACEON (3605,1204,1222,-1)
Description:
3605 the results of the DBCC output to the error log.
1204 returns the resources and types of locks participating in the deadlock and the current command affected.
1222 Back to the resources and types of locks participating in the deadlock, and the use does not comply with any XSD schema in XML format affected the current command (further than 1204, SQL 2005 and available).
-1 Open the specified trace flag globally.
DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)----this is going to add 100 lines in log file


*/
--== This is for SQL 2005 and higher. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog 0
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==--
 declare @mysubject nvarchar(200)
 set @mysubject = 'Deadlock event notification on server '+@servername+'.'
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==--
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name='Default',
 @recipients='abc@xyz.com,
 @subject = @mysubject,
 @body = 'Deadlock has occurred. View attachment to see the deadlock info',
 @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog

 --------------testing
-----------------------------------TESTING Below
 --DROP TABLE ##Employees
 --DROP TABLE ##Suppliers

USE AuditDB
 GO
 CREATE TABLE ##Employees ( EmpId INT IDENTITY, EmpName VARCHAR(16), Phone VARCHAR(16) )
 GO
 INSERT INTO ##Employees (EmpName, Phone)
 VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
 GO

 CREATE TABLE ##Suppliers( SupplierId INT IDENTITY, SupplierName VARCHAR(64), Fax VARCHAR(16) )
 GO
 INSERT INTO ##Suppliers (SupplierName, Fax) VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
 GO

 --TAB1
 BEGIN TRAN;
 UPDATE ##Suppliers
 SET Fax = N'676-1019'
 WHERE supplierid = 1


 ----IN the query tab 2
 --BEGIN TRAN;
 --UPDATE ##Employees
 --SET EmpName = 'Brian'
 --WHERE empid = 1

 --Now again in the query tab 1 EXECUTE
 UPDATE ##Employees
 SET phone = N'555-9999'
 WHERE empid = 1
 COMMIT


 ----And in the query tab 2  EXECUTE
 --UPDATE ##Suppliers
 --SET Fax = N'676-1019'
 --WHERE supplierid = 1
 --COMMIT