SQL Server 2008R2 ghost job

Hi guys, I need your help on this. I have an SQL Server that I use for reporting. Every morning at 4:45am, I have a job that loads data from my LIVE ERP system into my SQL Server. For a few months, the same jobs starts at around 11pm. I checked all my SQL Server instances and found no jobs or schedule like this. I also looked into a few system tables, but cannot find the source of this. I really need to be able to stop this job as it is taking resources and logs space for nothing. Can you please help me troubleshoot this so I can find where it is coming from ? Thanks PS : no idea of when it exactly started and what happened for this to happen BR Marc

more ▼

asked Jul 06, 2015 at 10:05 AM in Default

avatar image

11 1

I forgot to mention that the job is setup in the jobs from the sql server agent and triggers ssis packages

Jul 06, 2015 at 10:09 AM mbuthey

Is there any other third-party scheduler, apart from SQL Server Agent? Is the "ghost-activity" getting logged somewhere?

Jul 06, 2015 at 12:57 PM nidheesh.r.pillai

Hi, I don't have any 3rd party scheduler and the logs can be seen in the same SQL server instance logs than the one that is scheduled at 4:45am

Jul 06, 2015 at 05:14 PM mbuthey

When you say 'job' I assume you mean SQL Agent job? Have you tried View History on the job? That tells you how the job was started. If you still can't find anything, try renaming the job so whatever it is can't start it.

Jul 07, 2015 at 10:51 AM David Wimbush

Hi David, That's right, that's an SQL Agent job. The problem I have is that this same job is launched 2 twice while there's only one schedule that is supposed to trigger its start at 4:45am only. When looking at the history, I see 2 occurrences of this same job (same name, different times and the times shown for the steps of the "ghost" job are totally screwed up). I tried to delete the schedule in the SQL Agent job and create a new one, but when looking at the history, the "ghost" job is still launched and uses the newly made schedule. I am kind of losing my hair on this ... Thanks for your help. BR Marc

Jul 08, 2015 at 12:27 PM mbuthey
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It sounds like something other than the schedule is running the job, most likely using msdb.dbo.sp_start_job. It must be using either the job_name or job_id to start the job. If you change both of those it will stop. Here's what I would do:

  1. Script the job. (Right click, Script As Create to New Window).

  2. Save the script.

  3. Comment out the contents of the job so it doesn't do anything if called.

  4. Change the name of the job in the script (@job_name parameter of sp_add_job near the top of the script).

  5. Run the script.

  6. Test the new job.

  7. If all looks well, delete the original job.

I'd be surprised if this mystery thing can run the job after that.

I would also review security. Which accounts have permissions to run jobs? Should they have those permissions?

more ▼

answered Jul 08, 2015 at 12:53 PM

avatar image

David Wimbush
10.7k 31 34 43

Hi David, Thanks for the details. I did not launch the script to recreate the job, but I have done is manually from scratch and disabled the other one. I'll see tomorrow if it improved. I'll keep my fingers crossed and keep you updated tomorrow morning. BR Marc

Jul 08, 2015 at 03:01 PM mbuthey

Hi, Although I recreated the whole jobs with new name, new steps names, new schedule name, it is still the same behavior. My standard job did run normally, but the "ghost" (nightmare) job did run too... The log of this job says : "Message The job succeeded. The Job was invoked by Schedule 18 (DWH Load schedule). The last step to run was step 6 (Shrink Logs)." Schedule 18 is the brand new schedule I created yesterday. I am losing my hair ... link text I attached a pdf showing the job history. The one on the top is the correct one launched at 4:45am and the one on the bottom is the "ghost" one. The dates are completely out of bonds on this on. Don't understand ... Thanks BR Marc

job.pdf (62.0 kB)
Jul 09, 2015 at 06:34 AM mbuthey

Wow, that's bizarre. The 4:45 job was still running at 6:32 so the 6:32 attempt to run the job should have failed. SQL Agent always objects when I try to run a job that's already running, and I've never seen a config option about that.

But those step times in the 6:32 job are all out of synch. I see the steps are mostly running SSIS packages. I don't know much about SSIS or how SQL Agent and SSIS packages interact. But it's almost as if some of those packages are running asynchronously and the job is finishing before the packages. Then when the package finishes later, it updates the step history of the currently executing job.

Are any of these packages executing now? Is there any logging in SSIS that might help you compare the package executions with your job schedule? That might give you a clue. If not, perhaps it's worth just rebooting the server. I've had some utterly baffling errors just disappear thanks to a reboot.

Jul 09, 2015 at 07:21 AM David Wimbush

I think that the times are completely wrong for this job as I receive emails coming from SSIS when the job is finished and I received it at 2:10am when step 6 completed.

Jul 09, 2015 at 08:28 AM mbuthey
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 06, 2015 at 10:05 AM

Seen: 196 times

Last Updated: Jul 09, 2015 at 08:28 AM

Copyright 2018 Redgate Software. Privacy Policy