SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Mark Greenbaum / Find the Port a Connection is Using / Latest Posts

RE: Find the Port a Connection is Using

BIG1362000 — Mon, 12 Jul 2010 04:37:32 GMT

thank you.

RE: Find the Port a Connection is Using

m--S3qU3L — Fri, 09 Jul 2010 19:29:09 GMT

Its an advantage that it shows cpu_time and memory_usage of the connected sessions. Its easy to narrow down few issues.Cheers

RE: Find the Port a Connection is Using

MG-148046 — Fri, 09 Jul 2010 05:57:43 GMT

[quote][b]BIG1362000 (7/9/2010)[/b][hr]Hi,I tried it on several 2005 databases. All of them returned 0 rows.Is it normal ?Thanks.[/quote]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.

RE: Find the Port a Connection is Using

BIG1362000 — Fri, 09 Jul 2010 02:43:44 GMT

Hi,I tried it on several 2005 databases. All of them returned 0 rows.Is it normal ?Thanks.

RE: Find the Port a Connection is Using

MG-148046 — Thu, 08 Jul 2010 06:37:25 GMT

[quote][b]Twinsoft SME (7/7/2010)[/b][hr]SQL Server should only be using 1 open port? :w00t:[/quote]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".

RE: Find the Port a Connection is Using

Atif-ullah Sheikh — Thu, 08 Jul 2010 00:39:13 GMT

Nice script. Very useful information. Thanks.

RE: Find the Port a Connection is Using

pparsons — Wed, 07 Jul 2010 21:27:45 GMT

Too bad these views don't exist on SQL2K

RE: Find the Port a Connection is Using

SQLRNNR — Wed, 07 Jul 2010 14:49:28 GMT

Nice article. Thanks for sharing.

RE: Find the Port a Connection is Using

Rick.Cornell.SME — Wed, 07 Jul 2010 07:55:35 GMT

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:

RE: Find the Port a Connection is Using

MG-148046 — Wed, 07 Jul 2010 06:57:44 GMT

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.

RE: Find the Port a Connection is Using

shyamalshukla — Wed, 07 Jul 2010 04:50:18 GMT

Thanks for sharing this info.

RE: Find the Port a Connection is Using

fluppe_be — Wed, 07 Jul 2010 01:56:46 GMT

Thanks for you replyI was used to use the SP_WHO function, but this does not show the portsAny way around possible?

RE: Find the Port a Connection is Using

Chris Houghton — Wed, 07 Jul 2010 01:47:56 GMT

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.

RE: Find the Port a Connection is Using

fluppe_be — Wed, 07 Jul 2010 01:43:18 GMT

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!

Find the Port a Connection is Using

MG-148046 — Tue, 06 Jul 2010 23:35:28 GMT

Comments posted to this topic are about the item [B]Find the Port a Connection is Using[/B]