Quick Server Info – sys.dm_os_sys_info and SERVERPROPERTY ()

I needed a quick query to get some server information using  sys.dm_os_sys_info and SERVERPROPERTY ( propertyname ) .  It is budget time so folks want to know some specifics about some of the current infrastructure. This is a nice way to provide a high level look at you infrastructure. Tested on SQL 2005 and SQL 2008 R2.

The SQLOS Team  has a neat article about changes in 2012 for the DMV.

SELECT  SERVERPROPERTY('ServerName') [ServerName]
,CEILING(CAST(physical_memory_in_bytes/ 1073741824.0E AS DECIMAL (10,2)))[Physical Memory]
,CASE WHEN SERVERPROPERTY('IsClustered')=1 Then 'yes' else'no' END [Cluster]
FROM Sys.dm_os_sys_info;



More OS fun for SQL 2008 R2 servers.

USE [master]
-- Registry settings
SELECT * FROM sys.dm_server_registry;
-- SQL Services information
SELECT * FROM sys.dm_server_services;
--Windows OS information
SELECT * FROM sys.dm_os_windows_info

Share the joy

Tiny URL for this post:

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress Anti Spam by WP-SpamShield