MSSQL Script for Retrieve SQL instance connect information

Description:

This script provides a quick and easy way to get some useful connection information from your SQL Server instances.  It is very helpful if you have a SQL cluster running multiple instances and want to quickly find out what node the instance is running on or the IP address or port for each of your instances.

Script:

SELECT SERVERPROPERTY(‘ServerName’) as ‘Server\Instance’,
            SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as ‘Node’,
            (select left(af.filename,3)from sys.sysaltfiles af where af.name like ‘%MSDB%’ and af.fileid = 1) as ‘Drive’,
            ec.local_net_address as ‘IP Addr’,
            ec.local_tcp_port as ‘SQL Port’
from sys.dm_exec_connections ec
where ec.session_id = @@SPID

For an example, consider following screenshot:

MSSQL Script for Retrieve SQL instance connect information


<<Click here to see all posts>>

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.