One of our users is trying to do a consumption Journal and she say’s Locked out by another user, please try again later. She’s been trying for over a 1/2 hour and keeps getting the same message. Is there anyway to tell who is locking her out???
Thanks in advance,
Greg
Hi!
Which Platform do you use? C/SIDE or SQL Server?
With SQL I would like to refer to my BLOG here: http://dynamicsuser.net/blogs/stryk/archive/2008/05/12/blocks-amp-deadlocks-in-nav-with-sql-server.aspx
WIth “native” NAV you could check out the “Perfromance Troubleshootig Tools”, specifically the “Session Monitor” feature …
Regards,
Jörg
Thanks. We use the SQL version 5.1.
I’ll check your blog out now…
Greg
I have to admit that the code example on my BLOG are somewhat buggy (it’s a version somewhere between SQL 2000 and 2005 compatibility[:$]);
I actually have updated version available, but have not updated my BLOG so far; please find here the BETA version of the new stuff (SQL Server 2005 only):
The new Table:
create table [dbo].[ssi_BlockLog]
(
[entry_no] bigint identity constraint [ssi_BlockLog$pk_ci] primary key clustered,
[timestamp] datetime,
[db] varchar(128) collate database_default,
[waitresource] varchar(128),
[table_name] varchar(128) collate database_default,
[index_name] varchar(128) collate database_default,
[waittime] bigint,
[lastwaittype] varchar(128),
[spid] int,
[loginame] varchar(128) collate database_default,
[hostname] varchar(128) collate database_default,
[program_name] varchar(128) collate database_default,
[cmd] nvarchar(max) collate database_default,
[status] varchar(128) collate database_default,
[cpu] bigint,
[lock_timeout] int,
[blocked by] int,
[loginame 2] varchar(128) collate database_default,
[hostname 2] varchar(128) collate database_default,
[program_name 2] varchar(128) collate database_default,
[cmd 2] nvarchar(max) collate database_default,
[status 2] varchar(128) collate database_default,
[cpu 2] bigint,
[block_orig_id] int,
[block_orig_loginame] varchar(128) collate database_default
)
go
The new Procedure:
create procedure dbo.ssi_blockdetection
@mode varchar(10) = ‘loop’, – “loop” or “once”
@threshold int = 1000, – Block threshold in milliseconds
@frequency int = 3, – Check frequency in milliseconds
@save tinyint = 0 – save output to table ssi_BlockLog (0 = no, 1 = yes)
with encryption
asif @mode <> ‘once’ begin
print ‘’
print ’ STRYK System Improvement ’
print ’ Performance Optimization & Troubleshooting ’
print ’ (c) 2008, STRYK System Improvement, Jörg Stryk ’
print ’ www.stryk.info ’
print '’
print ’ Version 4.00, Date: 24.10.2008 ’
print ‘’
endif (@mode not in (‘loop’, ‘once’)) begin
raiserror (‘ERROR: Invalid Parameter @mode: %s’, 15, 1, @mode)
return
end
if (@threshold < 1) begin
raiserror (‘ERROR: Invalid Parameter @threshold: %i’, 15, 1, @threshold)
return
end
if (@frequency < 1) begin
raiserror (‘ERROR: Invalid Parameter @frequency: %i’, 15, 1, @frequency)
return
end
if (@save not in (0,1)) begin
raiserror (‘ERRor: Invalid Parameter @save: %i’, 15, 1, @save)
return
endif @mode <> ‘once’ begin
print 'Block Detection Mode : ’ + @mode
print 'Block Threshold (msec): ’ + convert(varchar(15), @threshold)
print 'Check Frequency (sec) : ’ + convert(varchar(10), @frequency)
print 'Save Output to table : ’ + convert(varchar(10), @save)
print ‘’
print ‘Searching for blocked processes …’
print ‘’
endset nocount on
set statistics io off
declare @spid int, @spid2 int, @loginame varchar(128), @blocked_by int, @blocked_by_name varchar(128), @orig_id int, @orig_name varchar(128), @timestmp datetime, @i intif @mode = ‘once’
goto start_checkwhile 1 = 1 begin
start_check:
if exists (select * from sys.dm_exec_requests where [blocking_session_id] <> 0) begin
print 'Checkpoint ’ + convert(varchar(30), getdate())if @save = 0 begin
select distinct
[db] = db_name(s1.[database_id]),
[waitresource] = ltrim(rtrim(s1.[wait_resource])),
[table_name] = object_name(sl.rsc_objid),
[index_name] = si.[name],
s1.[wait_time],
s1.[last_wait_type],
s1.[session_id],
session1.[login_name],
session1.[host_name],
session1.[program_name],
[cmd] = isnull(st1.[text], ‘’),
session1.[status],
session1.[cpu_time],
s1.[lock_timeout],
[blocked by] = s1.[blocking_session_id],
[login_name 2] = session2.[login_name],
[hostname 2] = session2.[host_name],
[program_name 2] = session2.[program_name],
[cmd 2] = isnull(st2.[text], ‘’),
session2.[status],
session2.[cpu_time]
– Process Requests
from sys.dm_exec_requests (nolock) s1
left join sys.dm_exec_requests (nolock) s2 on s2.[session_id] = s1.[blocking_session_id]
– Sessions
left join sys.dm_exec_sessions (nolock) session1 on session1.[session_id] = s1.[session_id]
left join sys.dm_exec_sessions (nolock) session2 on session2.[session_id] = s1.[blocking_session_id]
– Connections
left join sys.dm_exec_connections (nolock) sc1 on sc1.[session_id] = s1.[session_id]
cross apply sys.dm_exec_sql_text(sc1.most_recent_sql_handle) st1
left join sys.dm_exec_connections (nolock) sc2 on sc2.[session_id] = s2.[session_id]
cross apply sys.dm_exec_sql_text(sc2.most_recent_sql_handle) st2
– Lock-Info
left outer join master.dbo.syslockinfo (nolock) sl on s1.[session_id] = sl.req_spid
– Indexes
left outer join sys.indexes (nolock) si on sl.rsc_objid = si.[object_id] and sl.rsc_indid = si.[index_id]
where s1.[blocking_session_id] <> 0
and (sl.rsc_type in (4,5,6,7)) and sl.rsc_indid <> 0 and sl.req_status = 3
and s1.[wait_time] >= @thresholdend else begin
set @timestmp = getdate()
insert into [ssi_BlockLog]
select distinct
@timestmp,
[db] = db_name(s1.[database_id]),
[waitresource] = ltrim(rtrim(s1.[wait_resource])),
[table_name] = object_name(sl.rsc_objid),
[index_name] = si.[name],
s1.[wait_time],
s1.[last_wait_type],
s1.[session_id],
session1.[login_name],
session1.[host_name],
session1.[program_name],
[cmd] = isnull(st1.[text], ‘’),
session1.[status],
session1.[cpu_time],
s1.[lock_timeout],
[blocked by] = s1.[blocking_session_id],
[login_name 2] = session2.[login_name],
[hostname 2] = session2.[host_name],
[program_name 2] = session2.[program_name],
[cmd 2] = isnull(st2.[text], ‘’),
session2.[status],
session2.[cpu_time],
[block_orig_id] = null,
[block_orig_id] = null
– Process Requests
from sys.dm_exec_requests (nolock) s1
left join sys.dm_exec_requests (nolock) s2 on s2.[session_id] = s1.[blocking_session_id]
– Sessions
left join sys.dm_exec_sessions (nolock) session1 on session1.[session_id] = s1.[session_id]
left join sys.dm_exec_sessions (nolock) session2 on session2.[session_id] = s1.[blocking_session_id]
– Connections
left join sys.dm_exec_connections (nolock) sc1 on sc1.[session_id] = s1.[session_id]
cross apply sys.dm_exec_sql_text(sc1.most_recent_sql_handle) st1
left join sys.dm_exec_connections (nolock) sc2 on sc2.[session_id] = s2.[session_id]
cross apply sys.dm_exec_sql_text(sc2.most_recent_sql_handle) st2
– Lock-Info
left outer join master.dbo.syslockinfo (nolock) sl on s1.[session_id] = sl.req_spid
– Indexes
left outer join sys.indexes (nolock) si on sl.rsc_objid = si.[object_id] and sl.rsc_indid = si.[index_id]
where s1.[blocking_session_id] <> 0
and (sl.rsc_type in (4,5,6,7)) and sl.rsc_indid <> 0 and sl.req_status = 3
and s1.[wait_time] >= @thresholdupdate [dbo].[ssi_BlockLog] set [table_name] = ‘- unknown -’ where [table_name] is null
– get block originator
declare originator_cur cursor for select [blocked by], [loginame 2]
from [dbo].[ssi_BlockLog]
where [timestamp] = @timestmp
for update
open originator_cur
fetch next from originator_cur into @blocked_by, @blocked_by_name
while @@fetch_status = 0 begin
set @i = 0
set @orig_id = @blocked_by
set @orig_name = @blocked_by_name
set @spid2 = @blocked_by
while (@spid2 <> 0) and (@i < 100) begin
if exists(select top 1 [blocked by] from [dbo].[ssi_BlockLog] where ([timestamp] = @timestmp) and ([spid] = @spid2)) begin
select top 1 @spid = [blocked by], @loginame = [loginame 2] from [dbo].[ssi_BlockLog] where ([timestamp] = @timestmp) and ([spid] = @spid2)
set @orig_id = @spid
set @orig_name = @loginame
set @spid2 = @spid
end else
set @spid2 = 0
set @i = @i + 1 – “Emergency Exit”, to avoid recursive loop
end
update [dbo].[ssi_BlockLog] set [block_orig_id] = @orig_id, [block_orig_loginame] = @orig_name where current of originator_cur
fetch next from originator_cur into @blocked_by, @blocked_by_name
end
close originator_cur
deallocate originator_curend
endend_check:
if @mode = ‘once’
returnwaitfor delay @frequency
end
The Alert is the same; the Job step should be
exec ssi_blockdetection
@mode = ‘once’
,@threshold = 1
,@frequency = 1
,@save = 1
Thanks Jorg…
Correction of “Procedure”, better using all OUTER JOIN and OUTER APPLY (sorry about the inconvenience):
create procedure dbo.ssi_blockdetection
@mode varchar(10) = ‘loop’, – “loop” or “once”
@threshold int = 1000, – Block threshold in milliseconds
@frequency int = 3, – Check frequency in milliseconds
@save tinyint = 0 – save output to table ssi_BlockLog (0 = no, 1 = yes)
with encryption
asif @mode <> ‘once’ begin
print ‘’
print ’ STRYK System Improvement ’
print ’ Performance Optimization & Troubleshooting ’
print ’ (c) 2008, STRYK System Improvement, Jörg Stryk ’
print ’ www.stryk.info ’
print '’
print ’ Version 4.00, Date: 24.10.2008 ’
print ‘’
endif (@mode not in (‘loop’, ‘once’)) begin
raiserror (‘ERROR: Invalid Parameter @mode: %s’, 15, 1, @mode)
return
end
if (@threshold < 1) begin
raiserror (‘ERROR: Invalid Parameter @threshold: %i’, 15, 1, @threshold)
return
end
if (@frequency < 1) begin
raiserror (‘ERROR: Invalid Parameter @frequency: %i’, 15, 1, @frequency)
return
end
if (@save not in (0,1)) begin
raiserror (‘ERRor: Invalid Parameter @save: %i’, 15, 1, @save)
return
endif @mode <> ‘once’ begin
print 'Block Detection Mode : ’ + @mode
print 'Block Threshold (msec): ’ + convert(varchar(15), @threshold)
print 'Check Frequency (sec) : ’ + convert(varchar(10), @frequency)
print 'Save Output to table : ’ + convert(varchar(10), @save)
print ‘’
print ‘Searching for blocked processes …’
print ‘’
endset nocount on
set statistics io off
declare @spid int, @spid2 int, @loginame varchar(128), @blocked_by int, @blocked_by_name varchar(128), @orig_id int, @orig_name varchar(128), @timestmp datetime, @i intif @mode = ‘once’
goto start_checkwhile 1 = 1 begin
start_check:
if exists (select * from sys.dm_exec_requests where [blocking_session_id] <> 0) begin
print 'Checkpoint ’ + convert(varchar(30), getdate())if @save = 0 begin
select distinct
[db] = db_name(s1.[database_id]),
[waitresource] = ltrim(rtrim(s1.[wait_resource])),
[table_name] = object_name(sl.rsc_objid),
[index_name] = si.[name],
s1.[wait_time],
s1.[last_wait_type],
s1.[session_id],
session1.[login_name],
session1.[host_name],
session1.[program_name],
[cmd] = isnull(st1.[text], ‘’),
session1.[status],
session1.[cpu_time],
s1.[lock_timeout],
[blocked by] = s1.[blocking_session_id],
[login_name 2] = session2.[login_name],
[hostname 2] = session2.[host_name],
[program_name 2] = session2.[program_name],
[cmd 2] = isnull(st2.[text], ‘’),
session2.[status],
session2.[cpu_time]
– Process Requests
from sys.dm_exec_requests (nolock) s1
outer apply sys.dm_exec_sql_text(s1.sql_handle) st1
left outer join sys.dm_exec_requests (nolock) s2 on s2.[session_id] = s1.[blocking_session_id]
outer apply sys.dm_exec_sql_text(s2.sql_handle) st2
– Sessions
left outer join sys.dm_exec_sessions (nolock) session1 on session1.[session_id] = s1.[session_id]
left outer join sys.dm_exec_sessions (nolock) session2 on session2.[session_id] = s1.[blocking_session_id]
– Lock-Info
left outer join master.dbo.syslockinfo (nolock) sl on s1.[session_id] = sl.req_spid
– Indexes
left outer join sys.indexes (nolock) si on sl.rsc_objid = si.[object_id] and sl.rsc_indid = si.[index_id]
where s1.[blocking_session_id] <> 0
and (sl.rsc_type in (4,5,6,7,8,9)) and sl.rsc_indid <> 0 and sl.req_status = 3
and s1.[wait_time] >= @thresholdend else begin
set @timestmp = getdate()
insert into [ssi_BlockCheck_Tab]
select distinct
@timestmp,
[db] = db_name(s1.[database_id]),
[waitresource] = ltrim(rtrim(s1.[wait_resource])),
[table_name] = object_name(sl.rsc_objid),
[index_name] = si.[name],
s1.[wait_time],
s1.[last_wait_type],
s1.[session_id],
session1.[login_name],
session1.[host_name],
session1.[program_name],
[cmd] = isnull(st1.[text], ‘’),
session1.[status],
session1.[cpu_time],
s1.[lock_timeout],
[blocked by] = s1.[blocking_session_id],
[login_name 2] = session2.[login_name],
[hostname 2] = session2.[host_name],
[program_name 2] = session2.[program_name],
[cmd 2] = isnull(st2.[text], ‘’),
session2.[status],
session2.[cpu_time],
[block_orig_id] = null,
[block_orig_id] = null
– Process Requests
from sys.dm_exec_requests (nolock) s1
outer apply sys.dm_exec_sql_text(s1.sql_handle) st1
left outer join sys.dm_exec_requests (nolock) s2 on s2.[session_id] = s1.[blocking_session_id]
outer apply sys.dm_exec_sql_text(s2.sql_handle) st2
– Sessions
left outer join sys.dm_exec_sessions (nolock) session1 on session1.[session_id] = s1.[session_id]
left outer join sys.dm_exec_sessions (nolock) session2 on session2.[session_id] = s1.[blocking_session_id]
– Lock-Info
left outer join master.dbo.syslockinfo (nolock) sl on s1.[session_id] = sl.req_spid
– Indexes
left outer join sys.indexes (nolock) si on sl.rsc_objid = si.[object_id] and sl.rsc_indid = si.[index_id]
where s1.[blocking_session_id] <> 0
and (sl.rsc_type in (4,5,6,7,8,9)) and sl.rsc_indid <> 0 and sl.req_status = 3
and s1.[wait_time] >= @thresholdupdate [dbo].[ssi_BlockCheck_Tab] set [table_name] = ‘- unknown -’ where [table_name] is null
– get block originator
declare originator_cur cursor for select [blocked by], [loginame 2]
from [dbo].[ssi_BlockCheck_Tab]
where [timestamp] = @timestmp
for update
open originator_cur
fetch next from originator_cur into @blocked_by, @blocked_by_name
while @@fetch_status = 0 begin
set @i = 0
set @orig_id = @blocked_by
set @orig_name = @blocked_by_name
set @spid2 = @blocked_by
while (@spid2 <> 0) and (@i < 100) begin
if exists(select top 1 [blocked by] from [dbo].[ssi_BlockCheck_Tab] where ([timestamp] = @timestmp) and ([spid] = @spid2)) begin
select top 1 @spid = [blocked by], @loginame = [loginame 2] from [dbo].[ssi_BlockCheck_Tab] where ([timestamp] = @timestmp) and ([spid] = @spid2)
set @orig_id = @spid
set @orig_name = @loginame
set @spid2 = @spid
end else
set @spid2 = 0
set @i = @i + 1 – “Emergency Exit”, to avoid recursive loop
end
update [dbo].[ssi_BlockCheck_Tab] set [block_orig_id] = @orig_id, [block_orig_loginame] = @orig_name where current of originator_cur
fetch next from originator_cur into @blocked_by, @blocked_by_name
end
close originator_cur
deallocate originator_curend
endend_check:
if @mode = ‘once’
returnwaitfor delay @frequency
end