0

I am trying to find active connections on multiple remote SQL Servers at once without establishing connection to them via SSMS. Is that possible? If yes, please help.

I want to see those connection information on the SSMS of my local SQL Server.

Merin Nakarmi
  • 3,148
  • 3
  • 35
  • 42
  • 1
    Well, if one instance has linked servers to the others, you can say `SELECT * FROM linkedservername.master.sys.dm_exec_connections;`... otherwise, no, you can't magically query servers you haven't connected to – Aaron Bertrand Aug 23 '18 at 00:10
  • @AaronBertrand This worked. Could you please post it as an answer so that I can accept it as an answer. Thank you! – Merin Nakarmi Aug 23 '18 at 18:29

1 Answers1

1

One way would be to create linked servers on your local instance, then you could connect there and reference all the other servers through a four-part name reference, like:

LinkedServerName.master.sys.dm_exec_connections

You could also look into Central Management Servers / registered server groups; SSMS provides some facilities for running the same batch against multiple servers in sequence.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490