How to capture SQL Server blocking information
Many a times Application team report SQL Server Blocking issues. In this article I will explain about Blocking and Its cause. Due to this blocking issue Application team asks the DBA team to capture the code which is causing the blocking. SQL Server profiler is one of the tool through which we can capture the SQL Statements running at the database server while users are accessing the application but as you all know that this tool is highly resource intensive and it’s not advised to run it specially in the production hours. I will go through a work around to setup and capture the SQL Server queries to identify the blocking issue and resolve it.
What is blocking?
“Blocking” occurs when one user connection is holding lock on an object or resource and at the same time another user connection tries to read or write on the same resource or objects.
This is a kind of scenario when a baby is holding a ball and he\she is blocking other babies from holding the same ball.
This is how the output of sp_who2 will look like in case there is any kind of blocking on the database server.
In the above example SPID 54 is blocking SPID ID 56.
We can schedule a SQL Server job to capture the blocking SPID’s SQL statements and troubleshoot the blocking issue.
Step 1: Create the stored procedure to capture the blocking information and wait types using below script.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_blocker_pss08] Script Date: 08/12/2016 07:34:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_blocker_pss08] (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')
as
--version 19.2005 - 2005 or Later
if is_member('sysadmin')=0
begin
print 'Must be a member of the sysadmin group in order to run this procedure'
return
end
set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant
declare @request varchar(12)
set @time = getdate()
declare @probclients table(spid smallint, request_id int, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
ignore_app tinyint, primary key (blocked, spid, request_id, ecid))
insert @probclients select spid, request_id, ecid, blocked, waittype, dbid,
case when convert(varchar(128),hostname) = @appname then 1 else 0 end
from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000
if exists (select spid from @probclients where ignore_app != 1)
begin
set @time2 = getdate()
print ''
print '9.0 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2005 '+ltrim(str(@latch))+' '+ltrim(str(@fast))
insert @probclients select distinct blocked, 0, 0, 0, 0x0000, 0, 0 from @probclients
where blocked not in (select spid from @probclients) and blocked != 0
if (@fast = 1)
begin
print ''
print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)
select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage, last_batch=convert(varchar(26), last_batch,121),
login_time=convert(varchar(26), login_time,121),net_address,
net_library, dbid, ecid, kpid, hostname, hostprocess,
loginame, program_name, nt_domain, nt_username, uid, sid,
sql_handle, stmt_start, stmt_end, request_id
from master.dbo.sysprocesses
where blocked!=0 or waittype != 0x0000
or spid in (select blocked from @probclients where blocked != 0)
or spid in (select spid from @probclients where blocked != 0)
print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))
print ''
print 'SYSPROC FIRST PASS'
select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000
if exists(select blocked from @probclients where blocked != 0)
begin
print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
print 'SPIDs at the head of blocking chains'
select distinct spid from @probclients -- change: added distinct
where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
if @latch = 0 and exists (select spid from @probclients where waittype between 0x0001 and 0x0017) -- Change: exists
begin
print 'SYSLOCKINFO'
select @time2 = getdate()
select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then 'NUL'
when 2 then 'DB'
when 3 then 'FIL'
when 4 then 'IDX'
when 5 then 'TAB'
when 6 then 'PAG'
when 7 then 'KEY'
when 8 then 'EXT'
when 9 then 'RID'
when 10 then 'APP' end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then 'Sch-S'
when 3 then 'Sch-M'
when 4 then 'S'
when 5 then 'U'
when 6 then 'X'
when 7 then 'IS'
when 8 then 'IU'
when 9 then 'IX'
when 10 then 'SIU'
when 11 then 'SIX'
when 12 then 'UIX'
when 13 then 'BU'
when 14 then 'RangeS-S'
when 15 then 'RangeS-U'
when 16 then 'RangeIn-Null'
when 17 then 'RangeIn-S'
when 18 then 'RangeIn-U'
when 19 then 'RangeIn-X'
when 20 then 'RangeX-S'
when 21 then 'RangeX-U'
when 22 then 'RangeX-X'end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end,
req_transactionID As TransID, req_transactionUOW As TransUOW
from master.dbo.syslockinfo s,
@probclients p
where p.spid = s.req_spid
--and ((p.waittype between 0x0001 and 0x0017) or ()) --change: added line
print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))
end -- latch not set
end
else
print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
end -- fast set
else
begin -- Fast not set
print ''
print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)
select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage, last_batch=convert(varchar(26), last_batch,121),
login_time=convert(varchar(26), login_time,121),net_address,
net_library, dbid, ecid, kpid, hostname, hostprocess,
loginame, program_name, nt_domain, nt_username, uid, sid,
sql_handle, stmt_start, stmt_end, request_id
from master.dbo.sysprocesses
print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))
print ''
print 'SYSPROC FIRST PASS'
select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000
if exists(select blocked from @probclients where blocked != 0)
begin
print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
print 'SPIDs at the head of blocking chains'
select spid from @probclients
where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
if @latch = 0
begin
print 'SYSLOCKINFO'
select @time2 = getdate()
select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then 'NUL'
when 2 then 'DB'
when 3 then 'FIL'
when 4 then 'IDX'
when 5 then 'TAB'
when 6 then 'PAG'
when 7 then 'KEY'
when 8 then 'EXT'
when 9 then 'RID'
when 10 then 'APP' end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then 'Sch-S'
when 3 then 'Sch-M'
when 4 then 'S'
when 5 then 'U'
when 6 then 'X'
when 7 then 'IS'
when 8 then 'IU'
when 9 then 'IX'
when 10 then 'SIU'
when 11 then 'SIX'
when 12 then 'UIX'
when 13 then 'BU'
when 14 then 'RangeS-S'
when 15 then 'RangeS-U'
when 16 then 'RangeIn-Null'
when 17 then 'RangeIn-S'
when 18 then 'RangeIn-U'
when 19 then 'RangeIn-X'
when 20 then 'RangeX-S'
when 21 then 'RangeX-U'
when 22 then 'RangeX-X'end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end,
req_transactionID As TransID, req_transactionUOW As TransUOW
from master.dbo.syslockinfo
print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))
end -- latch not set
end
else
print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
end -- Fast not set
print 'sys.dm_os_wait_stats'
select * from sys.dm_os_wait_stats where waiting_tasks_count > 0
print 'OWS'
Print ''
Print '*********************************************************************'
Print 'Print out SQL Statements for all blocked or blocking spids.'
Print '*********************************************************************'
declare ibuffer cursor fast_forward for
select distinct cast (spid as varchar(6)) as spid, cast (request_id as varchar(12)) as request_id
from @probclients
where (spid <> @@spid) and (spid > 50) and
((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
or spid in (select blocked from @probclients where blocked != 0))
open ibuffer
fetch next from ibuffer into @spid, @request
while (@@fetch_status != -1)
begin
print ''
--print 'DBCC INPUTBUFFER FOR SPID ' + @spid +'('+@request+')'
print 'SQL Statement running FOR SPID ' + @spid +'('+@request+')'
--exec ('dbcc inputbuffer (' + @spid + ',' + @request +')')
SELECT
p.spid AS [SPID],
--ltrim(rtrim(db_name(p.dbid))) AS [DBName],
CASE WHEN txt.encrypted = 0 THEN txt.text ELSE N'encrypted' END AS [Query] ,
p.login_time AS [Start Time],
p.last_batch AS [Last Batch]
FROM sys.sysprocesses p
INNER JOIN sys.dm_exec_connections c (NOLOCK)
ON c.session_id = p.spid
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) as txt
WHERE p.spid = @spid
fetch next from ibuffer into @spid, @request
end
deallocate ibuffer
Print ''
Print '*******************************************************************************'
Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
Print '*******************************************************************************'
declare ibuffer cursor fast_forward for
select distinct cast (dbid as varchar(6)) from @probclients
where dbid != 0
open ibuffer
fetch next from ibuffer into @spid
while (@@fetch_status != -1)
begin
print ''
set @dbname = db_name(@spid)
set @status = DATABASEPROPERTYEX(@dbname,'Status')
set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'
dbcc opentran(@dbname)
else
print 'Skipped: Status=' + convert(nvarchar(128),@status)
+ ' UserAccess=' + convert(nvarchar(128),@useraccess)
print ''
if @spid = '2' select @blocked = 'Y'
fetch next from ibuffer into @spid
end
deallocate ibuffer
if @blocked != 'Y'
begin
print ''
print 'DBCC OPENTRAN FOR DBID 2 [tempdb]'
dbcc opentran ('tempdb')
end
print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
+ convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2005'
GO
Note: By default this stored procedure will be created in master database, if you want you can change the database to some other user database. Also this stored procedure will display information about the wait types as well.
once the stored procedure is created you can schedule a job to run it using a batch file and capture the output in a text file as below.
Step 2: Create a batch file using below content and save it on the server.
rem @echo off set hh=%time:~0,2% if "%time:~0,1%"==" " set hh=0%hh:~1,1% set yymmdd_hhmmss=%date:~10,4%%date:~4,2%%date:~7,2%_%hh%%time:~3,2%%time:~6,2%echo %2 sqlcmd -S%1 -E -w2000 -Q"exec master.dbo.sp_blocker_pss80" >>D:\blocker_check_script_%1_%yymmdd_hhmmss%.out
Step 3: Schedule a job to run the above batch file and pass the SQL Server instance name in the job. You can schedule it to run it every minute or 5 minute as per the requirement.
Note: Keep an eye on the disk space on the location where the blocking script output is stored and if required purge or move the old files to some other location.
This is how the output of the blocker script will look like and it will capture the SQL Statements along with other information.
Sample Output:
9.0 Start time: 2016-08-12 07:20:24.363 0 19.2005 1 1
SYSPROCESSES USER-PC\TECHNO 171050560
spid status blocked open_tran waitresource waittype waittime cmd lastwaittype cpu physical_io memusage last_batch login_time net_address net_library dbid ecid kpid hostname hostprocess loginame program_name nt_domain nt_username uid sid sql_handle stmt_start stmt_end request_id
------ ------------------------------ ------- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------- ---------------- -------------------------------- ----------- -------------------- ----------- -------------------------- -------------------------- ------------ ------------ ------ ------ ------ -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ---------------------------------------------------------------------------------------- ---------------------- ----------- ----------- -----------
2 background 0 0 0x0158 27134 XE TIMER XE_TIMER_EVENT 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 0 0 2456 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
3 background 0 0 0x015A 7738805 XE DISPATCHER XE_DISPATCHER_WAIT 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 0 0 2180 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
4 background 0 0 0x0060 297 LAZY WRITER LAZYWRITER_SLEEP 546 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 0 0 1840 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
5 background 0 0 0x007F 97152 LOG WRITER LOGMGR_QUEUE 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 0 0 312 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
6 background 0 0 0x0080 1460 LOCK MONITOR REQUEST_FOR_DEADLOCK_SEARCH 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 0 0 1860 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
7 background 0 0 0x009D 7731875 SIGNAL HANDLER KSOURCE_WAKEUP 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 1 0 4352 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
9 background 0 0 0x00A2 796 TRACE QUEUE TASK SQLTRACE_INCREMENTAL_FLUSH_SLEEP 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 1 0 2540 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
10 background 0 0 0x015E 526 BRKR TASK BROKER_TO_FLUSH 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 1 0 1284 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
12 background 0 0 0x007E 7736829 TASK MANAGER ONDEMAND_TASK_QUEUE 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 1 0 956 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
13 background 0 0 0x0081 74097 CHECKPOINT CHECKPOINT_QUEUE 0 4 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 2 0 4332 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
14 background 0 0 0x00AE 7732213 BRKR EVENT HNDLR BROKER_EVENTHANDLER 31 21 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 1 0 1692 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
17 background 0 0 0x00A9 7733744 BRKR TASK BROKER_TRANSMITTER 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 1 0 3240 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
18 background 0 0 0x00A9 7733743 BRKR TASK BROKER_TRANSMITTER 0 0 0 2016-08-12 05:11:32.293 2016-08-12 05:11:32.293 1 0 3300 sa 1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000 0 0 0
54 sleeping 0 1 0x0000 0 AWAITING COMMAND MISCELLANEOUS 265 3 2 2016-08-12 05:29:40.413 2016-08-12 05:27:27.153 DED502BF4CF9 LPC 5 0 0 TECH 4112 TECH\user Microsoft SQL Server Management Studio - Query TECH user 1 0x010500000000000515000000F80B6F0A8F0FBF8F892C993BE8030000000000000000000000000000000000 0x0100050081CEE804A00E 0 0 0
56 suspended 54 0 RID: 5:1:194:18 0x0003 6636491 SELECT LCK_M_S 0 0 2 2016-08-12 05:29:45.363 2016-08-12 05:29:45.353 DED502BF4CF9 LPC 5 0 4972 TECH 4112 TECH\user Microsoft SQL Server Management Studio - Query TECH user 1 0x010500000000000515000000F80B6F0A8F0FBF8F892C993BE8030000000000000000000000000000000000 0x01000500CA302D34E00D 0 -1 0
ESP 30
SYSPROC FIRST PASS
spid request_id ecid waittype
------ ----------- ------ --------
2 0 0 0x0158
3 0 0 0x015A
4 0 0 0x0060
5 0 0 0x007F
6 0 0 0x0080
7 0 0 0x009D
9 0 0 0x00A2
10 0 0 0x015E
12 0 0 0x007E
13 0 0 0x0081
14 0 0 0x00AE
17 0 0 0x00A9
18 0 0 0x00A9
56 0 0 0x0003
Blocking via locks at 2016-08-12 07:20:24.363
SPIDs at the head of blocking chains
spid
------
54
sys.dm_os_wait_stats
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
LCK_M_SCH_M 13 89 37 0
LCK_M_S 1 0 0 0
LCK_M_X 5 160 77 2
LATCH_SH 1 0 0 0
LATCH_EX 5 27 27 27
PAGELATCH_SH 37 0 0 0
PAGELATCH_EX 47 32 31 0
PAGEIOLATCH_SH 662 9585 142 96
PAGEIOLATCH_UP 20 180 37 0
PAGEIOLATCH_EX 6 104 28 0
LAZYWRITER_SLEEP 7767 7736985 1056 416
IO_COMPLETION 82 2168 115 0
ASYNC_IO_COMPLETION 1 256 256 0
ASYNC_NETWORK_IO 1123 1982 240 20
CHKPT 1 1948 1948 105
SLEEP_DBSTARTUP 17 1843 136 111
SLEEP_DCOMSTARTUP 2 600 300 0
SLEEP_TASK 10493 3866249 1058 330
SLEEP_SYSTEMTASK 1 1844 1844 0
OLEDB 13 49 23 0
THREADPOOL 6 1 0 0
SOS_SCHEDULER_YIELD 1400 52 36 49
ONDEMAND_TASK_QUEUE 1 0 0 0
LOGMGR_QUEUE 42 7641520 3660215 67
REQUEST_FOR_DEADLOCK_SEARCH 1548 7736933 5018 7736933
CHECKPOINT_QUEUE 9 7662622 2661412 2
KSOURCE_WAKEUP 1 0 0 0
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 1934 7735979 4015 9
BROKER_TRANSMITTER 2 0 0 0
BROKER_MASTERSTART 1 1 1 0
BROKER_EVENTHANDLER 8 744 295 82
WRITELOG 53 384 20 1
MSQL_XP 98 1043 453 0
LOGBUFFER 3 0 0 0
EE_SPECPROC_MAP_INIT 1 16 16 0
BROKER_TASK_STOP 6 40094 10007 0
DAC_INIT 1 1 1 0
XE_TIMER_EVENT 427 7711575 30007 7710255
XE_DISPATCHER_WAIT 1 0 0 0
BROKER_TO_FLUSH 3769 3867447 1071 199
PREEMPTIVE_OS_GENERICOPS 15 1988 688 0
PREEMPTIVE_OS_AUTHENTICATIONOPS 2035 286 4 0
PREEMPTIVE_OS_DECRYPTMESSAGE 339 12 0 0
PREEMPTIVE_OS_DELETESECURITYCONTEXT 330 34 4 0
PREEMPTIVE_OS_AUTHORIZATIONOPS 357 21 5 0
PREEMPTIVE_OS_LOOKUPACCOUNTSID 341 177 1 0
PREEMPTIVE_OS_REVERTTOSELF 339 6 0 0
PREEMPTIVE_OS_COMOPS 1 497 497 0
PREEMPTIVE_OS_CRYPTOPS 2 849 756 0
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT 31 6 0 0
PREEMPTIVE_OS_CRYPTIMPORTKEY 8 2 2 0
PREEMPTIVE_OS_DOMAINSERVICESOPS 1 1 1 0
PREEMPTIVE_OS_FILEOPS 60 568 251 0
PREEMPTIVE_OS_CREATEFILE 45 2 0 0
PREEMPTIVE_FILESIZEGET 14 4 3 0
PREEMPTIVE_OS_GETFILEATTRIBUTES 38 120 58 0
PREEMPTIVE_OS_WRITEFILEGATHER 6 111 59 0
PREEMPTIVE_OS_LIBRARYOPS 1 2573 2573 0
PREEMPTIVE_OS_GETPROCADDRESS 98 252 76 0
PREEMPTIVE_OS_LOADLIBRARY 2 129 65 0
PREEMPTIVE_OS_PIPEOPS 1 613 613 0
PREEMPTIVE_OS_DISCONNECTNAMEDPIPE 330 15 2 0
PREEMPTIVE_OS_REPORTEVENT 37 137 125 0
PREEMPTIVE_OS_WAITFORSINGLEOBJECT 136 1748 240 0
PREEMPTIVE_OS_QUERYREGISTRY 128 30 1 0
PREEMPTIVE_OS_SQMLAUNCH 1 0 0 0
PREEMPTIVE_XE_CALLBACKEXECUTE 8 0 0 0
PREEMPTIVE_XE_DISPATCHER 1 0 0 0
PREEMPTIVE_XE_SESSIONCOMMIT 1 0 0 0
PREEMPTIVE_XE_TARGETINIT 1 0 0 0
PREEMPTIVE_XE_TIMERRUN 1 0 0 0
PREEMPTIVE_LOCKMONITOR 1 0 0 0
WRITE_COMPLETION 43 476 38 0
FT_IFTSHC_MUTEX 1 1396 1396 0
FT_IFTS_SCHEDULER_IDLE_WAIT 128 7620270 60009 5
PERFORMANCE_COUNTERS_RWLOCK 2 0 0 0
SQLTRACE_FILE_WRITE_IO_COMPLETION 4 26 26 0
OWS
*********************************************************************
Print out SQL Statements for all blocked or blocking spids.
*********************************************************************
SQL Statement running FOR SPID 54(0)
SPID Query Start Time Last Batch
------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- -----------------------
54 begin tran a
insert into student(name,fname,lname)
values('dd','dsds','dwdw')
2016-08-12 05:27:27.153 2016-08-12 05:29:40.413
SQL Statement running FOR SPID 56(0)
SPID Query Start Time Last Batch
------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- -----------------------
56 select * from student
2016-08-12 05:29:45.353 2016-08-12 05:29:45.363
*******************************************************************************
Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.
*******************************************************************************
DBCC OPENTRAN FOR DBID 1 [master]
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC OPENTRAN FOR DBID 2 [tempdb]
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC OPENTRAN FOR DBID 5 [admin]
Transaction information for database 'admin'.
Oldest active transaction:
SPID (server process ID): 54
UID (user ID) : -1
Name : a
LSN : (22:187:2)
Start time : Aug 12 2016 5:29:40:413AM
SID : 0x010500000000000515000000f80b6f0a8f0fbf8f892c993be8030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
End time: 2016-08-12 07:20:24.423
References: The stored procedure has been used from below Microsoft support site and modified to use the DMVs in place of DBCC inputbuffer.
Vesa Juvonen
August 12, 2016Hi, could you provide this “Step 2: Create a batch file using below content and save it on the server.” also as text, now it is picture?
Technical Editor
August 12, 2016Hi Vesa,
Thanks for your feedback. I have added the text version of the syntax along with the image.
Thanks