Identifying SQL Server Port Number

This article provides several ways to identify the TCP/IP port number used by SQL Server.

Find the Running Port Number through Query

The following query provides a direct answer about TCP/IP port used by the SQL Server, as well as the SQL Server’s IP address.

SELECT DISTINCT local_net_address, local_tcp_port 
FROM sys.dm_exec_connections 
WHERE local_net_address IS NOT NULL;

Find the Running Port Number through xp_readerrorlog

The command xp_readerrorlog read the SQL Server logs, which consists of SQL Server boot log, to find the SQL Server listening port through keywords in the boot log.

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

Get the Running Port from the TCP/IP Properties

  1. Open the SQL Server Configuration Manager
  2. In the SQL Server Configuration Manager, under the SQL Server Network Configuration, select your SQL Server instance.
  3. After that, right click on TCP/IP and select Properties. You will find the SQL Server running port on the following screen.

 

How to: Find the Last Modified Date of The Database

This one will list all online databases on the server and the last modified date

DECLARE @sqlString NVARCHAR(max)
DECLARE @union NVARCHAR(max)
SET @sqlString = ''
SET @union = ''
DECLARE @name nvarchar(50);

DECLARE crs CURSOR FOR 
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union 
   SET @sqlString = @sqlString + '
    SELECT 
    TOP 1
  ''' + @name + ''' as DBName, modify_date
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END 

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'

CLOSE crs;
DEALLOCATE crs;

EXEC(@sqlString)