Find TCP IP port in SQL Server 2012 by sys.dm_tcp_listener_states DMV

Find TCP IP port in SQL Server 2012 by sys.dm_tcp_listener_states DMV

I have already explained different ways to find TCP port of SQL Server in one of my earlier article. We have already seen following different ways to find TCP port.

• XP_RegRead extended stored procedure
• From “SQL Server Configuration Manager”
• XP_ReadErrorLog extended stored procedure

These are the traditional ways which has been in use since long but SQL Server 2012 provides you one direct way by providing “sys.dm_tcp_listener_states” DMV.

Let us try executing simple SELECT statement on “sys.dm_tcp_listener_states” DMV.

[sourcecode language=”sql”]

SELECT

listener_id,

ip_address,

is_ipv4,

port,

type_desc,

state_desc,

start_time

FROM sys.dm_tcp_listener_states WITH (NOLOCK)

GO

[/sourcecode]

Here is the screen capture I have received from my laptop when I executed above SELECT statement.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.