March 6, 2016 at 11:48 pm
Hi All,
We have native maintenance job.
It has the steps dbcc check db and rebuild all indexes on one of the user db. It is scheduled to run every week.
I noticed this is running from last 3 days. There is some blocking (One select statement blocked the maintenance job session). When I run the below query I didn't see any last_executed_step_id. But the blocked statement is Alter Index. So I right click and stop the job. It cancelled immediately. When I run the same query again after stopping the job now I can see last_executed_step_id=1 which means dbcc check db completed.
After 2 days when I run this same query again, I didn't see the cancelled job date.
How can we run maintenance plans without blocking. SQL Server standard edition
select a.name, b.run_requested_date,b.start_execution_date,b.last_executed_step_id
from msdb.dbo.sysjobactivity b
join msdb.dbo.sysjobs a
on a.job_id=b.job_id
where b.job_id='@job_id'
and b.run_requested_date is not null
March 8, 2016 at 4:00 pm
From the sp_who2
the status of the select statement is in suspended.
Is suspend & sleeping spids also causes the blocking?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply