Post reply

Locks - Blocked Processes

  • August 11, 2009 at 10:51 pm

    #133811

    Hi,

    We are using Spotlight for monitoring and we are getting Blocked processes alarms. I would like to know where can I check this blocked processes in SQL server. I have checked the error log But I did not find any errors.

    Spotlight Enterprise has raised an alarm:

    Connection: ins1_sqlserver

    Time: 10/08/09 18:28:34.042

    Severity: High

    Locks - Blocked Processes: 1 processes are currently waiting on locks (blocked).

    thanks

  • August 12, 2009 at 1:52 am

    #1037874

    sp_who2 is ur friend. You can use following from performance monitor:

    SQLServer:Locks Lock Timeouts/sec _Total

    SQLServer:Locks Number of Deadlocks/sec _Total

    -LK

  • August 12, 2009 at 6:39 am

    #1037949

    Hi

    Use DBCC Traceon (1222, 3605, -1) for this. This will ouput the deadlock information to the error log. Analyzing the dead lock info so got is slightly complicated if you have not doe it before. There are some good articles in this site for the same.

    "Keep Trying"

  • August 12, 2009 at 2:58 pm

    #1038321

    -T1222 is on our server and I'm getting dead lock graph in error log whenever a dead lock occurs. But how to deal with Blocking?

  • August 12, 2009 at 11:28 pm

    #1038466

    Hi

    You need to analyze the deadlock graph and see which process is causing the deadlock. You will be able to make out the stored procedure/query which is causing this. Once you get this info you can do the necessary optimizations.

    I am not an expert at analyzing the deadlock graph, but there are others here who can do that. Maybe if you can post the graph somebody can help.

    "Keep Trying"

  • August 13, 2009 at 3:18 am

    #1038563

    Mani (8/12/2009)

    -T1222 is on our server and I'm getting dead lock graph in error log whenever a dead lock occurs. But how to deal with Blocking?

    select * from sys.dm_exec_requests

    where blocking_session_id 0

    and wait_time/1000 >= 120Above query shows information about sessions blocked for more than 2 minutes.

    Customize it according to your requrement.

    Schedule a SQL Server Agent job to run once in a few minutes to email you the list of blocked processes.

    If you receive email alert, deal with it.

    Hope it helps you.

  • August 13, 2009 at 9:11 am

    #1038766

    Hi,

    sp_who2 is useful for blocking. it will show which SPID is blocked by whom.

    Then you can use DBCC INPUTBUFFER (SPID ID) to see what the SPID is actually doing i.e the T-SQL used by the SPID.

    also, have a look at : http://technet.microsoft.com/en-us/library/cc966540.aspx

    hope this helps,

    \\K 🙂

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • August 13, 2009 at 10:20 am

    #1038818

    Since you already have Spotlight running, did you try drilling down to have it show you the sessions and queries involved in the blocking? If the blocking has already cleared, you should be able to use the history feature to set the display time back to when the block occurred, and then drill down.

  • August 17, 2009 at 12:48 pm

    #1040422

    SSMS-Management-Activity Monitor will dynamically show all processes with blocked/blocking status.

    -MarkO

    "You do not really understand something until you can explain it to your grandmother" - Albert Einstein

  • August 17, 2009 at 12:53 pm

    #1040424

    The view, sys.sysprocesses will help you in all what you need.

    example: select * from sys.sysprocesses where blocked =1

  • September 19, 2013 at 4:00 am

    #1651423

    try this - select * from sys.sysprocesses where blocked > 0

Viewing 11 posts - 1 through 10 (of 10 total)

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