Db Blocking/Locking

Posted on 2007-10-01
Last Modified: 2012-05-05
I need a script that can send an email to when
there is

1. a lock on the database Customer  
2. The head of the blocking chain
3. the query that the spid that is blocking is running....

Question by:ohemaa
    LVL 16

    Expert Comment

    CREATE Proc sp_getlocks

    I'm sure this will answer your questions...

    alittle more you. as well as doing a google search for more information on lock types, etc.

    Author Comment

    I have seen all these links.  just not sure how to put it all in a nice simple script..
    LVL 16

    Accepted Solution

    It is what is called shared intent lock on a table.

    Try creating the following procedure (I put it in the master database). It will give more informative lock info:

    CREATE Proc sp_getlocks
    Create Table #locks
          (spid       int,
          dbid       int,
          objid       int,
          indid       int,
          type       char(5),
          Resource nvarchar(255),
          Mode      nvarchar(255),
          Status      nvarchar(255))

    Insert #Locks Exec Master.dbo.sp_lock

    select  P.loginame, P.spid, as 'Database', as 'Object' ,P.cmd,
          l.type as 'LockSize', l.mode as 'lockMode', l.status, P.blocked, P.waittime
          From #Locks L
          Join sysobjects S
                on L.ObjID = S.ID
          Join Master.dbo.sysprocesses P
                on L.spid = P.spid
          Join master.dbo.sysdatabases D
                on l.dbid = d.dbid
          Order by p.loginame,P.spid, P.dbid,

    DROP TABLE #Locks

    Author Comment

    Does not alert me.   I am looking for some form of alert so that we ca resolve the issue before users start calling
    1. a lock occurs on  database  
    2. The head of the blocking chain or who is causing the locking/blocking
    3. the query that the spid that is blocking is running....

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now