Alert: Blocked Process not showing object

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Alert: Blocked Process not showing object

Postby Derekg » Fri Jul 13, 2012 1:36 am

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.
Derekg
 
Posts: 7
Joined: Tue Jan 27, 2009 4:27 pm

Postby Brian Donahue » Mon Jul 16, 2012 3:55 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Derekg » Mon Jul 16, 2012 4:51 pm

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.
Derekg
 
Posts: 7
Joined: Tue Jan 27, 2009 4:27 pm

Postby Brian Donahue » Wed Jul 18, 2012 4:45 pm

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 Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Mon Jul 23, 2012 2:00 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests