Month: June 2015

Get SQL Server deadlock graph instantly on mail when it occurs

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to get SQL Server deadlock graph instantly on mail when deadlock occurs.

Scenario:Lets say,there is one site where you have been deployed and you face deadlock on SQL server mostly.Now you want  to receive mail immediately with complete deadlock graph when SQL Server deadlock occurs.

Infrastructure: Below queries and processes are tested with SQL server2005,2008,2008 R2,2012.

Steps:.

  • First of all,build the queries to capture the deadlock details.once it is done then we will create the deadlock scenario for the testing purpose on test server.
  • Note:The below queries can be used in live server for capturing the deadlock details and to get SQL Server deadlock graph instantly on mail when it occurs.
  • Note: The deadlock graph is captured in sp_readerrorlog only when deadlock trace flags listed below must be enabled.

/* enable trace flags 1222,1204 at global level */

dbcc traceon(1204,-1)

go

dbcc traceon(1222,-1)

go

Or,

  • Go to startup parameter of SQL Server service by opening SQL Server configuration and enter the above two trace flag to get deadlock graph in sp_readerrorlog.This process needed restart of the DB service.
  • Below is the screen shot for the same.

traceFlags

  • Now once the deadlock trace flags get enabled do the following.

/* create the below table which will insert the content from sp_readerrorlog.

Please note this is one time table creation.You can create the table in any database except msdb,model and tempdb.

*/

Create table CaptureErrorLogs

(

ID int identity(1,1),

LogDate DATETIME,

ProcessInfo VARCHAR(255),

Text VARCHAR(MAX)

)

/* insert everything from sp_readerrorlog to newly created table.Put the below whole code into job and do not scheduled it.This Job will automatically get executed when ever the deadlock occurs through the sql alerts.The creation of alerts will be showed latter.Now the job will trigger the mail only when the deadlock occurs which will be having current deadlock graph.

Created by: Dharmesh Mishra
Date/Time:23rd June 2015,09:00 PM
Like my fb page:www.facebook.com/sqlserverkillers
Visit my blogs:lapsql.wordpress.com

*/

INSERT INTO CaptureErrorLogs

EXEC sp_readerrorlog

declare @subject varchar(250)
select @subject = ‘Deadlock reported on ‘ + @@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DBA’,  —change your DB profile name here
@recipients=’dharmesh@xyz.com’,
@subject =’Deadlock’,
@body = ‘A deadlock has been recorded. Further information can be found from the detailed message.’,
@body_format=’text’,
@attach_query_result_as_file = 1 ,
@query = ‘select logdate,processinfo,TEXT from CaptureErrorLogs
where id >=(select MAX(id) from CaptureErrorLogs where TEXT like ”%deadlock encountered%”) and
convert(varchar(10),LogDate,110) = convert(varchar(10),getdate()-0,110) ‘

truncate table CaptureErrorLogs

  • Kindly download the above query from below link.

https://app.box.com/s/9lvxkng2umb8lni6hju9eqscy611mw1r

  • Put this above script in a job and then create an alert which will execute the job when ever there is deadlock occurs.This job will send an attachment and you will receive mail with complete deadlock graph.
  • Note:

If you want the deadlock graph to be on mail with html format then use the below queries instead of above one and replace the below code in job to get html deadlock graph details.

INSERT INTO CaptureErrorLogs

EXEC sp_readerrorlog
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @subject varchar(250)
select @subject = ‘Deadlock reported on ‘ + @@servername

SET @tableHTML =
N'<H1>Instant DeadLock Report </H1>’ +
N'<table border=”1″>’ +
N'<tr><th>LogDate</th><th>ProcessInfo</th>’ +

N'<th>Text</th></tr>’ +
CAST ( ( SELECT td = Logdate, ”,
td = processinfo, ”,
td = text, ”
from CaptureErrorLogs
where id >=(select MAX(id) from CaptureErrorLogs where TEXT like ‘%deadlock encountered%’) and
convert(varchar(10),LogDate,110) = convert(varchar(10),getdate()-0,110)

FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’ ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DBA’,
@recipients=’dharmesh@xyz.com’,
@subject =@subject,
— @body = ‘A deadlock has been recorded. Further information can be found from the detailed message.’,

@body = @tableHTML,
@body_format = ‘HTML’ ;

truncate table CaptureErrorLogs

go

  • Kindly download the above query from below link.

https://app.box.com/s/6a9308ur7x2qxn2gmy9ghsvsumbvkni6

  • Now create an sql server agent alert.
  • Below is the screen shot for the same which is self explanatory

alerts

  • Above is the setting for the general page in alerts
  • Below is the setting for the response page

response

  • Below is setting for the option page

options

  • The last page is history page which shows the date and time the alerts triggers.
  • This is how following above steps you can receive mail with complete deadlock graph when sql server deadlock occurs.
  • So,its time to create to create the deadlock scenario

Note: Do not perform the below on Live server.This is only for your knowledge purpose.

  • Open a new session using ssms and fire the below code

create table deadlock1 (id int)
create table deadlock2 (id int)

insert deadlock1 values(1), (2), (3)

insert deadlock2 values(1), (2), (3)

begin transaction
update deadlock1 set id = 4 where id = 1

waitfor delay ’00:01:00′

update deadlock2 set id = 4 where id = 1
commit transaction

drop table deadlock1
drop table deadlock2

  • Now open the another query window and fire the f5 within 1 mints.

begin transaction
update deadlock2 set id = 4 where id = 1

waitfor delay ’00:00:20′

update deadlock1 set id = 4 where id = 1
commit transaction

  • when you do this then deadlock will occur.Below is the screen shot for the same.

deadlock

  • So,here is the screen shot which you want after the above implementation.

deadlock report

  • This is how you can get SQL Server deadlock graph immediately on mail when it occurs.
  • Done….
  • Please do comments when you find this useful or any suggestion on the same will be highly accepted.

How to send mail notification when SQL blocking occurs more than a defined time

Note:  Best viewed in Internet Explorer 6 and above

Implementation:Sending mail alerts on SQL blocking queries details when particular query blocks other query by more than a defined time let say 2,3.. minutes.

Scenario:Lets say,there is one site where you have been deployed and you face blocking on SQL server all the time.Now your TL want you to send mail notification only when there is blocking more than 3 minutes.In this case,below queries will help you lot for concentrating on blocked queries and finding root blocking SPID.

Infrastructure: Below queries and processes are tested with SQL server2005,2008 R2,2012,2014.

Steps:Before implementing this on Live server lets simulate the blocking environment on an UAT server for your knowledge purpose.

  • First create a table in any user database on UAT server(No need to create this table on Live server) to simulate and understand blocking environment.

CREATE TABLE [dbo].[BlockingScene](
[name] [varchar](20) NULL,
[Add] [varchar](20) NULL,
[City] [varchar](20) NULL
) ON [PRIMARY]

  • Now populate above table with some dummy data.

insert into BlockingScene values(‘Dharmesh’,’Maharashtra’,’Mumbai’)

go 100000

  • Now open two query windows and put the below code in 1st and 2nd query windows resp and execute the queries one by one.

—–1st query window

use SqlDev
go
BEGIN TRANSACTION
select * from blockingScene WITH (TABLOCKx, HOLDLOCK)
waitfor delay ’00:55:00′ —will wait for 55 minutes.The format is ‘hh:mm:ss’
–release lock
commit transaction

—2nd query window

select * from blockingScene

  • Now run the sp_who2 active or below queries to see whether blocking is there.
  • Note:Please note that below query is filtered with where clause i.e. when blocking exceed with duration more than 3 minutes then it will show result.You can change the filter condition on basis of your business requirements.

select sp.spid,sp.blocked, sp.last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, DB_NAME(sp.dbid),
st.[text],sp.loginame,sp.hostname,sp.cpu
from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(mi,last_batch,GETDATE())>3

  • when the above condition true then the below screen shot you will get.

SimulatedSqlBlocking

  • So the above process was to create a blocking environment on UAT server for understanding purpose.Now its time to create the stored procedure for sending email notification on sql blocking only when blocking exceed more than 3 minutes in my case.you can change it to any time as per client needs.
  • Use any database and create below table to capture blocking details.
  • Note:This is one time creation table.

CREATE TABLE [dbo].[CaptureBlockingDetails](
[spid] [smallint] NULL,
[VillainSpid] [smallint] NULL,
[last_batch] [datetime] NULL,
[CurrentTime] [datetime] default getdate() NOT NULL,
[waittime] [bigint] NULL,
[waitresource] [nchar](256) NULL,
[lastwaittype] [nchar](32) NULL,
[CmdStatement] [nchar](16) NULL,
[RunningQueries] [nvarchar](max) NULL,
[DatabaseName] [sysname] NOT NULL,
[LoginName] [varchar](30) NULL,
[HostName] [varchar](30) NULL,
[cpu] [int] NULL
) ON [PRIMARY]
GO

  • Now create stored procedure.

/*
Created by: Dharmesh Mishra
Date/Time:14th June 2015,12:00 PM
Like my fb page:www.facebook.com/sqlserverkillers
Visit my blogs:lapsql.wordpress.com
*/
USE [SqlDev]   —use any system or user database
GO

/****** Object: StoredProcedure [dbo].[Usp_sendMailNotificationWhenBlockingGoesAboveThreeMinutes] Script Date: 06/14/2015 12:02:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[Usp_sendMailNotificationWhenBlockingGoesAboveThreeMinutes]
as
begin

————2nd insert value in created table for which is blocking more than 3 minutes————-
insert into dbo.CaptureBlockingDetails(spid ,
VillainSpid ,
last_batch ,
waittime,
waitresource ,
lastwaittype ,
CmdStatement ,
DatabaseName ,
RunningQueries,
LoginName,
HostName,
cpu

) select sp.spid,sp.blocked, sp.last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, DB_NAME(sp.dbid),
st.[text],sp.loginame,sp.hostname,sp.cpu
from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(mi,last_batch,GETDATE())>3
—final touch for sending mail notification when blocking duration goes above 3 Minutes
if (select top 1 DATEdiff(MI,last_batch,currenttime) from CaptureBlockingDetails where DATEdiff(MI,last_batch,currenttime)>3)>3

begin
print ‘CPU Alert Condition True, Sending Email..’DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1 bgcolor=”magenta”>Blocking Found</H1>’ +
N'<H2 bgcolor=”magenta”>SQL Server Session Details</H2>’ +
N'<table border=”1″>’ +
N'<tr bgcolor=”magenta”><th>spid</th><th>VillainSpid</th><th>last_batch</th><th>CurrentTime</th><th>waittime</th>’+
N'<th>waitresource</th><th>lastwaittype</th><th>CmdStatement</th><th>DatabaseName</th>’+
N'<th>RunningQueries</th><th>LoginName</th><th>HostName</th> <th>cpu</th></tr>’+

CAST ( ( SELECT distinct — or all by using *

td= spid,”,
td= VillainSpid,”,
td= last_batch,”,
td= CurrentTime,”,
td= waittime,”,
td= waitresource,”,
td= lastwaittype,”,
td= CmdStatement,”,
td= DatabaseName,”,
td= RunningQueries,”,
td= LoginName,”,
td= HostName,”,
td=cpu,”from CaptureBlockingDetails
FOR XML PATH(‘tr’), TYPE )AS NVARCHAR(MAX))+N'</table>’
— Change SQL Server Email notification code here
EXEC msdb.dbo.sp_send_dbmail
@recipients=’dharmesh@gmail.com’,
@profile_name = ‘DBA’,
@subject = ‘home:Blocking Found’,
@body = @tableHTML,@body_format = ‘HTML’;
END
— Truncate the Table
truncate Table CaptureBlockingDetails
end

  • Kindly download the above stored procedure from below link.

https://app.box.com/s/uxv9qi9tduwccayiyu7uc98kae1nufjg

  • Now create a job and scheduled with continuous running when SQL agent start.

exec Usp_sendMailNotificationWhenBlockingGoesAboveThreeMinutes

  • The above stored procedure will send the blocking details only when blocking exceed more than 3 minutes.Below is the screen shot for the same.

blockingmail

  • Done..
  • Please comments when you find this useful or any suggestion on the same will be highly accepted.

How to get sp_readerrorlog output into table and sending mail in html format.

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Implementation: Sending html format mail to recipients of ‘sp_readerrorlog’ in sql server.

Scenario:Lets say,Client wants to get the output of ‘sp_readerrorlog’ on mail with proper html and understandable format  and  you are asked to carry out this implementation.In this case,below queries will help you lot.

Infrastructure: Below queries and processes are compatible with SQL server2000,2005,2008 R2,2012,2014.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts
 
Steps:

  • First execute the ‘sp_readerrolog’ in ssms and see the number of columns along with its datatypes. Accordingly create a table in any user database.The new table should have same no of columns and data types as ‘sp_readerrorlog’.

sp_readerrorlog

  • Now create a table to capture the contents of ‘sp_readerrorlog’ in it.
Create table logs1
(
LogDate DATETIME,
ProcessInfo VARCHAR(255),
Text VARCHAR(MAX)
)
  • Now insert into logs1 from ‘sp_readerrorlog’ using below command.
INSERT INTO logs1
EXEC sp_readerrorlog
  • Now cross check once whether the table having contents of ‘sp_readerrolog’ by select table
SELECT * FROM logs1

Imp Note:Here,you can filter the table by putting where clause.

Generally we don’t want all the error log to be mailed.We want only some particular error related log to be mailed.Example: lets say you want login failure error,backup,log shipping error,deadlock error to be mailed.In this cases we can simple put where clause and filter out the text messages from table ‘logs’

Example:

SELECT * FROM logs1 where text like ‘login%’
GO
  • Create a stored procedure which will send the mail in html format to respective person.
  • Below is the stored procedure script-run it once and scheduled the job as per requirements.
Create Procedure [usp_sendErrorlogMail]
  as
  begin
declare @recipients varchar(max)  
declare @message varchar(5000)  
declare @query  varchar(max)  
declare @subject varchar(112)  
declare @attach_results varchar(50)  
set @recipients = ‘ ‘ ——–recipients mail ID.
set @message = ”  
set @query  = ”  
set @subject = ‘ sp_readerrorlog details’——subject for the mail   
set @attach_results = ‘True’  
IF Exists(select 1  from logs1)  —‘select 1 from logs’ is used to check whether logs1 table contents data or not.
begin
———- case 2 starts 
begin  
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
begin
SET @xml = CAST((SELECT [LogDate] AS ‘td’,”,[ProcessInfo] AS ‘td’,”,[Text] AS ‘td’ from 
(SELECT * FROM logs1 where TEXT like ‘login %’ ) tab  
FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))
————————————————————–
SET @body =’

sp_readerrorlog details

This is system generated Report, Please do not reply.

 

 

‘    

 
SET @body = @body + @xml +’
LogDate ProcessInfo Text

 ———- case 2 starts
begin  
  set @message = ‘Please Do not reply on this mail’  
  exec msdb..sp_send_dbmail  
@profile_name = ‘dba’ —–DB mail profile  
     , @recipients=@recipients  
     , @body = @body
     , @subject=  @subject  
     , @body_format =’HTML’
 end  
end  
end
end
else print(‘bye’)
end
  • Kindly download the above script from below link

https://app.box.com/s/byedt058tkbfp4r0pnmp2iankkz0xh4y

  •   Now execute the created SP’s to test.

exec usp_sendErrorlogMail

  • Now ask the recipient to check his/her mail.

mailoutput

  • Now you can simply create a new job in sql server.In first step put
    INSERT INTO logs1
    EXEC sp_readerrorlog

    and second step of job put the created Stored procedure name  i.e. ‘usp_sendErrorlogMail’

and in 3rd step truncate the logs1 table to control the unnecessary growth of the table.

truncate table logs1—————-truncate table
or simply merge the queries in stored procedure itself.
  • Done……