April 1, 2010 at 4:37 pm
Hi,
We have SQL Server 2005 EE x64 with SP3 and enabled the trace flag -T1222 to write the dead lock graph to Error log. Is there way to get an email when dead lock occurs? because when dead lock occur, it' writing to error log but until unless users call about the problem and then I need to go & see the error log for the dead lock graph.
But to proactively act, is there any script which look for dead lock graph in the error log and if it find the dead lock, then send an email?
and also for blocking?
Thanks
April 1, 2010 at 4:50 pm
You can create a job that reads the errorlog every five minutes (insert the errorlog into a temp table), if there's a deadlock in the last five minutes (or one minutes, or ten, or whatever the frequency you choose), then send an alert out.
here's an article about how to import the log into a temp table
Job:
Step 1: import table
Step 2: search table within n minutes, send alert if necessary
setp 3: drop table
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 1, 2010 at 5:24 pm
GregoryF (4/1/2010)
You can create a job that reads the errorlog every five minutes (insert the errorlog into a temp table), if there's a deadlock in the last five minutes (or one minutes, or ten, or whatever the frequency you choose), then send an alert out.here's an article about how to import the log into a temp table
Job:
Step 1: import table
Step 2: search table within n minutes, send alert if necessary
setp 3: drop table
It looks like you forgot the article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2010 at 5:31 pm
Thanks, yeah, I gues I did
here is the article to import the log
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 1, 2010 at 11:49 pm
I have created the procedure that given in the link and executing as below
exec sp_import_errorlog
@log_name =errorlog,
@log_number = 0,
@overwrite = 0
but getting the below error:
Msg 213, Level 16, State 7, Procedure xp_readerrorlog, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
please advice
April 2, 2010 at 8:19 am
gmamata7 (4/1/2010)
I have created the procedure that given in the link and executing as belowexec sp_import_errorlog
@log_name =errorlog,
@log_number = 0,
@overwrite = 0
but getting the below error:
Msg 213, Level 16, State 7, Procedure xp_readerrorlog, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
please advice
Post the table definition and proc definition as you have created them.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 1:03 pm
Thanks,
I ran the below script first:
CREATE PROC sp_import_errorlog
(
@log_name sysname,
@log_number int = 0,
@overwrite bit = 0
)
AS
/*************************************************************************************************
Purpose:To import the SQL Server error log into a table, so that it can be queried
Written by:Anand Mahendra
Tested on: SQL Server 2000
Limitation: With error messages spanning more than one line only the first line is included in the table
Email: anandbox@sify.com
Example 1: To import the current error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog'
Example 2: To import the current error log to table myerrorlog, and overwrite the table
'myerrorlog' if it already exists
EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1
Example 3: To import the previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 1
Example 4: To import the second previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 2
*************************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log
IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL
BEGIN
IF @overwrite = 0
BEGIN
RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
RETURN -1
END
ELSE
BEGIN
EXEC('DROP TABLE ' + @log_name)
END
END
--Temp table to hold the output of sp_readerrorlog
CREATE TABLE #errlog
(
err varchar(1000),
controw tinyint
)
--Populating the temp table using sp_readerrorlog
INSERT #errlog
EXEC sp_readerrorlog @log_number
--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #errlog
SET ROWCOUNT 0
SET @sql = 'SELECT
CONVERT(DATETIME,LEFT(err,23)) [Date],
SUBSTRING(err,24,10) [spid],
RIGHT(err,LEN(err) - 33) [Message],
controw
INTO ' + QUOTENAME(@log_name) +
' FROM #errlog ' +
'WHERE controw = 0'
--Creates the table with the columns Date, spid, message and controw
EXEC (@sql)
--Dropping the temporary table
DROP TABLE #errlog
SET NOCOUNT OFF
PRINT 'Error log successfully imported to table: ' + @log_name
END
and then executed as below:
exec sp_import_errorlog
@log_name =errorlog,
@log_number = 0,
@overwrite = 0
and getting the below error:
Msg 213, Level 16, State 7, Procedure xp_readerrorlog, Line 1
Insert Error: Column name or number of supplied values does not match table definition
Thank you
mamata
April 2, 2010 at 1:16 pm
It looks like the xp_readerrorlog is different in 2005 than in 2000. This is where you are getting the error message. You may need to add some fields to the temp table defined in that proc (#errlog).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 3:03 pm
If you simply want to receive an email when a deadlock occurs, you can configure an alert for the 1205 error that is generated and have it send out an email:
Similar to this:
USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Transaction Deadlocked',
@message_id=1205,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@database_name=N'',
@notification_message=N'',
@event_description_keyword=N'',
@performance_condition=N'',
@wmi_namespace=N'',
@wmi_query=N'',
@job_id=N'6666b46e-33b9-4d63-99ff-9c1c69b138fe'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Transaction Deadlocked', @operator_name=N'DBA - Team', @notification_method = 1
GO
Blocking is a little more complicated because some blocking is natural. I typically create a scheduled job that runs every 1 minute and looks at the count of SPIDs being blocked. If it exceeds a threshold, I have it send out an email to the DBA team.
April 11, 2012 at 6:32 am
--Replace Profile_name with your database mail profile and email address with valid Email
--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
Declare @id int
select @id=Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like '%Deadlock encountered%' and DATEDIFF(MINUTE,logdate,GETDATE())<15 order by Id DESC
Print @id
IF @id is not null
--== 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='Profile_name',
@recipients='EMAIL@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%'' and DATEDIFF(MINUTE,logdate,GETDATE())<15 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
--schedule a job which runs every 15 mins to monitor Errorlog File
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply