SQL DBA Cheatcode Part 6 : Know your SQL Server


Hello Readers,

I am here adding few more feathers to our SQL DBA Cheatcode series hat. If you have not read our others blogs of this series, you may do it by clicking the links below or you may visit here again.

SQL DBA Cheatcode Part 5 : Playing with indexes

SQL DBA Cheatcode Part 4 : Few Important Queries for any DBA

SQL DBA Cheatcode Part 3 : Few Important Queries for any DBA

SQL DBA Cheatcode Part 2 : Few Important Queries for any DBA

SQL DBA Cheatcode Part 1 : Few Important Queries for any DBA


Here are six scripts which are very handy and useful when you are looking for some information about your SQL Server. I have tested these scripts on 2008, 2008 R2 and 2014 and they work well. However, I would say, try them at your end too.

Script 1 : SQL Server configuration

SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName],SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered],SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel],SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID],SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus];






Script 2 : SQL Server OS information

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






Script 3 : SQL Server OS Services Information for a SQL instance

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);






Script 4 : Hardware manufacture’s information

EXEC xp_readerrorlog 0,1,"Manufacturer";





Script 5 : Processor’s manufacture’s information

EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0','ProcessorNameString';





Script 6 : SQL Server installation time.

SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date]FROM sys.syslogins WHERE [sid] = 0x010100000000000512000000;






I hope this helps..


Sachin Diwakar

Sharing creates magic!!

Facebook Twitter LinkedIn 

  1. Series : SQL DBA Cheatcode - DBA THINGS June 5, 2015 at 6:44 pm

    […] Know your SQL Server – SQL Server Related […]

%d bloggers like this: