Posts Tagged ‘sql denali date function’

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

29.07.2011 1 comment

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

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

HKLMSoftware…MSSQLServerCurrentVersion   CurrentVersion       10.50.2500.0


(1 row(s) affected)

Result Set for "Denali":

registry_key                                    value_name    value_data

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

HKLMSoftware…MSSQLServerCurrentVersion    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!


SQL Server – "Denali" – End of Month using EOMONTH()

26.07.2011 3 comments

SQL Server Denali CTP3 introduces a new date/time function EOMONTH(). EOMONTH() returns the last day of the month for the given date. It also accepts an optional argument which is an integer expression specifying number of months to add/remove from specified date.


       EOMONTH( start_date [, months_to_add] )

It’s similar to Excel’s EOMONTH() function to calculate last day of the month.

It can be used as below:


       AS 'Last day of Month', — Earlier Versions


       AS 'Last day of Month',

       DATEADD(s, 1, DATEADD(day, 1, EOMONTH(GETDATE())))

       AS 'Last day of Month'

       — Time part 23:59:59

Result Set:

Last day of Month       Last day of Month       Last day of Month

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

2011-07-31 23:59:59.000 2011-07-31 00:00:00.000 2011-07-31 23:59:59.000


(1 row(s) affected)

You can use DATEADD() and EOMONTH() to calculate first day of the month as follows:


       AS 'First day of Month', — Earlier Versions

       DATEADD(day, 1, EOMONTH(GETDATE(), 1))

       AS 'First day of Month'

Result Set:

First day of Month      First day of Month

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

2011-07-01 00:00:00.000 2011-07-01 00:00:00.000


(1 row(s) affected)

Hope This Helps!