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:
/*
****************************************************************************************************************************************
Script Name: info_server_2012-ecc.sql
Client's Name: All customers
Create Date: 31/10/2018 - 16:00
Created By: Bruno Bragatto - Senior DBA (brunobragatto.com)
Objective: VERSIONE 2012 e SUCCESSIVI: Script per verificare le principali informazione a livello di Server e Istanza.
----------------------------------------------------------------------------------------------------------------------------------------
Modification Log
Date: 28/11/2023
Modified By: Bruno Bragatto
Modification Details: Traduzione Italiano e ulteriori aggiornamenti
Initial Created by: Bruno Bragatto
****************************************************************************************************************************************/
/*INFORMAÇÕES DO AMBIENTE
--Atualizado 20221003
--Correçao erro coversao int (solucao CAST(size as bigint) - 20231005
--Add Collation e HADR - 20221003
--ADD Replace Char(13) and CHAR(10) on the OS version (issue with paragraph)*/
DECLARE @Domain NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT
SELECT
Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain AS 'Server',
SERVERPROPERTY('InstanceName') AS 'Instanza',
REPLACE(REPLACE(SUBSTRING(@@VERSION,CHARINDEX('Windows',@@VERSION,0),100), CHAR(13), ''), CHAR(10), '') 'Versione S.O.',
info.virtual_machine_type_desc as 'Tipo 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'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL Server 2019'
WHEN CONVERT(VARCHAR(15), SERVERPROPERTY ('productversion')) like '16%' THEN 'SQL Server 2022'
ELSE 'unknown'
END AS 'Versione SQL Server',
SERVERPROPERTY('ProductLevel') AS 'Service Pack',
SERVERPROPERTY('Edition') AS 'Edizione',
SERVERPROPERTY('ProductVersion') AS 'Product Version',
CASE
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) LIKE '0' THEN 'NO'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) LIKE '1' THEN 'SI'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) IS NULL THEN 'NULLO'
ELSE 'unknown'
END AS 'Clusterizato',
CASE
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) LIKE '0' THEN '-'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) LIKE '1' THEN (select top 1 cluster_name from master.sys.dm_hadr_cluster)
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsClustered')) IS NULL THEN 'NULLO'
ELSE 'unknown'
END AS 'Cluster Name',
CASE
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsHadrEnabled')) LIKE '0' THEN 'NO'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsHadrEnabled')) LIKE '1' THEN 'SI'
WHEN CONVERT (VARCHAR(8), SERVERPROPERTY('IsHadrEnabled')) IS NULL THEN 'NULLO'
ELSE 'unknown'
END AS 'AlwaysOn',
SERVERPROPERTY ('Collation') as 'Collation',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'Nodo Attivo',
CONNECTIONPROPERTY('net_transport') AS 'Net Transport',
CONNECTIONPROPERTY('protocol_type') AS 'Tipo di Protocolo',
CONNECTIONPROPERTY('auth_scheme') AS 'Autenticazione',
CONNECTIONPROPERTY('local_net_address') AS 'IP server',
(SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID) AS 'Porta TCP del Server' ,
info.cpu_count/info.hyperthread_ratio AS 'Numero di Core',
info.cpu_count AS 'Processori',
info.hyperthread_ratio AS Hyperthread_Ratio,
info.affinity_type_desc 'Affinità Processori',
info.physical_memory_kb/1024 AS 'Total RAM su Server (in MB)',
mem.available_physical_memory_kb/1024 AS 'RAM disponibile',
mem.system_memory_state_desc 'Status della memoria fisica',
(SELECT value FROM sys.configurations WHERE name like '%min server memory%') as 'Min di memoria sull istanza (in MB)',
(SELECT value FROM sys.configurations WHERE name like '%max server memory%') as 'Max di memoria sull istanza (in MB)',
mem.total_page_file_kb/1024 as 'Page File Total (in MB)',
mem.available_page_file_kb/1024 as 'Page File disponibile (in MB)',
-- info.virtual_memory_kb/1048576 as 'Memoria virtual (em GB)',
info.sqlserver_start_time 'Data ultimo startup',
SERVERPROPERTY('InstanceDefaultDataPath') AS 'Local Datafile (Default)',
SERVERPROPERTY('InstanceDefaultLogPath') AS 'Local Logfile (Default)',
(SELECT CAST(ROUND(
SUM(CASE WHEN type = 0 THEN CAST (MF.size as bigint) * 8 / 1024.0 /1024 ELSE 0 END) +
SUM(CASE WHEN type = 1 THEN CAST (MF.size as bigint) * 8 / 1024.0 /1024 ELSE 0 END),2) AS DECIMAL(12,2))AS Total_GB
FROM
sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id is null) as 'Dimenzione totale Istanza (in GB)'
FROM sys.dm_os_sys_info info
join sys.dm_os_sys_memory mem on (mem.total_physical_memory_kb = info.physical_memory_kb)
Lo script sotto invece funziona soltanto per versioni di SQL Server 2005, 2008 e 2008R2.
/*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)
