Server Information - Updated version

 Hi Folks,

This is an updated version of the script used to get the server information. This one gets additional columns

Server Name

IP Address

SQL Server Build / Version and Edition (*)

Time Zone

Max Degree of Parallelism and Hyperthread Ratio

"Backup Compression" and "Optimized for AdHoc workloads" settings.

SQL Server Start Time

Number of CPUs and amount of Memory

Cluster

Database Mirroring
Server Collation

References

Backup Compression

http://technet.microsoft.com/en-us/library/bb964719.aspx

Optimized for AdHoc workloads

http://msdn.microsoft.com/en-us/library/cc645587.aspx

(*) when new service packs or cumulative updates are available, you have to update the section "SQL Version", use http://sqlserverbuilds.blogspot.com.ar/ to the the build/version data

/*

            -- !! Make sure you can see advanced options in sp_configure

            sp_configure 'show advanced options',1

            GO

            reconfigure

            GO

     

*/

-- MAIN SCRIPT

USE master

GO

SET NOCOUNT ON

DECLARE

@date datetime,

@start int,

@ver varchar(13),

@MDPconfig_value varchar(20),

@MDPrun_value varchar(20),

@Backup_Comp varchar(20),

@Adhoc varchar(20),

@sqlstart datetime,

@Mem int,

@optimal_maxdop int,

@cpu_count int,

@hyperthread_ratio int,

@IPAdd varchar(30),

@MirrorServer varchar(50),

@VersionNr varchar (200),

@SVCsecs INT,

@SVCTimeValue varchar(50)

DECLARE @CM Table

(

      [Index] int,

      Name nvarchar(1000) NOT NULL,

      Internal_Value int,

      Character_Value nvarchar(1000)

)

DECLARE @SP_CONFIGURE Table

(

      name nvarchar(1000),

      minimun int NOT NULL,

      maximun int NOT NULL,

      config_value int NOT NULL,

      run_value int NOT NULL

)

SELECT @date = getdate()

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)

if @start = 1

      SELECT @ver = rtrim(substring(@@version,29,13))

if @start = 0

      SELECT @ver = rtrim(substring(@@version,30,9))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008',@@version)

if @start = 1

      SELECT @ver = rtrim(substring(@@version,35,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008 R2',@@version)

if @start = 1

      SELECT @ver = rtrim(substring(@@version,38,12))

IF @@version like 'Microsoft SQL Server "Denali"%'

BEGIN

      SELECT @ver = rtrim(substring(@@version,40,11))

      SELECT rtrim(substring(@@version,40,11)),@@version

END

IF @@version like 'Microsoft SQL Server 2012%'

      SELECT @ver = rtrim(substring(@@version,charindex(' - ',@@version)+3,12))

INSERT INTO @SP_CONFIGURE exec sp_configure

-- MAX DEGREE OF PARALLELISM

SELECT @MDPconfig_value=rtrim(convert(varchar(8),config_value)) ,@MDPrun_value=rtrim(convert(varchar(8),run_value))

FROM @SP_CONFIGURE

WHERE name ='max degree of parallelism'

--BACKUP COMPRESSION

SELECT @Backup_Comp=rtrim(convert(varchar(8),run_value))

FROM @SP_CONFIGURE

WHERE name ='backup compression default'

--OPTIMIZE FOR AD HOC Workload

SELECT @Adhoc =rtrim(convert(varchar(8),run_value))

FROM @SP_CONFIGURE

WHERE name ='optimize for ad hoc workloads'

--SQL Server Service Start time

SELECT @sqlstart = create_date from sys.databases where name = 'Tempdb'

SET @SVCsecs = DATEDIFF(SECOND,@sqlstart,GETDATE())

select @SVCTimeValue =

convert(varchar(10), (@SVCsecs/86400)) + ':' +

convert(varchar(10), ((@SVCsecs%86400)/3600)) + ':'+

convert(varchar(10), (((@SVCsecs%86400)%3600)/60)) + ':'+

convert(varchar(10), (((@SVCsecs%86400)%3600)%60))

-- CPU and Memory

Insert into @CM exec xp_msver

--//REPORT

select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'

select

@cpu_count=cpu_count,

@hyperthread_ratio=hyperthread_ratio,@optimal_maxdop=case

when cpu_count / hyperthread_ratio > 8 then 4

else CEILING((cpu_count / hyperthread_ratio)*.5)

end

from sys.dm_os_sys_info;

-- IP Address

SELECT TOP 1 @IPAdd = dec.local_net_address

FROM sys.dm_exec_connections AS dec

WHERE dec.session_id = @@SPID

--Database Mirroring

SELECT TOP 1 @MirrorServer=  mirroring_partner_instance from msdb.sys.database_mirroring (nolock)

where mirroring_partner_instance is not null

-- SQL Server Patches

/*

      You can use below site to update below list

      http://sqlserverbuilds.blogspot.com.ar/

*/

DECLARE @Builds TABLE ([Version] varchar(200),[Build] varchar(50))

INSERT INTO @Builds ([Version] ,[Build] )

VALUES

('SQL 2012 + Cumulative Update 1','11.0.2316.0'),

('SQL 2012 RTM','11.0.2100.60'),

('SQL 2008 R2 SP1 + Cumulative Update 5','10.50.2806.0'),

('SQL 2012 RC0','11.0.1750.32'),

('SQL 2012 CTP3 + QFE','11.0.1706.0'),

('SQL 2012 CTP3','11.0.1440.0'),

('SQL 2012 CTP1','11.0.1103.0'),

('SQL 2008 R2 SP1 + Cumulative Update 4','10.50.2796.0'),

('SQL 2008 R2 SP1 + Cumulative Update 3','10.50.2789.0'),

('SQL 2008 R2 SP1 + Cumulative Update 2','10.50.2772.0'),

('SQL 2008 R2 SP1 + Cumulative Update 1','10.50.2769.0'),

('SQL 2008 R2 + SP1','10.50.2500.0'),

('SQL 2008 R2 + SP1 CTP','10.50.2418.0'),

('SQL 2008 R2 + Cumulative Update 9','10.50.1804.0'),

('SQL 2008 R2 + Cumulative Update 8','10.50.1797.0'),

('SQL 2008 R2 + Q2494086','10.50.1790.0'),

('SQL 2008 R2 + Cumulative Update 7','10.50.1777.0'),

('SQL 2008 R2 + Cumulative Update 6','10.50.1765.0'),

('SQL 2008 R2 + Cumulative Update 5','10.50.1753.0'),

('SQL 2008 R2 + Cumulative Update 4','10.50.1746.0'),

('SQL 2008 R2 + Cumulative Update 3','10.50.1734.0'),

('SQL 2008 R2 + Cumulative Update 2','10.50.1720.0'),

('SQL 2008 R2 + Cumulative Update 1','10.50.1702.0'),

('SQL 2008 RTM','10.50.1600.1'),

('SQL 2008 RC 0','10.50.1450.3'),

('SQL 2008 + Service Pack 3','10.0.5500.0'),

('SQL 2008 SP2 + Cumulative Update 6','10.0.4321.0'),

('SQL 2008 SP2 + Cumulative Update 5','10.0.4316.0'),

('SQL 2008 SP2 + Q2494094','10.0.4311.0'),

('SQL 2008 + Cumulative Update 4 for SP2','10.0.4285.0'),

('SQL 2008 + Cumulative Update 3 for SP2','10.0.4279.0'),

('SQL 2008 + Cumulative Update 2 for SP2','10.0.4272.0'),

('SQL 2008 + Cumulative Update 1 for SP2','10.0.4266.0'),

('SQL 2008 SP2 + Q2494089','10.0.4064.0'),

('SQL 2008 + Service Pack 2','10.0.4000.0'),

('SQL 2008 + Cumulative Update 16 for SP1','10.0.2850.0'),

('SQL 2008 + Cumulative Update 15 for SP1','10.0.2847.0'),

('SQL 2008 SP1 + Q2494100','10.0.2841.0'),

('SQL 2008 + Cumulative Update 14 for SP1','10.0.2821.0'),

('SQL 2008 + Cumulative Update 13 for SP1','10.0.2816.0'),

('SQL 2008 + Cumulative Update 12 for SP1','10.0.2808.0'),

('SQL 2008 + Q2413738','10.0.2804.0'),

('SQL 2008 + Cumulative Update 10 for SP1','10.0.2799.0'),

('SQL 2008 + Cumulative Update 9 for SP1','10.0.2789.0'),

('SQL 2008 + Cumulative Update 8 for SP1','10.0.2775.0'),

('SQL 2008 + Cumulative Update 7 for SP1','10.0.2766.0'),

('SQL 2008 + Q978839','10.0.2760.0'),

('SQL 2008 SP1 + Q978791','10.0.2758.0'),

('SQL 2008 + Cumulative Update 6 for SP1','10.0.2757.0'),

('SQL 2008 + Cumulative Update 5 for SP1','10.0.2746.0'),

('SQL 2008 + Q976761','10.0.2740.0'),

('SQL 2008 + Cumulative Update 4 for SP1','10.0.2734.0'),

('SQL 2008 + Cumulative Update 3 for SP1','10.0.2723.0'),

('SQL 2008 + Cumulative Update 2 for SP1','10.0.2714.0'),

('SQL 2008 + Q970507','10.0.2712.0'),

('SQL 2008 + Cumulative Update 1 for SP1','10.0.2710.0'),

('SQL 2008 + Service Pack 1 GDR June 14, 2011','10.0.2573.0'),

('SQL 2008 + Service Pack 1','10.0.2531.0'),

('SQL 2008 + Cumulative Update 10','10.0.1835.0'),

('SQL 2008 + Cumulative Update 9','10.0.1828.0'),

('SQL 2008 + Cumulative Update 8','10.0.1823.0'),

('SQL 2008 + Q973601','10.0.1818.0'),

('SQL 2008 + Cumulative Update 6','10.0.1812.0'),

('SQL 2008 + Cumulative Update 5','10.0.1806.0'),

('SQL 2008 + Cumulative Update 4','10.0.1798.0'),

('SQL 2008 + Cumulative Update 3','10.0.1787.0'),

('SQL 2008 + Q958186 (Cumulative HF2, available byrequest.)','10.0.1779.0'),

('SQL 2008 + Q958611','10.0.1771.0'),

('SQL 2008 + Q956717','10.0.1763.0'),

('SQL 2008 + Q957387','10.0.1755.0'),

('SQL 2008 + Q956718','10.0.1750.0'),

('SQL 2008 RTM','10.0.1600.22.0'),

('SQL 2008 February CTP','10.0.1300.13'),

('SQL 2008 July CTP (requires Virtual Server 2005 R2),','10.0.1049.14'),

('SQL 2008 June CTP','10.0.1019.17')

SELECT @VersionNr = [Version] From @Builds where [Build] = @ver

---------------------------------------------------------------------------------------

--//REPORT

---------------------------------------------------------------------------------------

SELECT

@@servername as Servername

,@IPAdd as [IP Address]

,@ver as [SQL Build]

,@VersionNr as [SQL Version]

,getdate() as [Current Date/Time]

,DATEDIFF(HH,GETUTCDATE(),GETDATE()) as [Time Zone]

,cast(@MDPconfig_value as varchar(1))+' / '+ cast(@MDPrun_value as varchar(1)) +' / '+ cast(@optimal_maxdop as varchar(2))as [MDP Cfg/Run/Optimal]

,@hyperthread_ratio as [Hyperthread Ratio]

,@backup_comp as [Backup Compression]

,@Adhoc as [Opt for Ad hoc workload]

,convert(varchar(30),@sqlstart,120)+ ' / '+@SVCTimeValue as [SQL Svc Start / DD:HH:MM:SS]
,SERVERPROPERTY('collation') AS SQLServerCollation

,serverproperty('Edition') as [SQL Edition]

,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as IsCluster

,@cpu_count as [CPU Count]

,@Mem as [Memory (MB)]

,@MirrorServer as [DBMirror Server]