question

GBSistemos-7288 avatar image
0 Votes"
GBSistemos-7288 asked ErlandSommarskog answered

SQL Server is not reachable at random

Hi all,

A little background about situation. I am using SQL Server 2019 Express installed on a Windows Server 2019. The thing is that at random times I can't reach SQL Server only from one specific server which is a Windows Server 2008R2 using SSMS tool.

But I am able to connect via RDP to this server at that time, server seems working fine from first view. From all other servers, workstations and locally I am able to connect to SQL during that not-working period.

I am connecting to server via hostname, ping resolves to IP seems good.

What I tried so far:

checked maximum connections which is unlimited
Firewall port TCP 1433 is opened
Firewall port UDP 1434 is opned
I check if backup occurring at that time and this maybe causing the problem but backup is not the case.
Since the problem occurs at random I'm not sure how to collect constant logs such as network like from Wireshark. It feels like for now Wireshark could give me a big log file to analyze so I don't want to use this.

Is there a way to identify from logs or so on why such problem occurs during time to time? Should I be worried about Windows server 2008? Or should I check something else?

sql-server-generalwindows-serverwindows-server-2019
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

falconitservicesinc avatar image
0 Votes"
falconitservicesinc answered falconitservicesinc edited

Hello,

During the time the issue is occurring, run netstat -a and check if another application is conflicting with the com ports.

Have you checked the server's logs? There should be application and system logs with some clue as to what might be happening.

Miguel Fra
https://www.falconitservices.com

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MotoX80 avatar image
0 Votes"
MotoX80 answered

You've got a number of suspects.

The server that hosts SQL (including firewall)
The SQL instance
Something in the network
The client server that is trying to connect (including firewall)
The SSMS application.

I'd suggest running a Powershell script to test connectivity to see if you can isolate the timeframe when it's not available. Run this script from the 2008R2 server, some other server, and on the server that hosts SQL.

You will have to modify the Read-SqlTableData call to actually read data.

RDP to each machine, run the script, and let it run until you get an outage. See how long it lasts. Use task manager and check cpu/memory/disk when it fails. If the script never fails, then the problem is with the SSMS application.

 $server = 'localhost'  
 $ProgressPreference = 'SilentlyContinue'
 "{0} - Monitoring initiated.." -f (get-date -Format 'yyyy-MM-dd hh:mm')
 while ($true) { 
     $smb = Test-NetConnection -ComputerName $server -CommonTCPPort SMB -ErrorAction SilentlyContinue  -WarningAction  SilentlyContinue
     if (!$smb.TcpTestSucceeded) {
         "{0} - SMB is not available." -f (get-date -Format 'yyyy-MM-dd hh:mm')
     }
    
     $sql = Test-NetConnection -ComputerName $server -Port 1433 -ErrorAction Stop -WarningAction SilentlyContinue
     if (!$sql.TcpTestSucceeded) {
         "{0} - SQL port is not available." -f (get-date -Format 'yyyy-MM-dd hh:mm')
     } 
    
    
     # Refer to https://docs.microsoft.com/en-us/powershell/module/sqlserver/read-sqltabledata?view=sqlserver-ps
     try {
         $table = Read-SqlTableData -ServerInstance "MyServer\MyInstance" -DatabaseName "MyDatabase" -SchemaName "dbo" -TableName "MyTable" -TopN 3 -erroraction stop
     } catch {
         "{0} - SQL table is not available." -f (get-date -Format 'yyyy-MM-dd hh:mm')
     }
     start-sleep -Seconds 60
 }


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

First, what exactly is the error you receive from SSMS?

Second, as this is intermittent, this is almost certainly a networking issue of some kind.

I suggest you start here:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15#testing-a-local-connection

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi GBSistemos-7288,
How are things going? Do the answers help you?
If not, please don't hesitate to let us know and share us more information such as event logs and errors received from SSMS in order to provide further help.
Please also check the troubleshooting article which might be helpful.

Best Regards,
Amelia


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GBSistemos-7288 avatar image
0 Votes"
GBSistemos-7288 answered

Hi,

Thanks for sharing thoughts. Since the problem occurs randomly I'll try to use some of your suggestions and let you know about the outcome.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered MotoX80 commented

Hello GBSistemos,

Thank you for your question and reaching out.

I can understand you are facing with connecting SQL from 2008 server.

As you have mentioned you are using SQL express and try to connect from 2008 Server which both have some limitations
A. SQL express have some limitations of connections and resources.
B. 2008 Server is legacy OS and its seems out of support.

On the other note it can be issue with networking between two server and looks connections are dropped randomly. Hence Please try to disable Firewall and Antivirus completely , as they can block connections even if ports are opened due to flooding.

You can look for SQL logs while connecting from this 2008 server.
https://docs.microsoft.com/en-us/sql/relational-databases/logs/open-log-file-viewer?view=sql-server-ver15



--If the reply is helpful, please Upvote and Accept as answer--

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thanks for your reply. SQL Logs do not help here since I don't see any logs related to networking or connection. I only see constantly repetitive logs as in picture below:
165638-image.png


Again, regarding SQL express connection limits - this shouldn't be an issue since already max allowed connection is set (where I have about 20-30 users connecting to SQL at most, I believe this shouldn't be an issue).
As I am testing this, I noticed users randomly getting this error (at the time when SQL is not reachable). A little background is that users connecting via RDP to server 2008 and then using application which connects to SQL server on different server. This 2008 server is important since it runs that application.
165628-image.png
165723-image.png
SQL server is running on default instance with default TCP port - 1433.

0 Votes 0 ·
image.png (10.7 KiB)
image.png (110.1 KiB)
image.png (101.1 KiB)

You will likely not see any errors in the SQL server log or server at all, because the clients are not connecting to the server at all.

The errors you posted are generic "could not connect to server" messages and don't help diagnose the actual problem. However, since this happens intermittently, this is almost certainly a networking issue of some kind, and not a SQL Server issue. It is possible to get that message if the SQL Server is too busy to respond before the connection timeout. But you normally would be seeing high CPU or page swapping on the SQL Server for that to be a cause.

0 Votes 0 ·
MotoX80 avatar image MotoX80 TomPhillips-1744 ·

and don't help diagnose the actual problem.

I agree. That's why I suggested the Powershell script to test connectivity from different machines, including the SQL server itself.

I'd also suggest running perfmon and monitoring whatever counters the Express version exposes.

0 Votes 0 ·
Show more comments

Is your SQL Server or the server they are RDPing into running on a VMWare VM? If so, the most common cause of this issue is VMWare snapshot removal. Snapshot removal "stuns" the server for a period of time, disconnecting and blocking all access.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

There have already been some suggestions, but there is one suspect that has not been mentioned. I discarded it at first, since you said it only happened from one machine, but now you say that the application runs on this machine, so I guess most connections attempts are from this machine.

Anyway, what numbers do you get when you run this query:

SELECT *FROM sys.dm_os_wait_stats WHERE wait_type = 'THREADPOOL'

Also, how long has the instance been up since it was last restarted?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.