How to find SQL server listening port

 

How to Read SQL Server Error Logs and Find TCP Port Information Using T-SQL

When troubleshooting SQL Server, error logs provide crucial insights into server events, startup messages, and potential issues. Additionally, sometimes you may want to programmatically discover the TCP port on which your SQL Server instance is listening. In this article, we'll cover how to read error logs using T-SQL and how to query the Windows registry to get the SQL Server port number.

---

Understanding the Parameters for Reading SQL Server Error Logs

The extended stored procedure xp_readerrorlog allows you to read SQL Server error logs with various options. Here’s what the parameters mean:

1. Error Log File Number:

   • 0 means the current error log file
   • 1 means Archive #1
   • 2 means Archive #2, and so on.

2. Log File Type:

   • 1 or NULL indicates the SQL Server error log
   • 2 indicates the SQL Server Agent log

3. Search String 1: The primary string you want to search for in the logs.

4. Search String 2: A secondary string to refine the search results further.

5. Start Time: The start date/time to filter the log entries.

6. End Time: The end date/time to filter the log entries.

7. Sort Order: Use N'asc' for ascending or N'desc' for descending order of results.

---

Example: Reading the Current Error Log for a Specific Message

If you want to read the current error log and search for the phrase "Server is listening on", you can run:

xp_readerrorlog 0, 1, N'Server is listening on';

This command will output all entries containing that string from the current SQL Server error log.

---

Retrieving SQL Server TCP Port from the Registry

SQL Server stores the TCP port it listens on in the Windows registry. Using xp_regread, you can retrieve this port number from within T-SQL.

Here’s a sample script that dynamically builds the registry path based on your server name and service name, reads the TCP port, and displays it:

SELECT @SERVERNAME AS ServerName, @SERVICENAME AS ServiceName;

DECLARE @value VARCHAR(20);
DECLARE @key VARCHAR(100);

IF ISNULL(CHARINDEX('\', @SERVERNAME, 0), 0) > 0
    SET @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp';
ELSE
    SET @key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP';

SELECT @key AS [RegistryKey];

EXEC master..xp_regread
   @rootkey = 'HKEY_LOCAL_MACHINE',
   @key = @key,
   @value_name = 'TcpPort',
   @value = @value OUTPUT;

SELECT 'Port Number : ' + CAST(@value AS VARCHAR(5)) AS PortNumber;

This script works by:

• Checking if the server name contains a backslash (indicating a named instance).
• Setting the registry key path accordingly.
• Reading the TcpPort value from the registry.
• Returning the port number in a readable format.

---

Conclusion

These tools allow SQL Server administrators and developers to efficiently gather important diagnostic information directly via T-SQL. Whether you're searching through error logs for critical messages or discovering configuration details like the SQL Server TCP port, leveraging xp_readerrorlog and xp_regread helps automate and streamline these tasks.

Always exercise caution when accessing the registry and ensure you have the necessary permissions and backups before making any changes.