SQL Server diagnostic script for network, version, compatibility, CPU, memory, and collation settings

1. Open SQL Server Management Studio (SSMS).
2. Connect to the SQL Server instance hosting your databases.
3. Run the script below.
4. Save or export the results if requested for troubleshooting.

-- Network protocol status
SELECT 'Named Pipes' AS [Protocol], iif(value_data = 1, 'Yes', 'No') AS isEnabled
FROM sys.dm_server_registry
WHERE registry_key LIKE '%np' AND value_name = 'Enabled'
UNION
SELECT 'Shared Memory', iif(value_data = 1, 'Yes', 'No')
FROM sys.dm_server_registry
WHERE registry_key LIKE '%sm' AND value_name = 'Enabled'
UNION
SELECT 'TCP/IP', iif(value_data = 1, 'Yes', 'No')
FROM sys.dm_server_registry
WHERE registry_key LIKE '%tcp' AND value_name = 'Enabled';

-- Listening ports
USE master;
GO
xp_readerrorlog 0, 1, N'Server is listening on', N'any', NULL, NULL, N'asc';
GO

-- SQL version
SELECT @@version;

-- Database compatibility levels
SELECT name, compatibility_level FROM sys.databases;

-- CPU information
SELECT
    cpu_count AS TotalLogicalProcessors,
    hyperthread_ratio AS HyperthreadRatio,
    cpu_count / hyperthread_ratio AS PhysicalProcessors
FROM sys.dm_os_sys_info;

-- Memory usage
SELECT 
    (physical_memory_kb / 1024) AS TotalPhysicalMemoryMB,
    (committed_target_kb / 1024) AS CommittedMemoryMB,
    (committed_kb / 1024) AS SQLServerProcessCommittedMemoryMB
FROM sys.dm_os_sys_info;

-- Disk space
SELECT 
    vs.volume_mount_point AS Drive,
    vs.total_bytes / 1048576 AS TotalSizeMB,
    vs.available_bytes / 1048576 AS FreeSpaceMB
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
GROUP BY vs.volume_mount_point, vs.total_bytes, vs.available_bytes
ORDER BY vs.volume_mount_point;

-- Memory-related configuration
SELECT 
    name AS [Name],
    value AS [Value]
FROM sys.configurations
WHERE name LIKE '%memory%';

-- Collation checks (Snow databases)
SELECT 
    name AS [Database Name],
    collation_name AS [Collation],
    CASE 
        WHEN collation_name LIKE '%CS' THEN 'Case-Sensitive'
        ELSE 'Case-Insensitive'
    END AS [Case Sensitivity]
FROM sys.databases
WHERE name IN ('SnowLicenseManager', 'SnowInventory');

-- List databases and highlight Snow databases
SELECT 
    name AS [Database Name],
    CASE 
        WHEN name IN ('SnowLicenseManager', 'SnowInventory') THEN 'Snow'
        ELSE 'Other'
    END AS [Category],
    collation_name AS [Collation],
    compatibility_level AS [CompatibilityLevel]
FROM sys.databases
ORDER BY [Category], [Database Name];

Example output

Additional output

Understanding compatibility levels

The compatibility level determines which SQL Server version behavior a database follows. The value is stored per database and can differ from the SQL Server engine version.