Find the Port a Connection is Using

Question

Find the Port a Connection is Using

MG-148046

SSCrazy Eights

Points: 9946
More actions
July 6, 2010 at 11:35 pm

#222903

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.

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

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

fluppe_be SSC Rookie Points: 30 More actions · Answer 1

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: 2557 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

shyamalshukla

Valued Member

Points: 72

July 7, 2010 at 4:50 am

#1189519

Thanks for sharing this info.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 5

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 More actions · Answer 6

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: 281334 More actions · Answer 7

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 More actions · Answer 8

pparsons

Old Hand

Points: 397

July 7, 2010 at 9:27 pm

#1189976

Too bad these views don't exist on SQL2K

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 9

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: 9946 More actions · Answer 10

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 More actions · Answer 11

Hi,

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

Is it normal ?

Thanks.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

BIG1362000

Newbie

Points: 4

July 12, 2010 at 4:37 am

#1191384

thank you.