find-server-configuration-last-restart.sql

 
DECLARE @ServiceStartDateTime AS DATETIME

SELECT @ServiceStartDateTime=sqlserver_start_time  -- for last restart data 
FROM 
sys.dm_os_sys_info

SELECT 
@@SERVERNAME AS ServerName
,@ServiceStartDateTime AS SQLServerServiceLastRestartDateTime
,REPLACE (CONVERT (NVARCHAR (128), SERVERPROPERTY ('Edition')),' Edition','') AS SQLServerEdition
,SERVERPROPERTY ('ProductVersion') AS SQLServerVersion
,SERVERPROPERTY ('ProductLevel') AS SQLServerProductLevel
,SYSCONF.configuration_id
,SYSCONF.name AS PropertyName
,SYSCONF.description AS PropertyDescription
,CASE 
WHEN SYSCONF.configuration_id IN ( '1543', '1544') THEN CAST(SYSCONF.value_in_use AS decimal (18,4))/1024/1024
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.value_in_use = 1 THEN 'Y'
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.value_in_use = 0 THEN 'N'
ELSE SYSCONF.value_in_use END AS CurrentValue
,CASE 
WHEN SYSCONF.configuration_id IN ( '1543', '1544') THEN CAST(SYSCONF.minimum AS decimal (18,4))/1024/1024 
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.minimum = 1 THEN 'Y'
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.minimum = 0 THEN 'N'
ELSE SYSCONF.minimum END AS MinValueforProperty
,CASE WHEN SYSCONF.configuration_id IN ( '1543', '1544') THEN CAST(SYSCONF.maximum AS decimal (18,4))/1024/1024 
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.maximum = 1 THEN 'Y'
WHEN SYSCONF.configuration_id IN ( '117', '1581','16391') AND SYSCONF.maximum = 0 THEN 'N'
ELSE SYSCONF.maximum END AS MaxValueforProperty
FROM 
sys.configurations AS SYSCONF
WHERE 
SYSCONF.configuration_id IN
(
'117'--remote access
,'505' --network packet size (B)
,'1543' --min server memory (MB)
,'1544' --max server memory (MB)
,'1581' --optimize for ad hoc workloads
,'16391' --xp_cmdshell
);  
go