During the recent Comprehensive Database Performance Health Check, I got a question from a client if there is an easy way to figure out the Network port used by SQL Server. Of course, there is a simple and easier way to figure this out.
Here is the script you can run to figure out the TCP Port used by your SQL Server.
SELECT * FROM sys.dm_tcp_listener_states WHERE type_desc = 'TSQL'
When I ran the above script on my machine, I got the following response.
It was very clear from my results that my SQL Server is running on the static standard port of 1434.
If I face any issue with the connections, I often use this simple script to figure out what port my SQL Server is using, and based on that I connect to SQL Server on that network port number.
Here are a few additional blog posts on the same subject:
- What are Ports Needed to Configure Log Shipping? – Interview Question of the Week #169
- SQL SERVER – How to Listen on Multiple TCP Ports in SQL Server?
- SQL SERVER – Unable to Start SQL Service – Server TCP provider failed to listen on [‘any’ 1433]. Tcp port is already in use.
- SQL SERVER – The server network address “TCP://SQLServer:5023” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
If you have any questions, you can always reach out to me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
