0

I have inherited another SQL database (Version 15.0.4083.2) and have noted some problems with the SQL Agent. Specifically the Agent is not logging any jobs (as I just discovered trying to check logs to debug an issue). I am seeing the following errors in the agent logs:

[298] SQLServer Error: 53, Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 08001]
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
[298] SQLServer Error: 53, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
[382] Logon to server 'NotMyClient' failed (ConnLogJobHistory)
(Server name substituted)

This is saying to me that the agent is trying to attach to an incorrect servername/instance. I have then checked the agent properties, i.e. right click on agent, and under Connection it also lists 'NotMyClient'

enter image description here

However when I click on View connection properties the correct IP and computer name.

enter image description here

I have looked at several options for correcting this, but the majoity of answers seem to basically consist of wiping the database, not an option in this case. Also seen a suggestion that the instance name is incorrect, but can see no way of changing the instance name on the server agent.

Any suggestions are greatly appreciated.

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • You've inherited a new *database* or new *SQL Server instance*? Assuming service instance, what account is your SQL Agent service running as? – squillman Aug 31 '21 at 13:31
  • SQL Server Agent uses the instance name recorded as the local server. There are circumstances where this can fail, notably if the machine is renamed after installation. Check `SELECT @@servername, convert(sysname, serverproperty('MachineName'))`, these should match. – Jeroen Mostert Aug 31 '21 at 13:36
  • @squillman. I inherited a database with a single instance on it. From what I've been told it was created from an old prexisting database (different server/different client) and some how some of the old details are still in there. – Matthew Baker Aug 31 '21 at 13:41
  • @JeroenMostert Good call there. And you are right, the @@servername is incorrect. Only option I've seen is EXEC sp_dropserver ''; GO EXEC sp_addserver '', local; GO Does that drop the whole server or just the servername? – Matthew Baker Aug 31 '21 at 13:44
  • 2
    Correct; you can automate this without explicit reference to the name using `declare @servername sysname = @@servername; declare @machinename sysname = convert(sysname, serverproperty('MachineName')); exec sp_dropserver @servername; exec sp_addserver @machinename, 'local'`. Restart of SQL Server required, I think. (These commands do nothing to the service itself, they just twiddle metadata, and then only some used by SQL Server Agent, since almost nothing else uses `@@SERVERNAME`.) – Jeroen Mostert Aug 31 '21 at 13:46
  • 1
    At the risk of sounding pedantic, in SQL Server terminology you inherited a SQL Server instance with a single database. Sorry if it sounds like I'm beating a dead horse here, but we do work in a profession frequently where it matters. No disrespect. – squillman Aug 31 '21 at 13:56
  • @squillman - No problem at all, I'd never be a dba without some amount of pedance. So I have inherited a Server, one instance, and a database in there. – Matthew Baker Aug 31 '21 at 14:01
  • @JeroenMostert that has worked for the problem here. Feel free to add it as an answer and I'll sign it off so others can find it and you get the credit. – Matthew Baker Sep 01 '21 at 12:59

0 Answers0