Post reply

Blocking DMVs after the fact

  • October 30, 2009 at 7:49 am

    #211578

    Does anyone know if any of there are any DMVs/DMFs that display blocking after the fact? Does the default trace save this?

    Example: I had some blocking on a database, the session was killed and the blocking stopped, I now want to know what objects were being blocked before it was killed.

    I have no monitoring for this at the moment, any advice?

    Thanks

    BU69

  • October 30, 2009 at 8:18 am

    #1073171

    I don't know of any. We used to have a big problem so I cobbled together a process that runs every minute to look for blocking & then sends me an email & writes "who", "what", and "when" to a table so I can review it later.

  • October 30, 2009 at 9:10 am

    #1073205

    Well, the performance counter dmv's will show how many blocks but not what.

    The Default Trace does not have blocking or deadlocking, you could set up your own server-side trace to monitor.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • October 30, 2009 at 9:39 am

    #1073232

    I've so far set up a trigger in Zabbix (Linux based monitoring tool) to alert me of blocking using the perfmon counter SQL Server General Statistics Processes Blocked, not sure of my next move, that will alert me, I need to set up some sort of trigger, if that's possible to run a script and store a log file somewhere but still working on that, I don't think I can trigger this from Zabbix but I need to check.

    Any ideas how I might do this without having to run a process every minute to detect blocks?

    Thanks

  • October 30, 2009 at 10:42 am

    #1073271

    I have a question. What is the problem you are trying to address by monitoring blocking? Are processes timing out?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 2, 2009 at 1:28 am

    #1073685

    It has to do with a Dynamics database, it keeps getting blocks too frequently which stops all users from working so I'm trying to find out if it's always on the same objects as no-one has bothered to investigate this before and just killed the SPID so the users can get back to work, it's quite a large Dynamics set up, world wide and the biggest in Europe so when users can't work it's quite a big thing so I want to find out straight away when it happens so I can get back to the Dynamics team and tell them where it's going wrong so they can look into it.

    Thanks

    BU69

  • November 2, 2009 at 2:34 am

    #1073713

    i had a similar issue, was a trigger causing the locks

  • November 2, 2009 at 2:37 am

    #1073715

    Do you know which trigger and objects this was?

  • November 2, 2009 at 2:40 am

    #1073719

    yip, was a update trigger trying to update. this was on 2000,when i double clicked the sspid under the locks/objects, it showed the query locking.

    hope this helps

  • November 2, 2009 at 2:47 am

    #1073721

    And this was on a Dynamics database? Do you know which tables the lock occured and what the trigger was called and does?

    When you say 2000 do you mean SQL Server 2000? What version of Dynamics are you running?

    I'm running SQL Server 2005 SP2 with Dynamics 4 AX 4.0.2.0.03 SP2

    Thanks

  • November 2, 2009 at 2:56 am

    #1073725

    no,

    i had this issue on a custom app. but ur problem sounds the same.

  • November 3, 2009 at 8:08 am

    #1074287

    In SQL Server we created an alert based on the Proccessed Blocked counter that sends an email and kicks off a job that collects the blocking info and populates a table. Once in a while the blocks are gone by the time the job kicks off.

    I have not found a counter that could be used for the length of time blocked, rather than the number of blocks.

    jg

  • November 3, 2009 at 8:22 am

    #1074299

    This is the same thing I'm going to do now, use the DMVs in a job kicked off by a Zabbix alert using the performance counter.

    I'll have to experiment with the best script to get some good information.

    Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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