By Richie Lee

When I am given a new SQL Server to look after, other than using excellent tools like sp_Blitz, I like to record the details of the instance in a spreadsheet I use to document our SQL estate. Part of what I document I can get from running a few SQL queries. I have shared these below.

Glenn Berry has a diagnostics script that goes into far greater detail per server, which is also a great tool to run in order to ascertain the state of a server.


SELECT servicename, service_account FROM sys.dm_server_services

SELECT SERVERPROPERTY('MachineName') AS [MACHINE NAME], SERVERPROPERTY('ServerName') AS [SERVER NAME],
SUBSTRING(@@version, 1, CHARINDEX ( '-', @@version)-1) as [RELEASE],
SERVERPROPERTY('Edition') AS [EDITION],
SERVERPROPERTY('ProductVersion') AS [PRODUCTVERSION],
SERVERPROPERTY('ProductLevel') AS [PRODUCTLEVEL],
CASE SERVERPROPERTY ('IsClustered') WHEN 1 THEN 'YES' WHEN 0 THEN 'NO' END as [CLUSTERED],
CASE virtual_machine_type
WHEN 1 THEN 'YES'
ELSE 'NO'
END as [VIRTUAL]
from sys.dm_os_sys_info

SELECT name, recovery_model_desc, suser_sname(owner_sid) as [Owner_SID], compatibility_level FROM SYS.databases

SELECT create_date AS [INSTALL_DATE]
FROM sys.server_principals
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE'

DBCC TRACESTATUS (-1)

SELECT windows_release as WindowsRelease,
CASE windows_release
WHEN 6.3 THEN 'Windows 8.1/Windows Server 2012 R2'
WHEN 6.2 THEN 'Windows 8/Windows Server 2012'
WHEN 6.1 THEN 'Windows 7/Windows Server 2008 R2'
WHEN 6.0 THEN 'Windows Server 2008/Windows Vista'
WHEN 5.2 THEN 'Windows Server 2003/R2 Windows Server 2003/Windows XP 64-Bit Edition'
WHEN 5.1 THEN 'Windows XP'
WHEN 5.0 THEN 'Windows 2000'
END AS [Windows],
windows_service_pack_level as ServicePack
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);

SQL Server Diagnostic Information Queries for April 2014

sp_Blitz® – Free SQL Server Health Check Script