Here's a handy function which shows the current status of the exec_queue:

if exists (select 1 from sys.objects where name = 'udf_queue_status')

drop function dbo.udf_queue_status

go

create function dbo.udf_queue_status()

returns table

as

return (

with cte(total, waiting, running, finished, succeeded, failed)

as

(

select (select count(*) from pmaster..exec_queue) as total

, (select count(*) from pmaster..exec_queue where worker_start_time is null and worker_end_time is null) as "waiting"

, (select count(*) from pmaster..exec_queue where worker_start_time is not null and worker_end_time is null) as "running"

, (select count(*) from pmaster..exec_queue where worker_start_time is not null and worker_end_time is not null) as "finished"

, (select count(*) from pmaster..exec_queue where return_code = 0) as "succeeded"

, (select count(*) from pmaster..exec_queue where return_code > 0) as "failed"

)

select total, waiting, running, finished, (cast(finished as real) / cast(coalesce(nullif(total,0),1) as real)) * 100 as percent_complete, succeeded, failed

from cte

)

go

EXECUTE sp_ms_marksystemobject 'udf_queue_status'

go