SQL Server Agent failed to update history

Recently I have faced a very interesting issue. A good friend of mine and my batchmate Rakesh called me this morning. He told me that one of his customers has an on-premise SQL Server standard edition. His customer has scheduled a handful of jobs and it runs fine. However, when he wanted to check the job history using Job Activity Monitor he noticed that the SQL Server Agent failed to update history.

This means, he is unable to see the next run date and if the job was completed successfully or failed in the last run. At first, he thought it is an issue with the SQL Server Agent service account. He tried all the things to resolve the issue, like the following:

  • Creating a new service account
  • Setting NTFS permission for SQL Server binaries, files, and folders
  • Making SQL Server Agent account a sysadmin

However, having all these did not help him. And, finally, he asked for help. When I looked at the SQL Server Agent Logs I noticed a few messages that raise a few questions to me. The messages in SQL Server Agent Logs reads like below:

Logon to server 'EC2AMAZ-0NLP8RF' failed (ConnLogJobHistory)
SQL Server Error:53
ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

SQL Server Agent Error Logs

 

Resolving SQL Server Agent failed to update history

I have noticed that the SQL Server instance name was different than the Physical or NetBIOS name. I asked him if he has renamed the server after installation is done? He said, yes, he did. The fix was easy, here is how I resolved the issue.

-- Step 1: 
-- Execute the below statement to capture the current Server Name
SELECT @@Servername


-- Step 2: 
-- Execute below to drop the current server name
-- Replace the OldSrvName with your current Server Name
-- That's the value you have captured in previous step/statement
EXEC SP_DropServer 'OldSrvName'


-- Step 3:
-- Execute below to add a new server name. Make sure local is specified.
-- The new Server Name is your NetBIOS/Windows Server name
-- Make sure to add instance name if you have named instance

EXEC SP_AddServer 'NewSrvName', 'local'

-- Step 4:

Restart the SQL Service and SQL Server Agent Server

-- Step 5:
-- Execute below command confirm that the name is changed now

SELECT @@SERVERNAME
GO
SELECT * FROM sys.servers WHERE server_id = 0
GO