About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a certain form and is released once the user has finished scrolling through the form or closes it. Since many of our users open this form as reference, these locks stay around for a while. Any update to the table causes the blocking, and suddenly nobody can select from this table since they're all waiting on the first lock. This is quite a problem for us, since lots of apps rely on this data. I understand that this locking behavior is part of how Access works with linked tables.

I've been solving the problem from Activity Monitor, by killing whichever SELECT process is the Head Blocker whenever I find out about it. This is a problem not only because it takes me time to do it manually, but also because it's reactive. By the time I hear about it, it's already been a problem for a lot of people.

I'd like to know if there is an automatic way to check for these long-lasting blocking chains, and either be emailed or have the problem resolved automatically. The logic seems straightforward enough ("if any process matching this SELECT query has been blocking for longer than a minute, notify me/kill it") but I don't know how to implement this with SQL Server.

For what it's worth, I think the proper solution is to fix or rewrite the app. However, due to departmental politics, this is not an option for the next few months, so I'm looking for a stopgap.


Have you considered using snapshot isolation? Enabling read_committed_snapshot in the database will cause all reads (selects) to be lock free:

alter database [...] set read_committed_snapshot on;

No application changes. Some semantics change under snapshot and your application may react weirdly, but that is the exception not the norm. The vast majority of applications don't notice any difference, they just get a free performance boost.

Anyway, I though to answer also the original question: how to detect (and possibly kill) a long running query. Actually, the engine already does that for you. There is an event raised when a threshold is passed: Blocked Process Report Event Class. The threshold is configured via the blocked process threshold Option. Any trace event can be turned into an Event Notification and event notifications can activate procedures. Connect the dots and you have on-demand activated code that runs when the engine detects a query that has crossed an execution time threshold. No polling, no monitoring. Note though that the notification is asynchronous, by the time you process it the query may had completed so that has to be taken into account.

Here is an example:

use msdb;

create queue blocked_process_report_queue;

create service blocked_process_report_service
    on queue blocked_process_report_queue

create event notification blocked_process_report_notification
    on server
    to service N'blocked_process_report_service',
          N'current database';

sp_configure 'show advanced options', 1 ;
sp_configure 'blocked process threshold', 20 ;

Now in a new query set up a WAITFOR expecting a notification:

use msdb;
   receive cast(message_body as xml), * 
   from  blocked_process_report_queue);

And go ahead and cause some blockage. I used a process that created a table and didn't commit, and from another query windows I tried to select from the table. In 20 seconds (my configured threshold above) I got the blocking report:

    <process id="process1b5a24ca8" ...>
        <frame line="1" stmtstart="-1"... />
          select * from t   </inputbuf>
    <process status="sleeping" spid="51" ...>
      <executionStack />
         begin transaction
         create table t (a int)   </inputbuf>

I'll leave the task of wrapping this up into an automated process as an exercise to the reader. And yes, the queue/service/activated procedure must be in [msdb].

  • I haven't, but I'm definitely going to read up on it! What kind of weirdness should I be looking for? If it's generally a performance boost, is there a reason snapshot isolation isn't enabled by default? – Warrior Bob Mar 15 '12 at 21:50
  • chasing the link inside the link provided, read through this and see how it applies to your situation – swasheck Mar 15 '12 at 21:59
  • 3
    I recommend reading Comparing Different Results with RCSI & Read Committed and the links at the end. Special worries are warranted if you have multi-statement UDFs, eg. Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent. Ultimately you need to test. But again, majority of cases there are no visible effects. – Remus Rusanu Mar 15 '12 at 22:08
  • 1
    No visible effects on the app, I agree. In the database system you'll want to keep an eye on the tempdb. There's more load there from read_committed_snapshot. – Grant Fritchey Mar 16 '12 at 11:07
  • 1
    @AlexKuznetsov: The verry way RCSI is deployed reveals its nature: is deployed by one single change to DB and it silently maps read-committed to snapshot for each statement. This all reads to me 'desperate attempt to fix a broken app that cannot be changed'. The OP is currently considering killing blocking processes every N minutes. Giving RCSI a test drive seems quite reasonable to me in such case. I know from experience that the number of cases that RCSI helps and does not break things far outweighs the cases when problems occur. – Remus Rusanu Mar 16 '12 at 16:51

You can either build your own monitoring tool or look to a 3rd party solution that can provide one for you. If you're interested in building your own, it depends on what version of SQL Server you're working with. If it's 2005, you can use the Blocked Process Report trace event. If you're running 2008 or above, I'd suggest using the equivalent extended event, blocked_process_report. Jonathan Kehayias has a good write up on how to use it.

If you're looking at 3rd party products, Red Gate software's SQL Monitor has blocked process and long running process alerts built in.


Although this does not address how to notify you of the problem, this procedure will show you how to query to see if blocking exists. It will also generate kill commands for you, if you pass in the correct parameter.

Hope this gives you some ideas.

IF (object_id('Who') IS NOT NULL)
  print 'Dropping procedure: Who'
  drop procedure Who
print 'Creating procedure: Who'
  @db varchar(100) = '%',
  @kill char(1) = 'N',
  @tran char(1) = 'N'

/*  This proc should be rewritten to take advantage of:
  select * from sys.dm_exec_connections
  select * from sys.dm_exec_sessions
  select * from sys.dm_exec_requests

-- Date Created: July 17, 2007
-- Author:       Bill McEvoy
-- Description:  This procedure gives a summary report and a detailed report of the logged-in
--               processes.  This procedure is a derivative of sp_who3 which I wrote in 2002.
-- Date Revised: 
-- Author:       
-- Reason:       
set nocount on

-- Validate input parameters                                       --

-- M A I N   P R O C E S S I N G                                   --
--                                                                 --
-- Generate login summary report                                   --
--                                                                 --
--                                                                 --

-- Generate login summary report                                   --

select 'loginame'   = convert(char(30),loginame),
       'connection' = count(*),
       'phys_io'    = str(sum(physical_io),10),
--       'cpu'        = sum(cpu),
       'cpu(mm:ss)' = str((sum(cpu)/1000/60),12) + ':' + case 
                                            when left((str(((sum(cpu)/1000) % 60),2)),1) = ' ' then stuff(str(((sum(cpu)/1000) %60),2),1,1,'0') 
       'wait_time'  = str(sum(waittime),12),
       'Total Memory(MB)' = convert(decimal(12,2),sum(convert(float,memusage) * 8192.0 / 1024.0 / 1024.0))
from master.dbo.sysprocesses
where db_name(dbid) like @db
  and not (loginame = 'sa' and program_name = '' and db_name(dbid) = 'master')
group by loginame
order by 3 desc

-- Generate detailed activity report                               --

select 'loginame'     = left(loginame, 30),
       'hostname'     = left(hostname,25),
       'database'     = left(db_name(dbid),25),
       'spid'         = str(spid,4,0),
       'block'        = str(blocked,5,0),
       'phys_io'      = str(physical_io,10,0),
       'cpu(mm:ss)'   = str((cpu/1000/60),10) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END,
       'mem(MB)'      = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),
       'program_name' = left(program_name,50),
       'command'      = cmd,
       'lastwaittype' = left(lastwaittype,20),
       'login_time'   = convert(char(19),login_time,120),
       'last_batch'   = convert(char(19),last_batch,120),
       'status'       = left(nt_username,20)
  from master..sysprocesses
where db_name(dbid) like @db
order by 5,4

-- Generate KILL commands                                          --

IF (upper(@Kill) = 'Y')
  select 'kill' + ' ' + str(spid,4,0)
    from master..sysprocesses
  where db_name(dbid) like @db
  order by spid

-- Report on open transactions                                     --

IF (UPPER(@Tran) = 'Y')

  -- Create the temporary table to accept the results.
  IF (object_id('tempdb..#Transactions') is NOT NULL)
    DROP TABLE #Transactions
  CREATE TABLE #Transactions
    DatabaseName    varchar(30),
    TransactionName varchar(25),
    Details         sql_variant 

  -- Execute the command, putting the results in the table.
  exec sp_msforeachdb '
  INSERT INTO #Transactions (TransactionName, Details)
  update #Transactions 
     set DatabaseName = ''[?]''
   where DatabaseName is NULL'

  -- Display the results.
  SELECT * FROM #Transactions order by transactionname

IF (object_id('Who') IS NOT NULL)
  print 'Procedure created'
  print 'Procedure NOT created'

exec who @tran=Y
  • You're giving him the hammer before letting him study better the blocking problems :-). I'd say you'd better change the condition to kill only MSACCESS sessions :D. – Marian Mar 15 '12 at 20:42
  • I was just trying to show how start investigating...it is an old proc though...likely won't work on 2012 – datagod Mar 16 '12 at 2:41

I'd suggest reading the following MSDN forum topic. It's about locking caused by Access to an SQL Server db. The suggestion is mainly to access the tables by queries using NOLOCK hint, so that the won't cause any locking problem. NOLOCK isn't the best solution, as it can cause other problems, but will reduce most of your locking problems.

The better solution would be to implement Remus's idea, setup snapshot isolation on your database. Or implement snapshot isolation level only for certain connections that you find causing blocking.

In order to properly monitor your server for blocking issues I'd suggest:

  • build a server side trace that will monitor blocking issues longer than x seconds (I'd say 5 is good enough);
  • save upper traces every day so you have a history for at least the last 30 days to see trends and patterns;
  • have an hourly job that studies the current days' trace file and email to you any interesting blocking situations;

If you want a proactive response to this issue, instead of having a job every hour to monitor the traces, make it run every minute and kill any leading blocking Access session.


Following @Remus Rusanu's excellent answer, I have done the reader's task to connect the event to a stored procedure.

In my case the sp will write the xml of the blocking event to a table, but you are free to do whatever you want at that position.

So, follow Remus' code and create the queue, the service and the notification with a simple copy/paste from above. Add the sp_configure options and you are basically set.

The only thing left to do are

  • Create a SP with no arguments.
  • Create a table for the SP to write the data into (for my example, your SP may vary)
  • Activate the SP on the queue

As soon as you activated the SP, the events will start flowing into your table.

I found out that the queue deactivates itself immediately, if the SP has an error. In that case, you need to go to Server Studio and activate it again in the context menu of the queue-entry ([msdb]->Service Broker->Warteschlangen in German version).

It took me quite some time to get this working and to find the right spots in the documentation, so I suppose this is helpful for others as well. I am using SQLServer 2005.

Create the SP with no arguments

  DECLARE @RecvReqMsg XML;
  DECLARE @RecvReqMsgName sysname;

  WHILE (1=1)


    ( RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle,
        @RecvReqMsg = message_body,
        @RecvReqMsgName = message_type_name
      FROM blocked_process_report_queue
    ), TIMEOUT 5000;

    IF (@@ROWCOUNT = 0)

    IF @RecvReqMsgName = 
        print 'Insert SQL to pdix_lock_events'
        INSERT INTO pdix_lock_events
               VALUES(null, cast( @RecvReqMsg as xml));

    ELSE IF @RecvReqMsgName =
       END CONVERSATION @RecvReqDlgHandle;
    ELSE IF @RecvReqMsgName =
       END CONVERSATION @RecvReqDlgHandle;



Create the pdix_lock_events table

USE [msdb]

/****** Object:  Table [dbo].[pdix_lock_events]    Script Date: 05/06/2015 17:48:36 ******/


CREATE TABLE [dbo].[pdix_lock_events](
    [locktime] [timestamp] NULL,
    [lockevent] [xml] NOT NULL


Activate the SP on the queue

alter queue blocked_process_report_queue with activation( 
    max_queue_readers = 1, 
    status = on, 
    execute as 'dbo');  

Your Answer

