Microsoft SQL Server: Server-related dynamic management views in SQL Server 2008 SP1 , “2012" and Above

There are three new server-related DMVs introduced in SQL Server 2008 R2 Service Pack 1 and SQL Server "2012".

1. sys.dm_server_services:

- For SQL Server 2008 R2 SP1, it contains information about SQL Server and SQL Server Agent services for the current instance.

- For "Denali", it returns information about SQL Server, Full-Text Search and SQL Server Agent services for the current instance. Let’s use below query on both:

SELECT servicename          AS [Name],

       startup_type_desc    AS [Type],

       status_desc          AS [Status],

       process_id           AS [ProcessID],

       service_account      AS [Startup Account]

FROM   sys.dm_server_services

Result Set for SQL Server 2008 R2 SP1:

Name                           Type       Status         ProcessID   Startup Account

————————–     ——-    ——         ———   —————

SQL Server (MSSQLSERVER)       Automatic  Running        2492        .\sqladmin

SQL Server Agent (MSSQLSERVER) Manual     Stopped        NULL        .\sqladmin

(2 row(s) affected)

Result Set for "Denali":

Name                       Type    Status   ProcessID  Startup Account

————————– ——  ——   ———  —————

SQL Server (DENALI)        Manual  Running  5176       .\sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .\sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .\sqladmin

(3 row(s) affected)

Ah!, for "Denali" it’s returning two entries for SQL Server Agent, and not returning the Full-Text Search service, (it is installed), a possible "Denali" bug (Anyone else got the same issue?).

2. sys.dm_server_registry:

- For both SQL Server 2008 R2 SP1 and "Denali" – this view returns configuration and installation information, such as host machine or network configuration for the current instance, i.e. information we used to fetch using xp_instance_regred for an instance, Let’s execute below query on both:

SELECT registry_key, value_name, value_data

FROM   sys.dm_server_registry

WHERE  value_name = N'CurrentVersion'

Result Set for SQL Server 2008 R2 SP1:

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLM\Software\…\MSSQLServer\CurrentVersion   CurrentVersion       10.50.2500.0

(1 row(s) affected)

Result Set for "2012":

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLM\Software\…\MSSQLServer\CurrentVersion    CurrentVersion       11.0.1440.19

(1 row(s) affected)

3. sys.dm_server_memory_dumps:

This view returns a list of memory dump files generated by the SQL Server Database Engine. It returns below information:

i) filename – File name and physical path of the memory dump file,

ii) creation_time – date and time the memory dump file creation,

iii) size_in_bytes – memory dump file size in bytes.

Microsoft SQL Server: Accessing Registry using XPs 

While xp_regread read values from registry under exact path specified. To read instance specific registry entries from registry you can use xp_instance_regenumvalues andxp_instance_regread.

xp_instance_regread translates the given path to instance-specific path in the registry:

for example, executing following code against a SQL 2008 Instance returns the default database location which is specific to that instance:

DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread

       @rootkey      = N'HKEY_LOCAL_MACHINE',

       @key          = N'SOFTWARE\Microsoft\MSSQLServer\Setup',

       @value_name   = N'SQLDataRoot',

       @value        = @returnValue output

PRINT @returnValue

[EDIT: Above path returns the default installation data directory, to get default data directory use path: SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer]

this returns:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

Now, if I execute the same code against a SQL 2005 Instance on my system, it returns:

C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL

If the key path cannot be translated to instance-specific path, it will read from the specified key path.

Microsoft SQL Server: Accessing Registry using XPs 

You can use the undocumented extended stored procedure xp_regread to access registry entries using T-SQL.

Syntax:

xp_regread    @rootkey      = N'rootkey',
              @key          = N'key',
              @value_name   = N'value_name',
              @value        = @outputValue OUTPUT

For example, below code returns "C:\Program Files" on my system :

DECLARE @returnValue NVARCHAR(100)
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\\Microsoft\Windows\\CurrentVersion',
       @value_name   = N'ProgramFilesDir', 
       @value        = @returnValue output
SELECT @returnValue

xp_regread ca only be used to retrieve a single value. If you need to retrieve multiple values, you will need to use xp_instance_regenumvalues, which returns all values under a specified key.

For example, To retrieve a list of start-up programs from registry we will use:

EXEC   master..xp_instance_regenumvalues
       @rootkey = N'HKEY_LOCAL_MACHINE',
       @key     = N'SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run'

this will return all entries under HKLM\Software\Microsoft\Windows\CurrentVersion\Run:

  

Microsoft SQL Server: Get Current SQL Server Services using T-SQL(single Inst)

Microsoft SQL Server:SQL Script to get SQL Services for all local instances(Multiple Inst)

SQL Server service account information is stored in Windows Registry database. You can get this information from Services Console or SQL Server Configuration Manager.

To get account information from Services Console:

1. Go to Start > Run > Services.msc

2. Right Click on SQL Server Service, i.e. “SQL Server (InstanceName)” and go to properties

3. The account information is available under Log On tab:

We can also get this information using T-SQL, we can use extended system stored procedurexp_instance_regread to read Windows Registry. Use below code to get information for SQL Server and SQL Server Agent Services:

DECLARE       @DBEngineLogin       VARCHAR(100)

DECLARE       @AgentLogin          VARCHAR(100)

EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N’HKEY_LOCAL_MACHINE’,

              @key          = N’SYSTEM\CurrentControlSet\Services\MSSQLServer’,

              @value_name   = N’ObjectName’,

              @value        = @DBEngineLogin OUTPUT

EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N’HKEY_LOCAL_MACHINE’,

              @key          = N’SYSTEM\CurrentControlSet\Services\SQLServerAgent’,

              @value_name   = N’ObjectName’,

              @value        = @AgentLogin OUTPUT

SELECT        [DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin

GO

Result Set:

DBEngineLogin        AgentLogin

.\Administrator      NT AUTHORITY\NETWORKSERVICE

(1 row(s) affected)

We can use same registry for default and named instances as xp_instance_regread returns instance specific registry.

With SQL Server 2008 R2 SP1 and above a new server related DMV sys.dm_server_servicesis available which returns information of all instance services. This view also returns additional information about each of services such as startup type, status, current process id, physical executable name. We can also query service account name using this DMV:

SELECT servicename, service_account

FROM   sys.dm_server_services

GO

Result Set:

servicename                                      service_account

SQL Server (SQL2012)                            .\Administrator

SQL Server Agent (SQL2012)                      NT AUTHORITY\NETWORKSERVICE

SQL Full-text Filter Daemon Launcher (SQL2012)  NT AUTHORITY\LOCALSERVICE

(3 row(s) affected)

Microsoft SQL Server:SQL Script to get SQL Services for all local instances

Microsoft SQL Server: Get Current SQL Server Services using T-SQL(single Inst) 

That works on a single instance.
If you multiple instances of SQL Server installed on a server you can use below script to get SQL Server and SQL Server Agent service account information for all local instances:

Get multiple instances of SQL Server installed on a server and their services:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

-- Script to get Service account details for SQL Server and SQL Server Agent -- Create Temp Table to store instance names CREATE TABLE #tempInstanceNames (       InstanceName  NVARCHAR(100),       RegPath       NVARCHAR(100),       LoginName     NVARCHAR(100) ) -- Get instance names from Windows registry INSERT INTO #tempInstanceNames (InstanceName, RegPath) EXEC   master..xp_instance_regenumvalues @rootkey = N'HKEY_LOCAL_MACHINE', @key     = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL' -- Get instance names from Windows registry INSERT INTO #tempInstanceNames (InstanceName, RegPath) EXEC   master..xp_instance_regenumvalues @rootkey = N'HKEY_LOCAL_MACHINE', @key     = N'SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL' -- Update instance names to service path in Windows registry INSERT INTO #tempInstanceNames (InstanceName, RegPath) SELECT 'MSSQL$' + InstanceName, RegPath FROM #tempInstanceNames WHERE InstanceName <> 'MSSQLSERVER' -- get SQL Server Agent path for default instance INSERT INTO #tempInstanceNames (InstanceName, RegPath) SELECT 'SQLServerAgent', RegPath FROM #tempInstanceNames WHERE InstanceName = 'MSSQLSERVER' -- update instance names for SQL Server Agent UPDATE #tempInstanceNames SET InstanceName = 'SQLAgent$' + InstanceName WHERE InstanceName <> 'MSSQLSERVER' AND InstanceName <> 'SQLServerAgent' AND InstanceName NOT LIKE '%$%' -- get account information DECLARE @SQL VARCHAR(MAX) SET @SQL = 'DECLARE @returnValue NVARCHAR(100)' SELECT @SQL = @SQL + CHAR(13) + 'EXEC master.dbo.xp_regread @rootkey = N''HKEY_LOCAL_MACHINE'', @key = N''SYSTEM\CurrentControlSet\Services\' + InstanceName + ''', @value_name = N''ObjectName'', @value = @returnValue OUTPUT; UPDATE #tempInstanceNames SET LoginName = @returnValue WHERE InstanceName = ''' + InstanceName + '''' + CHAR(13) FROM #tempInstanceNames EXEC (@SQL) -- display information SELECT InstanceName, RegPath, LoginName FROM #tempInstanceNames ORDER BY RegPath, InstanceName -- drop temporary table DROP TABLE #tempInstanceNames -- Script End

The above script will return results in following format:

Result Set:

InstanceName                RegPath                     LoginName

MSSQLSERVER                MSSQL.1                      LocalSystem

SQLServerAgent             MSSQL.1                      LocalSystem

MSSQL$SQL2005DEV           MSSQL.2                      LocalSystem

SQLAgent$SQL2005DEV        MSSQL.2                      LocalSystem

MSSQL$SQLSERVER_2008       MSSQL10_50.SQLSERVER_2008    .\sinhas

SQLAgent$SQLSERVER_2008    MSSQL10_50.SQLSERVER_2008    .\sinhas

MSSQL$SQL2012              MSSQL11.SQL2012              .\Administrator

SQLAgent$SQL2012           MSSQL11.SQL2012              NT AUTHORITY\NETWORKSERVICE

(8 row(s) affected)