Solved

SQL Server 2005 job stuck in queue

Posted on 2010-11-09
15
585 Views
Last Modified: 2012-05-10
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
Comment
Question by:rmm2001
  • 7
  • 7
15 Comments
 
LVL 2

Expert Comment

by:marat-oz
ID: 34098555
If you deleted a job and it still runs as per schedule, that mean you deleted the wrong one.
0
 
LVL 7

Author Comment

by:rmm2001
ID: 34098562
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
 
LVL 2

Expert Comment

by:marat-oz
ID: 34098664
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
 
LVL 7

Author Comment

by:rmm2001
ID: 34098671
All my jobs are disabled (but no i didn't have a setup like that. Just one step per job)
0
 
LVL 2

Expert Comment

by:marat-oz
ID: 34098910
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
 
LVL 7

Author Comment

by:rmm2001
ID: 34099020
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
 
LVL 2

Expert Comment

by:marat-oz
ID: 34099071
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 7

Author Comment

by:rmm2001
ID: 34099081
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
 
LVL 2

Expert Comment

by:marat-oz
ID: 34099157
"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
 
LVL 7

Author Comment

by:rmm2001
ID: 34099162
I did that. I deleted it too
0
 
LVL 2

Expert Comment

by:marat-oz
ID: 34099170
see my first comment
0
 
LVL 7

Author Comment

by:rmm2001
ID: 34099172
I did. All jobs are disabled and the culprit one is deleted.
0
 
LVL 2

Accepted Solution

by:
marat-oz earned 500 total points
ID: 34099244
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
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34100166
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
 
LVL 7

Author Closing Comment

by:rmm2001
ID: 34128681
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with SQL - TOP 10 by date and by group 13 34
SQL Exceptions 3 39
sql Total query 2 17
Getting same value for every field in SQL 2 29
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now