Hello, I am using an enterprise application on one of my servers (Server 2019) and recently migrated its database to MSSQL 2022 on another server. Initially, it was slow, but now SQL Server is repeatedly stopping right after starting. Here’s what I’ve checked so far: The SQL Server services are running under an Enterprise Admin account. TCP/IP is enabled for the specified server IP. Firewall is temporarily disabled for both the servers, so there should be no port issues. Despite this, the error logs still indicate port-related problems and won’t let me move forward. Below is the error I’m encountering: "Failed to retrieve data for the request - An exception occurred while executing a Transact-SQL statement or batch. The connection is broken, and recovery is not possible. The client driver attempted to recover the connection multiple times, but all attempts failed. Increase the ConnectRetryCount value to increase the number of recovery attempts. (Microsoft SQL Server, Error: 0) A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)" I’ve tried restarting the services and checking configuration settings, but the problem persists. Would it be possible to have a remote support session to troubleshoot this issue further? Any guidance or next steps would be greatly appreciated. Thank you!

Hi Tharun, Could you successfully connect to your SQL server instance using local SSMS? Please follow below steps to troubleshot SQL server connection issue, Make sure SQL Server Service is running, you can check this from SQL server configuration manager or windows service. If a named instance, make sure SQL Server browser service is running. Make sure the instance name is spelled correct and there is actually such an instance on your target machine. Make sure SQL Server is configured to allow remote connections if this is a remote connection. Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports. Test server connectivity with PING from the client machine. Test port connectivity using TELNET to the server and port (from step 4) from the client machine. For example, TELNET <server-name> 1433 Check firewall settings if step 5 or 6 connectivity test fails. If you have some confuse about the steps, please refer to MS document A network-related or instance-specific error occurred while establishing a connection to SQL Server to get more information. If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Persistent SQL Server Service Termination with Port Configuration Errors Despite Firewall and TCP/IP Adjustments and basic troubleshooting

Accepted answer

CathyJi-MSFT 21,871 Reputation points Microsoft Vendor

2024-09-18T07:33:57.3666667+00:00
Hi Tharun,

Could you successfully connect to your SQL server instance using local SSMS?

Please follow below steps to troubleshot SQL server connection issue,

Make sure SQL Server Service is running, you can check this from SQL server configuration manager or windows service.

If a named instance, make sure SQL Server browser service is running. Make sure the instance name is spelled correct and there is actually such an instance on your target machine.

Make sure SQL Server is configured to allow remote connections if this is a remote connection.

Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports.

Test server connectivity with PING from the client machine.

Test port connectivity using TELNET to the server and port (from step 4) from the client machine. For example,

TELNET <server-name> 1433

Check firewall settings if step 5 or 6 connectivity test fails.

If you have some confuse about the steps, please refer to MS document A network-related or instance-specific error occurred while establishing a connection to SQL Server to get more information.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.
Please sign in to rate this answer.

1 person found this answer helpful.
Tharun Reddy K 20 Reputation points

2024-09-18T07:59:32.77+00:00

Hey CathyJi-MSFT,

The SQL server service is up and running

The SQL server browser is also running

This isn't a remote connection, so no issues there.

Since firewalls are disabled on both servers, there are no port-related issues

Additionally, I am able to successfully ping the server

Thanks for your support,

We could proceed further based on your thoughts CathyJi-MSFT, Please keep me posted if you need any further details.

Regards,

Tharun

CathyJi-MSFT 21,871 Reputation points Microsoft Vendor

2024-09-18T08:02:03.3066667+00:00

Could you successfully connect to your SQL server instance using local SSMS?

Tharun Reddy K 20 Reputation points

2024-09-18T08:25:45.4166667+00:00

I've been using SSMS without any problems for a while now. But starting yesterday, every time I try to open SSMS, it launches, but immediately closes right away, without even showing the UI of the tool, Could insufficient space on the drive be causing this issue, or might there be other factors at play as well CathyJi-MSFT?

CathyJi-MSFT 21,871 Reputation points Microsoft Vendor

2024-09-18T08:40:08.6233333+00:00

Hi Tharun,

Okay, suggest creating a new thread for SSMS crash issue.

Let’s continue for SQL server connection issue on this thread. Please create a new txt file then change the file extension to udl, then test the SQL connection use udl file as below screenshot. Share test result to us.

Tharun Reddy K 20 Reputation points

2024-09-18T08:54:15.05+00:00

Hey CathyJi-MSFT,

I have executed the same now and test connection was succeeded.

And may i know what we're trying to achieve by creating a new text (udl) file and connecting to our server and the DB?

But soon after this when i tried to open SSMS, i could still not be able to access the UI of the tool, i mean couldn't get into SSMS.

CathyJi-MSFT 21,871 Reputation points Microsoft Vendor

2024-09-18T09:02:33.2866667+00:00

And may i know what we're trying to achieve by creating a new text (udl) file and connecting to our server and the DB?

If we can successfully connect to the SQL server instance by udl file. We can narrow down the issue, the issue is cause on client side not SQL server side. Did you find any useful error message from application event log? Did you check the application connection string?

CathyJi-MSFT 21,871 Reputation points Microsoft Vendor

2024-09-18T09:08:09.22+00:00

For SSMS issue, per support policy, please create a new thread to get better help. Thanks for your understanding.

Tharun Reddy K 20 Reputation points

2024-09-18T09:14:07.9333333+00:00

Hey CathyJi-MSFT,

I am attaching the logs (11:00 am to 02:15 pm) for your reference,

2024-09-18 11:57:12.28 spid84 Error: 701, Severity: 17, State: 123. 2024-09-18 11:57:12.28 spid84 There is insufficient system memory in resource pool 'default' to run this query. 2024-09-18 11:57:12.28 Server Error: 17300, Severity: 16, State: 1. 2024-09-18 11:57:12.28 Server SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes. 2024-09-18 11:57:12.28 Server Error: 17312, Severity: 16, State: 1. 2024-09-18 11:57:12.28 Server SQL Server is terminating a system or background task QDS Async Persist due to errors in starting up the task (setup state 1). 2024-09-18 12:01:02.56 Backup Database backed up. Database: Tharun_ADManagerPlus, creation date(time): 2024/08/20(19:33:36), pages dumped: 51850, first LSN: 161:2484:28, last LSN: 161:2498:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\ADMP_Backup_2024_09_18_12_00_15.bak'}). This is an informational message only. No user action is required. 2024-09-18 12:01:02.61 Backup BACKUP DATABASE successfully processed 51657 pages in 3.011 seconds (134.031 MB/sec). 2024-09-18 12:01:37.65 spid6s [DevOpsSnapshotTelemetryTask] Evaluating the background task. 2024-09-18 12:14:13.15 spid54 XE session 'telemetry_xevents' stopping. 2024-09-18 12:14:13.16 spid54 XE session 'telemetry_xevents' started. 2024-09-18 13:14:16.61 spid51 XE session 'telemetry_xevents' stopping. 2024-09-18 13:14:16.63 spid51 XE session 'telemetry_xevents' started. 2024-09-18 14:14:24.32 spid51 XE session 'telemetry_xevents' stopping. 2024-09-18 14:14:24.35 spid51 XE session 'telemetry_xevents' started.

As discussed above, is it because of the insufficient system memory in the resource pool ? Or could there be any other reasons causing the issue ?

Tharun Reddy K 20 Reputation points

2024-09-18T09:15:02.3933333+00:00

2024-09-18 11:57:12.28 spid84 Error: 701, Severity: 17, State: 123.

2024-09-18 11:57:12.28 spid84 There is insufficient system memory in resource pool 'default' to run this query.

2024-09-18 11:57:12.28 Server Error: 17300, Severity: 16, State: 1.

2024-09-18 11:57:12.28 Server SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

2024-09-18 11:57:12.28 Server Error: 17312, Severity: 16, State: 1.

2024-09-18 11:57:12.28 Server SQL Server is terminating a system or background task QDS Async Persist due to errors in starting up the task (setup state 1).

2024-09-18 12:01:02.56 Backup Database backed up. Database: Tharun_ADManagerPlus, creation date(time): 2024/08/20(19:33:36), pages dumped: 51850, first LSN: 161:2484:28, last LSN: 161:2498:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\ADMP_Backup_2024_09_18_12_00_15.bak'}). This is an informational message only. No user action is required.

2024-09-18 12:01:02.61 Backup BACKUP DATABASE successfully processed 51657 pages in 3.011 seconds (134.031 MB/sec).

2024-09-18 12:01:37.65 spid6s [DevOpsSnapshotTelemetryTask] Evaluating the background task.

2024-09-18 12:14:13.15 spid54 XE session 'telemetry_xevents' stopping.

2024-09-18 12:14:13.16 spid54 XE session 'telemetry_xevents' started.

2024-09-18 13:14:16.61 spid51 XE session 'telemetry_xevents' stopping.

2024-09-18 13:14:16.63 spid51 XE session 'telemetry_xevents' started.

2024-09-18 14:14:24.32 spid51 XE session 'telemetry_xevents' stopping.

2024-09-18 14:14:24.35 spid51 XE session 'telemetry_xevents' started.

Hope this is clear and better to go through!

CathyJi-MSFT 21,871 Reputation points Microsoft Vendor

2024-09-18T09:29:33.4+00:00

As discussed above, is it because of the insufficient system memory in the resource pool ?

it is possible, please verify that the server has enough memory.

Tharun Reddy K 20 Reputation points

2024-09-18T10:24:33.0166667+00:00

Cool CathyJi-MSFT, Thanks for your time today!

I will try to arrange enough space for the tool to work :)

And, Cathy, is there any way i could reach out to you directly in case of any query!

CathyJi-MSFT 21,871 Reputation points Microsoft Vendor

2024-09-19T03:32:19.44+00:00

Waiting for your good news.

You can @me when you create a new thread for new query.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Tharun Reddy K 20 Reputation points

2024-09-19T05:46:19.4033333+00:00

For sure @CathyJi-MSFT !

Thanks a ton :)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Persistent SQL Server Service Termination with Port Configuration Errors Despite Firewall and TCP/IP Adjustments and basic troubleshooting

0 additional answers

Your answer