May 6, 2010 at 4:59 am
Hi There
I got BlockProcs Alert message from Patrol Enterprise Manager.
MSSQL_Availability BlockProcs SQLDMO_1 has been blocked for 6 mins on DB Event in MSSQLSERVER. It is running on SPID 325.
Then I check the backup job status, is completed, no problem.
Did somebody can teach me about this message meaning?
I do some searching from this website, learn about table locking and blocking, is this activities is normal thing in SQL Server? if yes, why the alert message come out?
And I'm a beginner of SQL.
Thanks a lot
May 6, 2010 at 6:16 am
I'm not familiar with the BMC product you're working with, but what's going on is that you have a blocking threshold set within the product. When that threshold is exceeded, it will fire an alert. In your case, the alert fired, but by the time you got to it to look at it, the block had cleared.
It's a good idea, depending on the server, the applications, etc., to monitor blocking and respond if there has been one or more blocks held for a long period of time (that period is dependent on your system, there's no hard & fast rule). That way you can investigate and determine if the block is valid or not.
Blocking is when something is holding a lock, let's say it's an insert of a row, and other resources are waiting to access that same row. Blocking is a very normal, and desirable, part of the processes within SQL Server. But, if a lock is held too long, or a process takes too many locks, this can lead to things slowing down or even coming to a stop. In which case you need to investigate and understand why the thing was holding the lock for so long.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply