February 19, 2016 at 2:58 pm
We have a default instance installed and have 3 named instances which are running on different port .
Question : Is there any query for finding out how many instance( with the port # ) are running on the SQL Server ( i know i can get this by checking services.msc ) , i was thinking if we have any query ?
February 21, 2016 at 6:17 am
Hi...
I wonder if this would be helpful. I looked for system views that displayed this information, and I couldn't find any.
I did a google search and came across a few interesting hits. The script I found you could customize to loop through all of your instance names.
http://www.sanssql.com/2011/02/different-ways-to-find-sql-server-port.html
The registry setting seemed like it needed a little tweaking. The error log was most promising. If you wanted to query all of the databases using the error log, you would have to link the servers and use a script like this:
CREATE TABLE INSTANCENAMES
(
ID int IDENTITY(1,1) primary key,
INSTANCENAME varchar(255)
)
insert into INSTANCENAMES (INSTANCENAME)
values ('[MACHINENAME\SQLEXPRESS1]')
insert into INSTANCENAMES (INSTANCENAME)
values ('[MACHINENAME\SQLEXPRESS2]')
DECLARE @maxcount int = (select count(*) from INSTANCENAMES)
DECLARE @count int = 1
DECLARE @instancename varchar(255)
SET NOCOUNT ON
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8
BEGIN
Create Table ##ErrorLog_2K
(ErrorLog nvarchar(1000),
ContinuationRow int )
WHILE @count <= @maxcount
BEGIN
set @instancename = (select instancename from INSTANCENAMES where ID = @count)
set @instancename = 'Exec ' + @instancename + '.master..xp_readerrorlog'
INSERT INTO ##ErrorLog_2K
EXECUTE sp_executesql @instancename
print @instancename
SET @count = @count + 1
END
SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(ErrorLog,5),1,4) as [PortNumber]
FROM ##ErrorLog_2K where ErrorLog like '%SQL Server listening on 1%'
DROP TABLE ##ErrorLog_2K
END
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
Create Table ##ErrorLog
(Logdate datetime,
ProcessInfo nvarchar(100),
[Text] nvarchar(1000))
INSERT INTO ##ErrorLog exec master..xp_readerrorlog
SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(text,6),1,4) as [PortNumber]
FROM ##ErrorLog where text like 'Server is listening on % ''any'' %'
DROP TABLE ##ErrorLog
END
SET NOCOUNT OFF
February 21, 2016 at 6:26 am
Try something like:
SELECT DISTINCT
local_net_address
,local_tcp_port
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply