Alert: Blocked Process not showing object

DerekgDerekg Posts: 11
edited July 23, 2012 9:00AM in SQL Monitor Previous Versions
Monitor Version: 3.1.0.125
SQL Version: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

The main alert tab 'Details' doesn't show the details:
Block occurred at: 7:21 PM
Blocked process: Process
Blocking process: Unknown
Object blocked: Database (ReportServer$SQL2008)

It also shows the object blocked is the entire database? It does this for every alert we get for blocking. When I look in sysprocesses though, I can see the proper object id though so I don't think it is correct.

Also, if I go to the 'Processes' tab it *does* show the correct processes and their details, however no object info since that's supposed to be on the 'Details' tab.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,
    From what I can tell, the process "name" is taken from the connection properties of the process that connected to the SQL Server. So I can see that potentially this could be empty.

    I will try to get clarification on the "object blocked" - also on my system every attempt to simulate the blocked process says the object is a database. But you can get an idea of the object in the alert details by looking at the query fragment.
  • The thing is on the detail tab is has both processes names included...so it just doesn't make a whole lot of sense.

    Regarding the object block I am almost positive prior to my upgrading to the latest version of Red Gate Monitor 3 that the "object blocked" field used to actually show the table/index/ect that was block. I could be mistaken about that of course.

    If that's not the case, though, it definitely should be on the list for improvements. Blocked processes typically have the object/page/ect id that is being blocked listed. That would be much more helpful that just the DB name.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Deadlock alerts will show you page/index/table information. Blocked process alerts never have as far as I can tell, but the object names will be in the trace.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Derek,

    Just to confirm, the "process name" in the "SQL Processes" tab are taken from the connection string. For instance, if you were to enter "Application Name=test" in SSMS "advanced" connection properties, the SQL Process name would show up as "test". So if this is blank, then the process did not specify an applicaiton name when it connected. If your applciations allow you to specify a connection string, try including "application name=MyApplication" in the connection string.
Sign In or Register to comment.