Post reply

Notify Operator when Locking Exists

  • November 23, 2005 at 11:31 am

    #89968

    When a lock occurs, people call up saying the application froze up.  I then open Enterprise Manager, into Current Activity, then Locks / Process ID.  I find the process ID that is causing the locks and then Kill Process.

    This is very reactive and I would like to do two things

    1)  Send an Operator (me) a notification that this type of lock occurred.

    2)  Quickly find the NT Account that is associated with the ID causing the lock.  The SQL Account does not help out since one is shared amongst multiple users (this is not ideal but cannot be changed).

    Could anyone provide some direction or help?  I would greatly appreciate it!

  • November 23, 2005 at 11:50 am

    #605518

    Reactionary is more like it.

    Locks are a vital and integral part of the system - if you set up to be emailed every time you get a lock - prepare for system overload!

    If you mean deadlock, thats another issue.  Also if you mean blocking, again that is sometimes a problem.

    I would recommend instead of 'fixing' it each time there's a problem, you compile a list of what processes are causing the problem, and reevaluate them.

  • November 23, 2005 at 12:12 pm

    #605527

    Thanks for the grammatical correction.

    Pardon while I learn the appropriate terminology.  I want to be notified when a blocking or deadlock occur.

    Thanks

     

  • November 23, 2005 at 1:47 pm

    #605561

     

    Not sure how others do it, but I found a scheduled job running every x minutes scanning the sysprocesses table for anything which has a blocked != 0 anda  waittime over a minute or so quite handy.

     

  • November 23, 2005 at 2:42 pm

    #605571

    Great, thanks!

    I ran the query and 2 out of 5 times it returns different results.  What would that suggest, if anything important at all?

    The way the application queries the system in built in, so I have no way to control or change that aspect of the system.

     

    Either way, your advice gives me something to work off of.  I appreciate the time!

  • November 24, 2005 at 4:19 am

    #605662

    Well, blocking occurs when one process has a lock on a resource (eg a table, record, index etc) that another process needs.

    This is normal, and almost unavoidable.  The question is how long the block lasts - and does it become a problem.  I guess you just need to look at this for a while and get an idea for yourself of what can be causing problems.

    Some other things that may help are sp_lock (look in books on line for more info), dbcc opentran, dbcc inputbuffer(#), dbcc outputbuffer(#) where # = spid of the process you want to look at.

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply