Posted by mz1313@gmail.com on 10/19/05 12:08

MOM contains its own Block Analysis script. It is a script written on
VB, that looks for the monitored servers, creates for each one
MomCreateObject("SQLDMO.SQLServer"), afterwards connects to each
database on each server and executes SELECT GETDATE() query. If MOM
doesn't receive answer for 6 minutes, it sends alert. Several times
since I started to monitor my servers I received the next alert:

The program "SQLDMO_789" has been blocked for 6 minutes on database
BurstingDataWarehouse in the SQL instance MSSQLSERVER. The defined
acceptable blocking threshold is 1 minute(s). "SQLDMO_789" is running
on SPID 134 as login NT AUTHORITY\SYSTEM and is blocked by SPID 133.
The resource id is KEY: 10:2:1 (a2007950f190)

SQLDMO_789 - is the MOM itself (number varies from time to time). 10 -
is BurstingDataWarehouse database. As far as I can judge, MOM connects
to the server succesfully (otherwise, how can it know SPID?) The server
itself worked fine at that time - nothing unusual, all the jobs
finished succesfully including the heavy ones. What can block SELECT
GETDATE() query for 6 minutes???

[Back to original message]