SQL Server 2012 Job Seemingly not running at all, manual or scheduled, no logs

0

1

We recently installed a side-by-side instance of SQL Server 2017 on our DEV environment, and almost all of our DBs and jobs have migrated over. However due to compatibility issue, we still have a couple of jobs that need to be run on the 2012 instance. It appears they have not been succeeding in some time, however.

When I run them manually, either right-click on the job, or through job activity monitor, or via T-SQL, it "starts" normally but there's no actual activity. It doesn't show as running in the activity monitor, the tables don't get affected at all, nothing shows in the history for the job. I've tried a few different jobs and they all exhibit this "phantom" behavior.

I've checked the system table for SQL Agent Job history and it also does not show any new activity since over a month ago (roughly when the migration happened). I've looked at the various logs in MSSQL11.MSSQLSERVER\MSSQL\Log directory and they show nothing at all when I'm trying to run these jobs.

I've tried changing what account the SQL Agent runs under to no affect, the entire server restarts every week but I've also restarted the components of it myself and while that shows normally in the above log file, there's nothing I can do that seems to generate real activity in the logs or jobs.

As far as I recall from the installation, none of the directories for the 2012 instance were modified at all, we simply detached and reattached the user DBs to 2017. The 2012 temp db, installation location, log files, system DBs, etc. all were unchanged during the process.

I have not been able to find anything about this in my searches all day, and I'm pretty much out of ideas. I could try to uninstall / reinstall the 2012 instance but I'd be worried about disrupting the 2017 instance even though it should be fine, as theoretically the 2012 instance should have been fine. Any suggestions would be greatly appreciated.

Andrew83

Posted 2019-02-15T23:42:20.307

Reputation: 1

You detached msdb data and log files from the 2012 instance and attached to 2017 - did I understand the migration correctly? – billinkc – 2019-02-16T01:15:50.327

@billinkc No, I did not touch any of the system DBs, including msdb. Only user DBs were moved off of the 2012 instance. Both 2012 and 2017 have their own msdb on their own drives. – Andrew83 – 2019-02-17T22:57:24.780

The only additional information I can find is that according to the query used to query the msdb tables, the jobs are running, however the SQL command to stop the jobs fails as it says no job is running, and the jobs do not show running in activity monitor, AND I can tell it to start the job even when it says it is running and it will still "start". I tested with a fresh SSIS package and new SQL Agent Job and it exhibits same behavior so definitely some kind of issue with the server / job agent, not the packages. Only log file updates are normal server start / stop messages, nothing job relate – Andrew83 – 2019-02-20T22:33:58.820

No answers