LLI Wiki — My Digital Knowledge Base

Instance, listener and availability group information queries

Collection of T-SQL queries to check AG listeners, the listening port, service accounts, server properties and availability group replicas.

Diagnostic queries for a SQL Server instance: availability group listener addresses, listening port in the error log, service accounts, server properties, availability group replicas and the number of logical processors.

Availability group listener

select * from sys.availability_group_listener_ip_addresses
SELECT * FROM sys.availability_group_listener_ip_addresses;

SELECT *
FROM sys.availability_group_listeners agl 
	inner join sys.availability_group_listener_ip_addresses aglia on agl.listener_id = aglia.listener_id

Listening port in the error log

xp_readerrorlog 0, 1, N'Server is listening on' 

Service accounts

The SQL Server Agent service account is read from the registry, then the service accounts are listed with sys.dm_server_services:

DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread
    'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
    'ObjectName', 
    @sn OUTPUT;

-- get user Find Service Account for SQL Server and SQL Server Agent?
SELECT servicename, service_account
FROM sys.dm_server_services
GO
Method 


SELECT @sn;

		SELECT  DSS.servicename,
        DSS.startup_type_desc,
        DSS.status_desc,
        DSS.last_startup_time,
        DSS.service_account,
        DSS.is_clustered,
        DSS.cluster_nodename,
        DSS.filename,
        DSS.startup_type,
        DSS.status,
        DSS.process_id
FROM    sys.dm_server_services AS DSS;

Server properties and connection

SELECT  
  	 SERVERPROPERTY('ServerName'), SERVERPROPERTY('InstanceName'),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')	  ,SERVERPROPERTY('MachineName'),
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address   


   sp_who2

   xp_logininfo 'DOMAIN\user'

Databases of availability groups

SELECT  Groups.[name] AS AGName ,
Databaselist.[database_name] AS DatabaseName
FROM    sys.availability_databases_cluster Databaselist
INNER JOIN sys.availability_groups_cluster Groups ON Databaselist.group_id = Groups.group_id
ORDER BY
AGName ,
DatabaseName;

Availability group replicas

This query shows the primary replica, the synchronous or asynchronous mode, readable secondaries and the failover mode. Without a WHERE clause it lists every group; the filters are commented out.

--Show Availability groups visible to the Server and Replica information such as Which server is the Primary
--Sync and Async modes , Readable Secondary and Failover Mode, these can all be filtered using a Where clause
--if you are running some checks, no Where clause will show you all of the information.
WITH AGStatus
AS (
	SELECT name AS AGname,
		replica_server_name,
		CASE 
			WHEN (primary_replica = replica_server_name)
				THEN 1
			ELSE ''
			END AS IsPrimaryServer,
		secondary_role_allow_connections_desc AS ReadableSecondary,
		[availability_mode] AS [Synchronous],
		failover_mode_desc
	FROM master.sys.availability_groups Groups
	INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
	INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
	)
SELECT [AGname],
	[Replica_server_name],
	[IsPrimaryServer],
	[Synchronous],
	[ReadableSecondary],
	[Failover_mode_desc]
FROM AGStatus
--WHERE
--IsPrimaryServer = 1
--AND Synchronous = 1
ORDER BY AGname ASC,
	IsPrimaryServer DESC;

Number of logical processors

SELECT cpu_count
FROM [sys].[dm_os_sys_info]

Notes

Mastodon LLIMastodon SocialFosstodon