Alle volte, sia per analizzare i vostri ambienti (un nuovo ambiente in un nuovo cliente, per esempio) oppure per sistemare e organizzare i vostri server, è necessario ottenere informazioni come per esempio il totale della RAM o la versione del Sistema Operativo o, anche, la propria versione del SQL Server. Per questo c’è uno script che può essere utile per voi:
/*INFORMAÇÕES DO AMBIENTE
SQL Server 2012 e Posterior*/
SELECT
SERVERPROPERTY('MachineName') AS 'Servidor',
SERVERPROPERTY('InstanceName') AS 'Instância',
RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ',@@VERSION)) 'Versão do S.O.',
info.virtual_machine_type_desc as 'Tipo de VM',
CASE
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL Server 2000'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL Server 2005'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL Server 2008'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL Server 2008 R2'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL Server 2012'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL Server 2014'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL Server 2016'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL Server 2017'
ELSE 'unknown'
END AS 'Versão do SQL Server',
SERVERPROPERTY('ProductLevel') AS 'Service Pack',
SERVERPROPERTY('Edition') AS 'Edição',
SERVERPROPERTY('ProductVersion') AS 'Versão do Produto',
CASE
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) LIKE '0' THEN 'NÃO'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) LIKE '1' THEN 'SIM'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) IS NULL THEN 'NULO'
ELSE 'unknown'
END AS 'Clusterizado',
CONNECTIONPROPERTY('net_transport') AS 'Net Transport',
CONNECTIONPROPERTY('protocol_type') AS 'Tipo de Protocolo',
CONNECTIONPROPERTY('auth_scheme') AS 'Autenticação',
CONNECTIONPROPERTY('local_net_address') AS 'IP Servidor',
(SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID) AS 'Porta TCP do Servidor' ,
info.cpu_count/info.hyperthread_ratio AS 'Cores',
info.cpu_count AS 'Processadores',
info.hyperthread_ratio AS Hyperthread_Ratio,
info.affinity_type_desc 'Afinidade de CPU',
info.physical_memory_kb/1024 AS 'Total RAM do Servidor (em MB)',
mem.available_physical_memory_kb/1024 AS 'RAM disponivel',
mem.system_memory_state_desc 'Status da memoria fisica',
(SELECT value FROM sys.configurations WHERE name like '%min server memory%') as 'Min de Memória da Instância (em MB)',
(SELECT value FROM sys.configurations WHERE name like '%max server memory%') as 'Max de Memória da Instância (em MB)',
mem.total_page_file_kb/1024 as 'Page File Total (em MB)',
mem.available_page_file_kb/1024 as 'Page File disponivel (em MB)',
-- info.virtual_memory_kb/1048576 as 'Memoria virtual (em GB)',
info.sqlserver_start_time 'Data do ultimo startup',
SERVERPROPERTY('InstanceDefaultDataPath') AS 'Local Datafile (Default)',
SERVERPROPERTY('InstanceDefaultLogPath') AS 'Local Logfile (Default)'
FROM sys.dm_os_sys_info info
join sys.dm_os_sys_memory mem on (mem.total_physical_memory_kb = info.physical_memory_kb)
/*INFORMAÇÕES DO AMBIENTE
SQL Server 2005 - SQL Server 2008R2*/
declare @DefaultData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData output
declare @DefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog output
declare @MasterData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output
select @MasterData=substring(@MasterData, 3, 255)
select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))
declare @MasterLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output
select @MasterLog=substring(@MasterLog, 3, 255)
select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))
SELECT
SERVERPROPERTY('MachineName') AS 'Servidor',
SERVERPROPERTY('InstanceName') AS 'Instância',
RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ',@@VERSION)) 'Versão do S.O.',
info.virtual_machine_type_desc as 'Tipo de VM',
CASE
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL Server 2000'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL Server 2005'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL Server 2008'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL Server 2008 R2'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL Server 2012'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL Server 2014'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL Server 2016'
ELSE 'unknown'
END AS 'Versão do SQL Server',
SERVERPROPERTY('ProductLevel') AS 'Service Pack',
SERVERPROPERTY('Edition') AS 'Edição',
SERVERPROPERTY('ProductVersion') AS 'Versão do Produto',
CASE
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) LIKE '0' THEN 'NÃO'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) LIKE '1' THEN 'SIM'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) IS NULL THEN 'NULO'
ELSE 'unknown'
END AS 'Clusterizado',
CONNECTIONPROPERTY('net_transport') AS 'Net Transport',
CONNECTIONPROPERTY('protocol_type') AS 'Tipo de Protocolo',
CONNECTIONPROPERTY('auth_scheme') AS 'Autenticação',
CONNECTIONPROPERTY('local_net_address') AS 'IP Servidor',
(SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID) AS 'Porta TCP do Servidor' ,
info.cpu_count/info.hyperthread_ratio AS 'Cores',
info.cpu_count AS 'Processadores',
info.hyperthread_ratio AS Hyperthread_Ratio,
info.affinity_type_desc 'Afinidade de CPU',
info.physical_memory_in_bytes/1048576 AS 'Total RAM em MB',
mem.available_physical_memory_kb/1024 AS 'RAM disponivel',
mem.system_memory_state_desc 'Status da memoria fisica',
(SELECT value FROM sys.configurations WHERE name like '%min server memory%') as 'Min de Memória da Instância (em MB)',
(SELECT value FROM sys.configurations WHERE name like '%max server memory%') as 'Max de Memória da Instância (em MB)',
mem.total_page_file_kb/1024 as 'Page File Total em MB',
mem.available_page_file_kb/1024 as 'Page File disponivel em MB',
--ROUND(info.virtual_memory_in_bytes/1099511627776,4,1) as 'Memoria virtual em GB',
info.sqlserver_start_time 'Data do ultimo startup',
(select isnull(@DefaultData, @MasterData)) as 'Local DataFile',
(select isnull(@DefaultLog, @MasterLog)) as 'Local LogFile'
FROM sys.dm_os_sys_info info
join sys.dm_os_sys_memory mem on (mem.total_physical_memory_kb = info.physical_memory_in_bytes/1024)
