I updated SQL 2017 on a development box last Friday and since then it appears no job history has been recorded, although backups etc. are definitely running. Has anyone else noticed something like this or is there something else that needs doing to fix this other than uninstall the patch? Thank you kindly.
June 14, 2021 at 7:09 pm
There are a few things I'd check.
In no particular order:
I'd try to create a new job, run it, and see if it logs the job history. It could be something related to the existing jobs. This new job would be a TSQL job that just does a "SELECT 1". No chance of job failure, it should complete ALMOST immediately, and I can run it manually then to check the job status.
I'd double check that the jobs are actually being run and that they didn't get disabled or "stuck" in a running state for some reason.
I'd check the job history from the table level via TSQL as well as in the GUI.
I'd also try it again with SSMS 17, as it could be a bug in the version of SSMS you are using. I've run into snags before where the SSMS version needed to match the SQL instance version in order for data to come back correctly to the GUI. That being said, using TSQL should get around any potential SSMS issues.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 14, 2021 at 7:35 pm
Also check the settings for the agent - the default doesn't keep a lot of data and just one job scheduled to run every few seconds can cause all other jobs history to be deleted.
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
June 14, 2021 at 7:53 pm
Thank you Brian and Jeffrey, I appreciate you giving me some hints for troubleshooting!
I queried msdb.dbo.sysjobhistory and the latest entry there is from about an hour before I installed the patch last week. There are 759 records in that table and normally we keep 30 days of history around. I created a simple test job and tried running it manually and it looks like it never finish, but no entry is made in job history and I don't see any errors in the log. I have not installed the newest SSMS, but I figured that wouldn't have anything to do with whether entries are made in the sysjobhistory table or not.
Meanwhile, the backups are still happening etc. so the agent is definitely running.
June 14, 2021 at 8:27 pm
Interesting - there was a similar issue recently where the individual 'restored' the system databases after building up the system. The method used for restoring wasn't a standard method and caused agent to appear to be running but nothing was actually running.
The only thing I can think of at this point is to try and rebuild the msdb database. Before doing that - script out all jobs, operators, etc...
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
June 14, 2021 at 9:15 pm
Just to be clear - the jobs are running just fine. My test job enters a date into a table every 5 minutes on schedule, backups are completing, etc., there just isn't a log kept. I'll try to uninstall the patch first.
June 15, 2021 at 9:36 pm
Same error here. Almost - but few jobs are running: very few of them seem to run (since there are notifications sent from them), most of them just starts: then nothing. History is gone since crit.update: so no way to tell exactly (too many of them).
Suggested "simple job" (select 1), as all others, just hangs (no matter who the owner is) - starts and shows as running, but it's not really: just an error in sqlagent error log:
2021-06-15 ******* - ! [298] SQLServer Error: 18452, Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [SQLSTATE 28000]
2021-06-15 ******* - ! [382] Logon to server '**************' failed. (ConnAttemptCachableOp) -- or (ConnLogJobHistory) -- or (ConnSetJobCompletionState)
All logins fail to execute jobs, of course they were all working just fine before the critical update: CU24. And yes server auth. is set correctly (and it has not changed). Very strange. Almost same thing happened to sql2019 a year ago after critical update.
But all above is not the real issue here: it's just the "result" - issue here was clearly "installed" with CU...
Any ideas? (rollback of CU did not do the trick: still not working)
thanks!
.
June 15, 2021 at 9:51 pm
As a thought, are your SPN's set up correctly?
I know I hit that snag YEARS back and I found a SO article about that error message where they are reminded me of the SPN's. Link:
What I am thinking here is that isn't the update that broke it, but that the SPN's were not set correctly or changed and then on server reboot or service restart, that is when the issue started.
Just a guess though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 15, 2021 at 10:50 pm
Ok, I just realized that I wasn't looking in the right spot for more info on my problem (that's how green I am - my display name really should be InvoluntaryDBA). When I looked into the agent log I found a very similar error:
SQLServer Error: 53, Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 08001]
So I'm having the same problem. Do you all think that SPNs are a likely candidate here too? Or has something changed around the SQL agent service account?
Again, thanks so much.
VICTORY!
I found a forum thread dealing with very similar set of errors. The accepted answer described updating a registry key, but another commenter mentioned that the same thing could be achieved by entering the server's hostname as alias host server in the SQL Server Agent Properties/Connection tab. I did that, restarted the Agent service and voila!
This is the link to the forum thread in case it is of help to anyone else:
Now, I'm not sure if the CU did this, or the Windows Server updates that I foolishly installed just before. That is something I definitely won't do again.
June 17, 2021 at 4:01 am
Glad you fixed it.
It was a bit different here: machine name and sql server name were not the same: and that worked just fine until this CU.
I feel for the ones that don't have an option to change the sql server name to same as servername it's runing on: eg win2019_1srv and sql21 (just an example) - and are stuck with that. This is a bug (my opinion) from MS, or some silly imposed 'security' fix: but it does give tons of headaches. Hope it helps someone: because rollback of updates did not solve the issue.
June 17, 2021 at 2:03 pm
@loaderror - I don't think that you need to have the server name match the SQL instance name as then you wouldn't be able to have multiple named instances on a single machine. To me, that sounds like it is probably an SPN issue. I'd double check that your SPN's are set up correctly.
@caracolina - That's always a fun type of problem to run across. I am glad you got it fixed and thanks for posting the link to how you fixed it AND letting everyone know that it is fixed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply