Category Archives: Blocking And Deadlock

Capture lead blocker\blocking details in SQL 2008 along with SQL Commands exceeding 60 seconds

It is very hard to find good and bad blocking, in case you need to capture blocking in your database which is exceeding a specific timeframe than below mentioned script can be used.

Step 1 Create Table to store blocking information

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[CatchBlocking](

      [CaptureDate] [datetime] NULL,

      [session id] [smallint] NULL,

      [login] [nvarchar](256) NULL,

      [database] [nvarchar](256) NULL,

      [task state] [nvarchar](120) NULL,

      [command] [nvarchar](32) NULL,

      [application] [nvarchar](256) NULL,

      [head blocker] [varchar](1) NULL,

      [login time] [datetime] NULL,

      [last request start time] [datetime] NULL,

      [monitoring time] [datetime] NULL,

      [blocked minutes] [nvarchar](256) NULL,

      [host name] [nvarchar](256) NULL,

      [text] [nvarchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

 

 

 

Step 2 Insert the below code in a job step which will execute after every minute

 

 

DECLARE @loginname         sysname,

            @databasename      sysname,

            @hostname          sysname,

            @userprocessesonly char(1),

            @headblocker       nvarchar(1),

            @minutesofblocking int,

            @monitortime       datetime

 

— Filter your monitoring here!

—————————————–

SET @loginname = ”

SET @databasename = ”

SET @hostname = ”

SET @userprocessesonly = ‘Y’ –Y

SET @headblocker = ‘1’ — 1

SET @minutesofblocking = ’60’ — 1

–Blocking can be captured in terms of minutes and seconds, below in the script use ss\minutes accordingly to

–capture blocking in minutes\seconds

—————————————–

 

SET @monitortime = getdate()

INSERT INTO dbo.CatchBlocking

SELECT         GETDATE(),

                         [session id] = s.session_id,

                        —  [User Process]  = CONVERT(CHAR(1), s.is_user_process),

                        [login] = s.login_name,

                        [database] = isnull(db_name(p.dbid),

                                                      N”),

                        [task state] = isnull(t.task_state,

                                                        N”),

                        [command] = isnull(r.command,

                                                   N”),

                        [application] = isnull(s.program_name,

                                                         N”),

                        [head blocker] = CASE

                                                       — session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others

                                                       WHEN r2.session_id IS NOT NULL AND

                                                              (r.blocking_session_id = 0  OR

                                                               r.session_id IS NULL) THEN ‘1’

                                                       — session is either not blocking someone, or is blocking someone but is blocked by another party

                                                       ELSE ”

                                                 END,

                        [login time] = s.login_time,

                        [last request start time] = s.last_request_start_time,

                        [monitoring time] = @monitortime,

                        [blocked minutes] = cast(datediff(ss,s.last_request_start_time, @monitortime) AS int),

                        [host name] = isnull(s.host_name,

                                                       N”),

                        st.text AS ‘Text’

FROM            sys.dm_exec_sessions s

LEFT OUTER JOIN sys.dm_exec_connections c

                   ON (s.session_id = c.session_id)

LEFT OUTER JOIN sys.dm_exec_requests r

                   ON (s.session_id = r.session_id)

LEFT OUTER JOIN sys.dm_os_tasks t

                   ON (r.session_id = t.session_id AND

                         r.request_id = t.request_id)

LEFT OUTER JOIN (

                        — In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as

                        — waiting for several different threads.  This will cause that thread to show up in multiple rows

                        — in our grid, which we don’t want.  Use ROW_NUMBER to select the longest wait for each thread,

                        — and use it as representative of the other wait relationships this thread is involved in.

                        SELECT *,

                                 row_number()

                                       OVER (

                                             partition BY waiting_task_address

                                             ORDER BY wait_duration_ms DESC) AS row_num

                         FROM   sys.dm_os_waiting_tasks) w

                   ON (t.task_address = w.waiting_task_address) AND

                        w.row_num = 1

LEFT OUTER JOIN sys.dm_exec_requests r2

                   ON (s.session_id = r2.blocking_session_id)

LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g

                   ON (g.group_id = s.group_id)

LEFT OUTER JOIN sys.sysprocesses p

                   ON (s.session_id = p.spid)

CROSS apply     sys.dm_exec_sql_text(r.sql_handle) AS st

WHERE          

 

P.dbid in (select dbid from  sys.sysdatabases where name like ‘%IDEA%’)    

AND

 

                              (@loginname = ”  OR

                         s.login_name = @loginname) AND

                        (@databasename = ”  OR

                         isnull(db_name(p.dbid),

                                    N”) = @databasename) AND

                        (@hostname = ”  OR

                         @hostname = isnull(s.host_name,

                                                      N”)) AND

                        (s.is_user_process = CASE

                                                             WHEN upper(@userprocessesonly) = ‘Y’ THEN 1

                                                             ELSE s.is_user_process

                                                       END) AND

                        (@headblocker = ”  OR

                         @headblocker = CASE

                                                      — session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others

                                                      WHEN r2.session_id IS NOT NULL AND

                                                             (r.blocking_session_id = 0  OR

                                                              r.session_id IS NULL) THEN ‘1’

                                                      — session is either not blocking someone, or is blocking someone but is blocked by another party

                                                      ELSE ”

                                                END) AND

                        datediff(ss,s.last_request_start_time,@monitortime) >= @minutesofblocking AND

                        (st.text NOT LIKE ‘%BACKUP%’ AND st.text NOT LIKE ‘%RESTORE%’)

ORDER           BY s.session_id

 

— If there is an unreported head blocker session let’s send a new mail.

IF EXISTS (SELECT hbs1.[session id]

               FROM   dbo.CatchBlocking hbs1

               WHERE  cast(hbs1.[monitoring time] AS nvarchar(256)) = cast(@monitortime AS nvarchar(256)) AND

                          hbs1.[session id] NOT IN (SELECT hbs2.[session id]

                                                                  FROM   dbo.CatchBlocking hbs2

                                                                  WHERE  cast(hbs2.[monitoring time] AS nvarchar(256)) < cast(@monitortime AS nvarchar(256)) AND

                                                                           hbs2.[session id] = hbs1.[session id] AND

                                                                           hbs2.[last request start time] = hbs1.[last request start time] AND

                                                                           hbs2.text = hbs1.text))

 

 

select * from dbo.CatchBlocking

GO

Advertisements

Capture blocking details in SQL 2005 along with SQL Commands for both SPID and Blocked

If you want to store all blocking details in your databases in SQL Server 2005 then you can use the below method.

Step 1 Run the below script to create a table

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGOFF

GO

CREATETABLE [dbo].[MyBlockingInfo](

[CaptureDateTime] [datetime] NOTNULL,

[spid] [smallint] NOTNULL,

[kpid] [smallint] NOTNULL,

[blocked] [smallint] NOTNULL,

[waittype] [binary](2)NOTNULL,

[waittime] [bigint] NOTNULL,

[lastwaittype] [nchar](32)NOTNULL,

[waitresource] [nchar](256)NOTNULL,

[dbid] [smallint] NOTNULL,

[uid] [smallint] NULL,

[cpu] [int] NOTNULL,

[physical_io] [bigint] NOTNULL,

[memusage] [int] NOTNULL,

[login_time] [datetime] NOTNULL,

[last_batch] [datetime] NOTNULL,

[ecid] [smallint] NOTNULL,

[open_tran] [smallint] NOTNULL,

[status] [nchar](30)NOTNULL,

[sid] [binary](86)NOTNULL,

[hostname] [nchar](128)NOTNULL,

[program_name] [nchar](128)NOTNULL,

[hostprocess] [nchar](10)NOTNULL,

[cmd] [nchar](16)NOTNULL,

[nt_domain] [nchar](128)NOTNULL,

[nt_username] [nchar](128)NOTNULL,

[net_address] [nchar](12)NOTNULL,

[net_library] [nchar](12)NOTNULL,

[loginame] [nchar](128)NOTNULL,

[context_info] [binary](128)NOTNULL,

[sql_handle] [binary](20)NOTNULL,

[stmt_start] [int] NOTNULL,

[stmt_end] [int] NOTNULL,

[request_id] [int] NOTNULL

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

Step 2 Run the below script to create a table to capture SQL Command

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATETABLE [dbo].[CAPTUREINPUTBUFFERVALUE](

[CPDATETIME] [datetime] NULL,

[SPIDTXT] [nvarchar](4000)NULL,

[BKSPIDTXT] [nvarchar](4000)NULL

)ON [PRIMARY]

Step 3 Run the below script to create trigger on table MyBlocking

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATETRIGGER [dbo].[CAPTUREINPUTBUFFER]

ON [dbo].[MyBlocking]

FORINSERT

AS

begin

DECLARE @SQLCOMMAND VARCHAR(100)

DECLARE @SQLCOMMANDbk VARCHAR(100)

DECLARE @CPDATE DATETIME

DECLARE @SPID INT

DECLARE @BKSPID INT

DECLARE @SPIDTXT  NVARCHAR(4000)

DECLARE @BKSPIDTXT NVARCHAR(4000)

DECLARE @SQLHandle BINARY(20)

DECLARE @SQLHandleBK BINARY(20)

set @CPDATE=(select CAPTUREDATETIME from INSERTED wheredbid=13)

set @SPID=(select SPID from INSERTED wheredbid=13)

set @BKSPID=(select BLOCKED from INSERTED wheredbid=13)

— this will give you the SQLHandle for the culprit SPID

SELECT @SQLHandle =SQL_HANDLEFROM MASTER..SYSPROCESSES WHERE SPID = @SPID

SELECT @SQLHandleBK =SQL_HANDLEFROM MASTER..SYSPROCESSES WHERE SPID = @BKSPID

— this statement will give you the SQL Statement for culprit SPID

SET @SPIDTXT=(SELECT [TEXT] FROM::FN_GET_SQL(@SQLHandle))

SET @BKSPIDTXT=(SELECT [TEXT] FROM::FN_GET_SQL(@SQLHandleBK))

IF @SPID ISNOTNULL

INSERTINTO CAPTUREINPUTBUFFERVALUE VALUES(@CPDATE,@SPIDTXT,@BKSPIDTXT)

END

Step 4 Run the below script to create a table

Create 2 jobs with an interval of 30 seconds inserting sysprocesses information in MyBlocking table.

@command=N’Insert into dbo.MyBlocking

select getdate(),* from sys.sysprocesses where blocked <>0′,

When the above jobs execute they will insert the blocking information in your blocking table MyBlocking and then the trigger will capture the details of running process and the blocking process in another table for future references and output will be shown as below

deadlock