September 7, 2016 at 12:00 pm
Hello,
I know this topic is covered all over the Internet, however, I am unable to find a post that explains the behavior that I am seeing in a manner I understand. I've read quite a bit about connection pooling and I am confused at this point so I will explin what I am seeing. Mind you that this is from a Systems perspective.
NETSTAT -AN results in 700 connections in an ESTABLISHED state from the SQL Server to itself on port 1433.
The following returns 300 less connections
SELECT
COUNT(dbid) as TotalConnections
FROM
sys.sysprocesses
WHERE
dbid > 0
I've tried many other means that people have posted for getting a connection count and the results remain the same. I have looked as sp_whoisactive to include sleeping spids, sp_who, sp_who2 and summing up the results and they line up with the SQL query above.
I've looked at process explorer and the connections tab of the properties for processes that are connected and using SQL. I see no smoking gun.
I've looked at .NET performance counters and can see some minute connection pool activity. I've turned on the ODBC counters and see no activity for connection pools.
So, I am struggling to understand why I have 300 missing connections that appear as ESTABLISHED in the netstat output.
Has anyone encountered this scenario that would be willing to share a proven troubleshooting routine that would expose what has these connections open or prove why they are open?
#EDIT# Since 10 people have looked at this and no suggestions I will pose yet another related question. As this server is a VM and I have seen what I believe is an issue with VEEAM causing access to SQL halt when the backup snapshot is removed, is there any chance that SQL wouldn't show the connections but netstat would still think that they are established? I didn't think that it was possible but I'm scratching my head on this one. In the Windows event log I see some NTFS event ID 57 messages at the same time that the sql logs I show a series of these events for each database.
I/O is frozen on database X. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
I/O was resumed on database X. No user action is required.
Database backed up. Database X, creation date.....
I assume that this can't be the case as the events all happen sub second and the backup completes. Thus I doubt that existing connections are getting orphaned if you will if there is even such a thing. Beyond this the server looks as healthy as one can get.
September 9, 2016 at 12:11 pm
cp.ap.pp (9/7/2016)
Hello,#EDIT# Since 10 people have looked at this and no suggestions I will pose yet another related question. As this server is a VM and I have seen what I believe is an issue with VEEAM causing access to SQL halt when the backup snapshot is removed, is there any chance that SQL wouldn't show the connections but netstat would still think that they are established? I didn't think that it was possible but I'm scratching my head on this one. In the Windows event log I see some NTFS event ID 57 messages at the same time that the sql logs I show a series of these events for each database.
I/O is frozen on database X. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
I/O was resumed on database X. No user action is required.
Database backed up. Database X, creation date.....
I assume that this can't be the case as the events all happen sub second and the backup completes. Thus I doubt that existing connections are getting orphaned if you will if there is even such a thing. Beyond this the server looks as healthy as one can get.
Use a network monitoring tool to track the connection down and figure out what's going on with the connections.
Any issues associated with Event ID 57 depends on the source.
The Series of messages with the I/O is totally normal for VSS backups.
Sue
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply