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.

TCPIP

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.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Find TCP IP port in SQL Server 2012 by sys.dm_tcp_listener_states DMV”

Comments are closed.