January 3, 2008 at 8:30 am
Just prior to a server becoming unresponsive we received a number of emails from Microsoft Operations Manager (MOM) regarding blocking. The first message reported pertains to SQLAgent - Job Manager. The additional messages pertained to tempdb. I am unable to determine what blocked Job Manager, but the other messages indicate the tempdb resources blocked themselves. Any idea what this means? I cannot find any information on SQLAgent - Job Manager for SQL 2005.
Alert Description : The program "SQLAgent - Job Manager" has been blocked for 4.00441666666667 minutes on database msdb in the SQL instance DEV_LDSQL01. The defined acceptable blocking threshold is 1 minute(s). "SQLAgent - Job Manager" is running on SPID 59 as login BLAIRNET\sqlsaservice and is blocked by SPID 67. The resource id is TAB: 4:117575457:0 .
(Looking at the TAB information I found that 4 is MSDB and 117575457 is sysjobhistory)
Alert Description : The program "SQLAgent - TSQL JobStep (Job 0x786C1D46275FA947AE6C0AC49A1F59E3 : Step 1)" has been blocked for 3.98306666666667 minutes on database tempdb in the SQL instance DEV_LDSQL01. The defined acceptable blocking threshold is 1 minute(s). "SQLAgent - TSQL JobStep (Job 0x786C1D46275FA947AE6C0AC49A1F59E3 : Step 1)" is running on SPID 56 as login and is blocked by SPID 56. The resource id is DBCC_OBJECT_METADATA (538BCEEC) .
Alert Description : The program "SQLAgent - TSQL JobStep (Job 0x786C1D46275FA947AE6C0AC49A1F59E3 : Step 1)" has been blocked for 3.98333333333333 minutes on database tempdb in the SQL instance DEV_LDSQL01. The defined acceptable blocking threshold is 1 minute(s). "SQLAgent - TSQL JobStep (Job 0x786C1D46275FA947AE6C0AC49A1F59E3 : Step 1)" is running on SPID 56 as login and is blocked by SPID 56. The resource id is DBCC_OBJECT_METADATA (538BCEEC) .
Thanks, Dave
January 3, 2008 at 3:17 pm
a certain amount of blocking usually occurs on a server from time to time and it is a matter of management and such which dictates what is acceptable. You're looking at a threshold which may or may not be relevant at that time. for example data loads and database maintenance may cause blocking . The other cuplprit is rubbish code within dts/ssis packages - I've seen some real classic where developers try to parallel thread tasks and end up trying to delete/update/insert into the same table simultaneously!
The other possibility is paralleism - this can cause blocking of a process by its self.
What you need to decide is if you have a problem, or you think you have a problem becuase MOM says so - there is a difference.
You need to profile/trace etc. to find out what's happening. Tempdb may be used by checkdb, index rebuilds, select into, data loads, temp tables etc. etc.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 4, 2008 at 8:38 am
I'm more concerned over the function of SQL Agent - Job Manager, since that was the first resource blocked and within a few seconds of the blocking our server became unresponsive and required a reboot. Do you have any information on SQL Agent - Job Manager?
Thanks, Dave
January 5, 2008 at 3:35 am
It's one of those situations you need to view to be able to make a more informed suggestion. Your tempdb spid is blocking itself, that suggests possible parallelism issues.
How big is msdb and do you run maint jobs - the history tables can get very large, I usually tidy them up and remove data from them. I normally try to have all jobs owned by sa, found jobs with different owners can sometimnes be problematic.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply