Hi, hoping someone may have an easy answer for this. We have a SQL Server 2017 database on an AWS windows server. The database was just migrated to a new server with SQL Server 2017 from a prior AWS with SQL Server 2012.
Everything else seems to be fine, but for some reason jobs will not complete and job history does not show any records.
when we run select * from msdb.dbo.sysjobhistory it returns zero records even though we have a couple of jobs scheduled to run nightly.
We created a new job and the only thing it does is insert one record into a blank table.
When we manually kick off or schedule that tiny job, the record inserts into the table, but the job never completes and the job history is blank. No sign that any part of the job ever ran.
We upgraded SQL Server 2017 to the latest release but are still seeing the issue. Any tips on what might be causing this issue or steps we can take to resolve?
Thanks so much for any help or advice!
May 21, 2021 at 8:09 pm
Hi
any errors in sqlagent error log? Jobs during migration were scripted and recreated or msdb mdf files were copied?
Any trigers on system tables in msdb database?
Any session blocked in sys.dm_exec_requests?
How is set job history on SQL Agent?
May 21, 2021 at 8:31 pm
Thanks so much for responding!
any errors in sqlagent error log? no, the error log is empty. I just kicked off our small job again and watched the error log, no new entries.
Jobs during migration were scripted and recreated or msdb mdf files were copied? The msdb mdf files were copied and restored, but the jobs were built from scratch in the new DB. No jobs were moved from the old DB.
Any trigers on system tables in msdb database? no triggers on system databases.
Any session blocked in sys.dm_exec_requests? no. the only entries in that table have status of background, running, and sleeping
How is set job history on SQL Agent? job history is set to 10000 records with 1000 per job limit
May 22, 2021 at 10:52 am
"The msdb mdf files were copied and restored, but the jobs were built from scratch in the new DB." so here is yours problem. This is not supported method for upgrade system databases even restoring them on same instance is tricky "System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2012 (11.x) SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2012 (11.x) SP1." https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server?view=sql-server-ver15
May 22, 2021 at 12:52 pm
Ugh. Ok thanks for that info. What can we do to resolve? Unfortunately that old AWS image is gone so we can’t go back to that server to update. Is there anything we can do to get this running?
What if we create a new DB called DB_new? If we migrate content over from the restored DB object by object will the new DB work?
Review this document (https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-ver15)
Try using the process to rebuild the msdb database. Before you do that, make sure you have scripted out every job so you can recreate them after the database has been rebuilt. Note that there are some jobs that should already be created on a new SQL instance - don't recreate those from your scripts. There are also some jobs that cannot be scripted - for example - maintenance plans. These need to be recreated on the new system.
You may also need to rebuild the other system databases if you copied them the same way.
Also check @@servername - I am assuming you renamed the server to the same as the old server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2021 at 9:23 pm
Thanks so much! That's really helpful, I will try to rebuild those system DBs this weekend.
Also check @@servername - I am assuming you renamed the server to the same as the old server. Actually, we haven't changed the new servername to match the old one. Is that something that we should do before or after rebuilding the system databases?
May 23, 2021 at 3:35 pm
Thanks so much! That's really helpful, I will try to rebuild those system DBs this weekend.
Also check @@servername - I am assuming you renamed the server to the same as the old server. Actually, we haven't changed the new servername to match the old one. Is that something that we should do before or after rebuilding the system databases?
If you did not rename the server - then you don't need to reset @@servername and I would not recommend making that change. It was only included as it seemed like that was what you did when you built the new system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 25, 2021 at 12:29 pm
Thanks so much for your help! That seemed to do the trick.
It was a bit scary because it broke all of the user logins so we had to rebuild security again, but well worth it to have jobs running. 🙂
Thank you!
May 25, 2021 at 6:00 pm
Glad to hear it worked out - and yeah, rebuilding master requires recreating the logins and any other configuration settings.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2021 at 10:33 am
This was removed by the editor as SPAM
May 26, 2021 at 12:52 pm
If you're facing the same sort of issues that I was describing above, it might help you to explore the solution that Paul Williams posted above (https://www.sqlservercentral.com/forums/topic/sql-server-2017-jobs-not-completing#post-3886762).
That worked for me. Just be prepared to rebuild your security and logins and re-attach your databases, and as Paul suggested, to script out your jobs before you start the process.
May 26, 2021 at 3:01 pm
This was removed by the editor as SPAM
May 26, 2021 at 5:41 pm
Paul???? :):):)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 12, 2021 at 11:01 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply