June 26, 2024 at 8:00 am
I have SQL Server Standard Edition 2022 running on Linux
The IT department linked this server to the DNS over the weekend - after that I have started experiencing the following:
Some SQL Agent jobs on the server will fail intermittently with the error:
Unable to connect to SQL Server '(local),1433'. The step failed.
The code in the job is not trying to access a remote server. It is accessing system databases and system tables on the local server itself.
Without any intervention, the job will succeed on its next run, then randomly fail again later, with the same error.
It is not affecting all jobs - some affected jobs are running stored procs, others are running code in the job itself.
The effected jobs are not different from other non-affected jobs, so I am stumped.
Any idea what this could be? Or where to start troubleshooting
June 27, 2024 at 8:08 am
whats in the sql log
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 27, 2024 at 9:54 am
Agent log has the following 4 entries at the time of failure:
[298] SQLServer Error: 26, Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server. [SQLSTATE 08001]
[298] SQLServer Error: 26, Client unable to establish connection [SQLSTATE 08001]
[298] SQLServer Error: 10054, Client unable to establish connection due to prelogin failure [SQLSTATE 08001]
[210] Unable to send job completion notification email to operator '***' for job '********'
It cannot be "server too busy" as there is currently very limited activity on the pre-prod server.
"max server memory (MB)" assignment is set to 18 GB (24 GB total on server)
June 27, 2024 at 11:09 am
Have you tried connecting to the IP address instead of localhost or the server name?
June 27, 2024 at 11:34 am
Agent log has the following 4 entries at the time of failure:
[298] SQLServer Error: 26, Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server. [SQLSTATE 08001]
[298] SQLServer Error: 26, Client unable to establish connection [SQLSTATE 08001]
[298] SQLServer Error: 10054, Client unable to establish connection due to prelogin failure [SQLSTATE 08001]
[210] Unable to send job completion notification email to operator '***' for job '********'
It cannot be "server too busy" as there is currently very limited activity on the pre-prod server.
"max server memory (MB)" assignment is set to 18 GB (24 GB total on server)
whats in sql server error log
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 27, 2024 at 11:41 am
There is nothing in the sql error log around the time of failure
The log is being flooded with this (but not sure if it is related)
sp_server_diangostics encountered a long delay that was 2.5 times more than the repeat interval between two consecutive diagnostics result generations. The repeat interval: 300000000, the elapsed time of this diagnostics result generation: 235415, the elapsed time between the latest two diagnostics result generations: 3804424563.
June 27, 2024 at 11:42 am
I am not making the connection - it is code running locally in a SQL agent job - and the code is reading from system tables and/or DMV's
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply