I have the following query that gives me one line for each sql server instance installed in the current server:
--list the instances in the current server
--marcello miorelli
--16-sep-2016
DECLARE @GetInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))
Insert into @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'
--Select InstanceNames from @GetInstances
Select *
from @GetInstances
I have a server with 2 instances, when I run the above query there, I get the following result:
Now for EACH
of those instances – or for a single instance, be it named or not, I would like to know at hand, information about the sql server services account similar to what is returned from the queries below:
select * from
sys.dm_server_services
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;
how can I achieve that?
Best Answer
DMV is specific to a particular instance, so no way to get info for all instances of sql server running on a given machine.
2 ways to do it -
sys.dm_server_services
and/orsys.dm_server_registry
depending on your needs. You can possibly join both the dmvs using the ImagePathFind-DbaInstance
and then pipe the discovered instances to any other service related commands for additional info. I have used Boe Prox'sGet-SQLInstance
to discover sql server instances on a given machine.