Unfortunately, we've dealt with too may issues with this where I work. In cases where this has been a problem, people who don't know databases, literally open a SQL table "live" in MS Access. At first things can appear transient because they may open and close Access quickly. But then there are those moments where they leave MS Access open on the live SQL table while they go away for lunch or perhaps through the long weekend--blocking applications that try to use the database. Just to state the obvious--blocking can adversely affect any application trying to use the blocked SQL row, table, or database.
All we really had to do to prove anything was capture data from sp_who2 to find the blocking chains.
That being said, when I'm away from the console, I still use the following code I wrote over a decade ago (I mean I'm still using DBCC InputBuffer) to find these pesky issues -- if needed (please run in a test/dev environment to see if it meets your needs). Though certainly not the Rolls Royce of routines it still works for me.
This code essentially creates a table, BlockingProcesses, in a new database DBADMIN to store the blocking information. Two stored procedures sp__Maint_BlockWatch and sp__Maint_Blockingprocesses work together to find the blockers. And a SQL job runs once a minute to check for blocks. You may need to change minor things in the code below like the user who needs to execute the job, possible scheduling, etc.
CREATE DATABASE DBADMIN
GO
USE [dbadmin]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BlockingProcesses]
(
[PK] [int] IDENTITY(1,1) NOT NULL,
[last_batch] [datetime] NULL,
[spid] [int] NULL,
[BlockedTotal] [int] NULL,
[LoginName] [varchar](128) NULL,
[DBName] [varchar](128) NULL,
[HostName] [varchar](128) NULL,
[Program_Name] [varchar](255) NULL,
[CPU] [bigint] NULL,
[Physical_IO] [bigint] NULL,
[Memusage] [bigint] NULL,
[Open_tran] [int] NULL,
[EventInfo] [varchar](max) NULL,
[InsertTime] [smalldatetime] NULL,
[Params] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BlockingProcesses]
ADD DEFAULT (getdate()) FOR [InsertTime]
GO
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp__Maint_BlockWatch]
AS
declare @blocker smallint
DECLARE blocker_cursor CURSOR FOR
select distinct blocked
from sysprocesses
where blocked != 0
OPEN blocker_cursor
FETCH NEXT FROM blocker_cursor INTO @blocker
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM blocker_cursor INTO @blocker
CONTINUE
END
exec sp__Maint_Blockingprocesses @blocker
FETCH NEXT FROM blocker_cursor INTO @blocker
END
DEALLOCATE blocker_cursor
GO
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp__Maint_BlockingProcesses] Script Date: 09/20/2016 13:31:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp__Maint_BlockingProcesses]
@spid smallint
as
declare
@blockedTotal int,
@loginame varchar(128),
@dbid int,
@hostname varchar(128),
@program_name varchar(128),
@cpu bigint,
@physical_io bigint,
@memusage bigint,
@open_tran int,
@dbname varchar(128),
@EventInfo varchar(255),
@Last_batch datetime
create table #temp
(
EventType varchar(50),
Parameters int,
EventInfo varchar(255)
)
insert into #temp
exec ('dbcc inputbuffer('+@spid+')')
select @EventInfo=eventinfo from #temp
select @blockedTotal= count(*) from sysprocesses where blocked = @spid
select @loginame=loginame, @dbid=dbid, @hostname=hostname, @program_name=program_name, @cpu=cpu,
@physical_io = physical_io, @memusage=[memusage], @open_tran=open_tran, @Last_batch=last_batch
from sysprocesses where spid=@spid
select @dbname = name from sysdatabases where dbid=@dbid
insert into dbadmin..blockingprocesses(last_batch,spid, BlockedTotal, LoginName, DBName, HostName, Program_Name, CPU, Physical_IO, Memusage, Open_tran, EventInfo)
values(@last_batch, @spid, @BlockedTotal, @LogiName, @DBName, @HostName, @Program_Name, @CPU, @Physical_IO, @Memusage, @Open_tran, @EventInfo)
drop table #temp
GO
USE [msdb]
GO
/****** Object: Job [_Monitor Blocks] Script Date: 09/20/2016 13:29:34 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/20/2016 13:29:34 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_Monitor Blocks',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Look For Blocking] Script Date: 09/20/2016 13:29:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Look For Blocking',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec sp__Maint_BlockWatch',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Once a Minute',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20060918,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
--, @schedule_uid=N'bf518f11-c5ba-438a-8afd-e4e33e8dad1e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
