• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

Db Blocking/Locking

I need a script that can send an email to Ohemaa@yahoo.com 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....

0
ohemaa
Asked:
ohemaa
  • 2
  • 2
1 Solution
 
SQL_SERVER_DBACommented:
CREATE Proc sp_getlocks

I'm sure this will answer your questions...
http://articles.techrepublic.com.com/5100-9592_11-6088055.html

alittle more you.
 http://msdn2.microsoft.com/en-us/library/ms187749.aspx as well as doing a google search for more information on lock types, etc.
0
 
ohemaaAuthor Commented:
I have seen all these links.  just not sure how to put it all in a nice simple script..
0
 
SQL_SERVER_DBACommented:
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
as
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, d.name as 'Database', S.name 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, s.name

DROP TABLE #Locks
Return
GO
0
 
ohemaaAuthor Commented:
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....
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now