Home > Management Views and Functions, SQL Configuration, SQLServer, SQLServer 2008 R2, SQLServer 2012 > SQL Server – Server-related dynamic management views in SQL Server 2008 SP1 and “Denali”

SQL Server – Server-related dynamic management views in SQL Server 2008 SP1 and “Denali”

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

 

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 "Denali":

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.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. Rumesh Srivastav
    December 22, 2011 at 8:51 pm

    This is one of the best articles so far I have read online. No crap, just useful information. Very well presented. Its really helpful for beginner as well as developer check out this link too its also helped me to complete my task.

    http://mindstick.com/Articles/5271ab7c-bec7-49d2-8679-e31d471580d4/?Views%20in%20SQL%20Server

    Thanks

  1. August 20, 2013 at 12:44 pm
  2. August 20, 2013 at 12:46 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: