Wednesday 16 May 2012

Configuring Deadlock Alert

/* Configuring Deadlock Alert on PRD servers.*/.

 

/* Step 1  -- Enable Trace Flag 1222 to 1 to capture deadlock information  in log files
Note: Trace flag will be reset after every SQL server reset so we need to enable flag after every SQL services restart or configure at startup parameter
*/
/* Step 2 --  Creating an Deadlock Alert and Trigger the Deadlock Job
USE [msdb]
GO

/****** Object:  Alert [DeadLock Alert]    Script Date: 05/16/2012 23:28:16 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DeadLock Alert',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0',
@job_id=N'8a31ce05-3880-4fa4-a020-0263135d0129'
--Job ID needs to be replaced
GO


*/

/* -- Step 3.Create an new job and place an below Query in Job Step*/

/*--Create an temporary table  to capture the data from the log file */
IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
BEGIN
DROP TABLE tempdb.dbo.ErrorLog
END
CREATE TABLE tempdb.dbo.ErrorLog
(Id int IDENTITY (1, 1) NOT NULL,
logdate DATETIME, procInfo VARCHAR(10),
ERRORLOG VARCHAR(MAX))
/*--Insert the data into errorlog table  from logs using SP_readerrorlog.*/
INSERT INTO tempdb.dbo.ErrorLog
EXEC master.dbo.sp_readerrorlog
/*--Query to execute the alert using DB mail */
BEGIN
DECLARE @servername nvarchar(150)
SET @servername = @@servername
DECLARE @mysubject nvarchar(200)
SET @mysubject = 'Deadlock event notification on server '+@servername+'.'
/* --Use this query in SQl 2000
--IF EXISTS(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))
--BEGIN
-- EXEC msdb.dbo.sp_send_dbmail
-- @profile_name ='DB Mail Profile',
-- @recipients='Deadlock@Gmail.com;Perfteam@Gmail.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)
  --   and convert(varchar,logdate,112)=convert(varchar,getutcdate(),112)',
-- @query_result_width = 600,
-- @attach_query_result_as_file = 1,
-- @importance='HIGH'

*/

/* Below query is applicable for SQL 2005 higher versions*/
IF EXISTS(
SELECT TOP 1 B.PROCINFO FROM TEMPDB.DBO.ERRORLOG B ,
(SELECT TOP 1 A.LOGDATE, A.PROCINFO FROM TEMPDB.DBO.ERRORLOG A
WHERE A.ERRORLOG LIKE '%Deadlock%' and A.ERRORLOG not like '%EXEC dbo.proc_MSS%'
--AND CONVERT(VARCHAR,A.LOGDATE,112)=CONVERT(VARCHAR,GETUTCDATE(),112)
ORDER BY A.PROCINFO DESC
)WHERE B.LOGDATE=A.LOGDATE AND B.PROCINFO=A.PROCINFO
)

/* Above query is used to find the deadlock in the Errolog table if deadlock exists then below DB Mail Script will be execute with captured deadlock information and will send the mail*/
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='DB Mail Profile',
@recipients='Deadlock@Gmail.com',
@copy_recipients='Perfteam@slk-soft.com',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info',
/*Actual Query to get the deadlock information and send the information via mail*/
@query = 'SELECT B.* FROM TEMPDB.DBO.ERRORLOG B ,
(SELECT TOP 1 A.LOGDATE, A.PROCINFO FROM TEMPDB.DBO.ERRORLOG A
WHERE A.ERRORLOG LIKE ''%Deadlock%''
--AND CONVERT(VARCHAR,A.LOGDATE,112)=CONVERT(VARCHAR,GETUTCDATE(),112)
ORDER BY A.PROCINFO DESC
)A
WHERE B.LOGDATE=A.LOGDATE AND B.PROCINFO=A.PROCINFO ORDER BY 1',
@query_result_width = 600,
@attach_query_result_as_file = 1,
@importance='HIGH'

END
END
DROP TABLE tempdb.dbo.ErrorLog



1 comment:

  1. INTERNATIONAL CONCEPT OF WORK FROM HOME
    Work from home theory is fast gaining popularity because of the freedom and flexibility that comes with it. Since one is not bound by fixed working hours, they can schedule their work at the time when they feel most productive and convenient to them. Women & Men benefit a lot from this concept of work since they can balance their home and work perfectly. People mostly find that in this situation, their productivity is higher and stress levels lower. Those who like isolation and a tranquil work environment also tend to prefer this way of working. Today, with the kind of communication networks available, millions of people worldwide are considering this option.

    Women & Men who want to be independent but cannot afford to leave their responsibilities at home aside will benefit a lot from this concept of work. It makes it easier to maintain a healthy balance between home and work. The family doesn't get neglected and you can get your work done too. You can thus effectively juggle home responsibilities with your career. Working from home is definitely a viable option but it also needs a lot of hard work and discipline. You have to make a time schedule for yourself and stick to it. There will be a time frame of course for any job you take up and you have to fulfill that project within that time frame.

    There are many things that can be done working from home. A few of them is listed below that will give you a general idea about the benefits of this concept.

    Baby-sitting
    This is the most common and highly preferred job that Women & Men like doing. Since in today's competitive world both the parents have to work they need a secure place to leave behind their children who will take care of them and parents can also relax without being worried all the time. In this job you don't require any degree or qualifications. You only have to know how to take care of children. Parents are happy to pay handsome salary and you can also earn a lot without putting too much of an effort.

    Nursery
    For those who have a garden or an open space at your disposal and are also interested in gardening can go for this method of earning money. If given proper time and efforts nursery business can flourish very well and you will earn handsomely. But just as all jobs establishing it will be a bit difficult but the end results are outstanding.

    Freelance
    Freelance can be in different wings. Either you can be a freelance reporter or a freelance photographer. You can also do designing or be in the advertising field doing project on your own. Being independent and working independently will depend on your field of work and the availability of its worth in the market. If you like doing jewellery designing you can do that at home totally independently. You can also work on freelancing as a marketing executive working from home. Wanna know more, email us on workfromhome.otr@gmail.com and we will send you information on how you can actually work as a marketing freelancer.


    Internet related work
    This is a very vast field and here sky is the limit. All you need is a computer and Internet facility. Whatever field you are into work at home is perfect match in the software field. You can match your time according to your convenience and complete whatever projects you get. To learn more about how to work from home, contact us today on workfromhome.otr@gmail.comand our team will get you started on some excellent work from home projects.

    Diet food
    Since now a days Women & Men are more conscious of the food that they eat hence they prefer to have homemade low cal food and if you can start supplying low cal food to various offices then it will be a very good source of income and not too much of efforts. You can hire a few ladies who will help you out and this can be a good business.

    Thus think over this concept and go ahead.

    ReplyDelete