Hi guys in the Previous post i explained How we can get the Server Properties , returning SQL Server Agent Job Information put today and How we can return SQL Server Agent Alert Information put today in DAY 3 i will show new DMV in our track Instance Level configuration

DMV#6 Global Trace Flag Information
Very useful to know what global trace flags are currently enabled as part of the diagnostic process if not trace flag enable it will not return any thing for more information about trace flag look Here


DMV#7 Getting Information About Windows

by the below DMV we can return the major OS version, Service Pack, Edition, and language info for the operating system

SELECT windows_release, windows_service_pack_level,
windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);

DMV#8SQL Server Services Information

by the below DMV we can return very important information like this instance is clustered or No , processid , when they were last started, and their current status , the start up type of the SQL server services and SQL server agent services .

SELECT servicename, process_id, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename, [filename]
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

SQL Server Services Information
SQL Server Services Information

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Website Slideshare ,Youtube Channel

One thought on “SQL Server 2014 Health check DMV queries Day 3

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.