Find the Port a Connection is Using

  • MG-148046

    SSCrazy Eights

    Points: 9945

    Comments posted to this topic are about the item Find the Port a Connection is Using

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • fluppe_be

    SSC Rookie

    Points: 30

    Works great in SQL 2005!

    However, i have several sites still running on SQL 2000, the script is not working in there (i think the table does not exist yet). Do you have a similar script for SQL 2000? Would be great!

  • Chris Houghton

    SSCrazy

    Points: 2497

    It won't work in SQL 2000. The query uses Dynamic Management Views which were only introduced in SQL Server 2005.

    Excellent article that I'm sure will come in handy at some point. Thanks for sharing.

  • fluppe_be

    SSC Rookie

    Points: 30

    Thanks for you reply

    I was used to use the SP_WHO function, but this does not show the ports

    Any way around possible?

  • shyamalshukla

    Valued Member

    Points: 72

    Thanks for sharing this info.

  • MG-148046

    SSCrazy Eights

    Points: 9945

    fluppe_be,

    sp_who in SQL 2008 uses a system view in the resource database so I think you will need to write your own version of sp_who or search for one that meets your needs. There are quite a few available that use the DMVs and you would be able to easily add the port to any of them.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • Rick.Cornell.SME

    SSCrazy Eights

    Points: 8597

    I think it would if useful to also add the client_tcp_port from the connections DMV, because your SQL Server should only be using 1 open port? :w00t:

  • SQLRNNR

    SSC Guru

    Points: 281243

    Nice article. Thanks for sharing.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • pparsons

    Old Hand

    Points: 397

    Too bad these views don't exist on SQL2K

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    Nice script. Very useful information.

    Thanks.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • MG-148046

    SSCrazy Eights

    Points: 9945

    Twinsoft SME (7/7/2010)


    SQL Server should only be using 1 open port? :w00t:

    Actually - Microsoft recommends multiple ports to help balance the "listening" load on busy servers.

    From http://support.microsoft.com/kb/294453 - "To increase the performance of the SQL Server server to simultaneously process more connection requests, additional TCP ports to listen on can be added".

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • BIG1362000

    Newbie

    Points: 4

    Hi,

    I tried it on several 2005 databases. All of them returned 0 rows.

    Is it normal ?

    Thanks.

  • MG-148046

    SSCrazy Eights

    Points: 9945

    BIG1362000 (7/9/2010)


    Hi,

    I tried it on several 2005 databases. All of them returned 0 rows.

    Is it normal ?

    Thanks.

    In the script, I exclude port 1433 (WHERE c.local_tcp_port <> 1433) which is the default listener for SQL Server. You will get 0 rows in the result if you haven't got connections using a port other than the default.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • m--S3qU3L

    Hall of Fame

    Points: 3706

    Its an advantage that it shows cpu_time and memory_usage of the connected sessions.

    Its easy to narrow down few issues.

    Cheers

    John

  • BIG1362000

    Newbie

    Points: 4

    thank you.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply