Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

SQL Server 2005 job stuck in queue

So I had this job disabled for a while and I wanted to enable it to start running things again. So I did. And it ran fine. Then I disabled because I didn't need it just yet...thing is though, it's still running.

I disabled the schedule step. Then deleted the schedule step. Then deleted the job and it's still stuck (I know because the last step in it is to send me an email with the output).

Ideas on how to get it to stop/out of the queue?

Thanks!
0
rmm2001
Asked:
rmm2001
  • 7
  • 7
1 Solution
 
marat-ozCommented:
If you deleted a job and it still runs as per schedule, that mean you deleted the wrong one.
0
 
rmm2001Author Commented:
Nope - I deleted the right one. I know because the same email that it generates keeps coming to me, it's not listed under sql server agent and isn't in job activity monitor. Unless I'm missing somewhere?
0
 
marat-ozCommented:
It is possible that you deleted a job which activates/enables another job, which is sending e-mails.
In that case even deleting the first one will not make any difference.
Check all jobs that are running around the time when e-mail being sent and narrow your search.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
rmm2001Author Commented:
All my jobs are disabled (but no i didn't have a setup like that. Just one step per job)
0
 
marat-ozCommented:
You can stop SQL Server Agent if you can and see if e-mails are still comming.
Do you have Multi Server Administration setup in your environment?
0
 
rmm2001Author Commented:
No idea what multiserver administration is (sorry). So I stopped sql agent and the emails stopped coming. Then I started it back up and they started coming back
0
 
marat-ozCommented:
If e-mails stopped when you stopped Agent means that this job is running "under" SQL Server Agent schedule.
Multiserver Administration means managing jobs from central server.

http://msdn.microsoft.com/en-us/library/ms180992(v=SQL.90).aspx
http://sqlship.wordpress.com/category/multi-server-administration/
0
 
rmm2001Author Commented:
I don't think that's set up.Any way to stop or cancel whatever SQL Server Agent is doing? The jobs I created are created directly on this server - not via another one
0
 
marat-ozCommented:
"to stop or cancel whatever SQL Server Agent is doing" Stop SQL Server Agent.
To disable a job,
http://msdn.microsoft.com/en-us/library/ms186976(v=SQL.90).aspx
0
 
rmm2001Author Commented:
I did that. I deleted it too
0
 
marat-ozCommented:
see my first comment
0
 
rmm2001Author Commented:
I did. All jobs are disabled and the culprit one is deleted.
0
 
marat-ozCommented:
1. Disable all your jobs.
2. Mark the time when you receive e-mail after disabling all your jobs
3. Run the below script to check what jobs have been running after you disabled all jobs.
4. Send me results

 Declare
        @date as int
set @date =
    datepart(year,getdate())*10000 + datepart(month,getdate())* 100 + datepart(day,getdate())  

select j.[name], h.run_status, h.run_date, h.run_time, h.run_duration
, dateadd(year, h.run_date/10000-1900, dateadd(month, h.run_date%10000/100-1, dateadd(day, h.run_date%100-1,
  dateadd(hour, h.run_time/10000, dateadd(minute, h.run_time%10000/100, dateadd(second, h.run_time%100, 0))))))
  as StartTime
,dateadd(ss, h.run_duration%100  
 ,dateadd( mi, (h.run_duration%10000)/100  
  ,dateadd(hh, h.run_duration/10000  
   ,cast(
         cast(h.run_date as char(8)) + ' '
          + right('0' + cast(h.run_time/10000 as varchar(2)), 2) + ':'
          + right('0' + cast((h.run_time%10000)/100 as varchar(2)), 2) + ':'
          + right('0' + cast(h.run_time%100 as varchar(2)), 2)
           
    as datetime)
   )
  )
) as EndTime
from msdb..sysjobs j
join msdb..sysjobhistory h
on h.job_id = j.job_id
where step_id = 0
and run_date =  @date  
order by StartTime
0
 
subhashpuniaCommented:
In the result of sp_who2 (or sp_who2 active), check the process that is run by your job (decide by the starttime, owner, database or by dbcc inputbuffer etc) and then kill that process.
0
 
rmm2001Author Commented:
This really didn't solve anything other than what job activity monitor was telling me. I ended up deleting all of my jobs. Recreating a new mail profile. Restarting sql server agent a few times and it went away. Thanks for the query though!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now