SQL Server Summary Scripts

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

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s