SQL Server information, OS details in a script
SQL Server info, OS details, databases, and more.
Easy to Use: Just run it in SSMS and read the output.
See server configuration, backup status, and disk space at a glance.
-- SQL Server Report
-- Print Server Information
PRINT 'SQL Server Extended Report'
PRINT '=========================='
PRINT 'Server Name: ' + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
PRINT 'Product Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))
PRINT 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128))
PRINT 'Machine Name: ' + CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128))
PRINT 'Is Clustered: ' + CAST(SERVERPROPERTY('IsClustered') AS NVARCHAR(10))
PRINT '---------------------------------'
-- Print OS Information
PRINT 'Operating System Details:'
PRINT 'OS Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))
PRINT '---------------------------------'
-- Print Database Information
PRINT 'Databases:'
DECLARE @DbInfo NVARCHAR(MAX) = ''
SELECT @DbInfo += ' - ' + name + ' (State: ' + state_desc + ', Recovery Model: ' + recovery_model_desc + ')' + CHAR(13)
FROM sys.databases
PRINT @DbInfo
-- Print SQL Server Configuration
PRINT 'SQL Server Configuration:'
DECLARE @ConfigInfo NVARCHAR(MAX) = ''
SELECT @ConfigInfo += ' - ' + name + ': ' + CAST(value AS NVARCHAR(256)) + CHAR(13)
FROM sys.configurations
WHERE is_dynamic = 1
PRINT @ConfigInfo
-- Print Backup Information
PRINT 'Recent Backups (Last 30 Days):'
DECLARE @BackupInfo NVARCHAR(MAX) = ''
SELECT @BackupInfo += ' - Database: ' + database_name + ', Type: ' + type + ', Backup Start: ' + CAST(backup_start_date AS NVARCHAR(50)) + ', Backup Size (MB): ' + CAST(backup_size / 1048576 AS NVARCHAR(50)) + CHAR(13)
FROM msdb.dbo.backupset
WHERE backup_start_date > DATEADD(day, -30, GETDATE())
PRINT @BackupInfo
-- Print Disk Space Information (Limited)
PRINT 'Available Disk Space:'
IF OBJECT_ID('tempdb..#DiskSpace') IS NOT NULL DROP TABLE #DiskSpace
CREATE TABLE #DiskSpace (Drive CHAR(1), FreeSpace INT)
INSERT INTO #DiskSpace EXEC xp_fixeddrives
DECLARE @DiskSpaceInfo NVARCHAR(MAX) = ''
SELECT @DiskSpaceInfo += ' - Drive ' + Drive + ': ' + CAST(FreeSpace AS NVARCHAR(20)) + ' MB free' + CHAR(13)
FROM #DiskSpace
PRINT @DiskSpaceInfo
-- End of Report
PRINT 'End of Report'
---
----------------------------------Output
SQL Server Extended Report
==========================
Server Name: TIRUPATI
Product Version: 15.0.2000.5
Edition: Developer Edition (64-bit)
Machine Name: TIRUPATI
Is Clustered: 0
---------------------------------
Operating System Details:
OS Version: 15.0.2000.5
---------------------------------
Databases:
- master (State: ONLINE, Recovery Model: SIMPLE)
- tempdb (State: ONLINE, Recovery Model: SIMPLE)
- model (State: ONLINE, Recovery Model: FULL)
- msdb (State: ONLINE, Recovery Model: SIMPLE)
- naresh_lab (State: ONLINE, Recovery Model: FULL)
- TSQLV4 (State: ONLINE, Recovery Model: FULL)
- mylab (State: ONLINE, Recovery Model: FULL)
SQL Server Configuration:
- recovery interval (min): 0
- allow updates: 0
- disallow results from triggers: 0
- nested triggers: 1
- server trigger recursion: 1
- default language: 0
- cross db ownership chaining: 0
- max worker threads: 0
- network packet size (B): 4096
- show advanced options: 0
- remote proc trans: 0
- default full-text language: 1033
- two digit year cutoff: 2049
- index create memory (KB): 0
- remote login timeout (s): 10
- remote query timeout (s): 600
- cursor threshold: -1
- user options: 0
- affinity mask: 0
- max text repl size (B): 65536
- media retention: 0
- cost threshold for parallelism: 5
- max degree of parallelism: 8
- min memory per query (KB): 1024
- query wait (s): -1
- min server memory (MB): 0
- max server memory (MB): 2147483647
- query governor cost limit: 0
- affinity64 mask: 0
- transform noise words: 0
- precompute rank: 0
- PH timeout (s): 60
- clr enabled: 0
- max full-text crawl range: 4
- ft notify bandwidth (min): 0
- ft notify bandwidth (max): 100
- ft crawl bandwidth (min): 0
- ft crawl bandwidth (max): 100
- default trace enabled: 1
- blocked process threshold (s): 0
- in-doubt xact resolution: 0
- remote admin connections: 0
- EKM provider enabled: 0
- backup compression default: 0
- filestream access level: 0
- optimize for ad hoc workloads: 0
- access check cache bucket count: 0
- access check cache quota: 0
- backup checksum default: 0
- external scripts enabled: 0
- clr strict security: 1
- ADR cleaner retry timeout (min): 0
- ADR Preallocation Factor: 0
- Agent XPs: 0
- Database Mail XPs: 0
- SMO and DMO XPs: 1
- Ole Automation Procedures: 0
- xp_cmdshell: 0
- Ad Hoc Distributed Queries: 0
- Replication XPs: 0
- contained database authentication: 0
- polybase network encryption: 1
- remote data archive: 0
- allow polybase export: 0
- allow filesystem enumeration: 1
- polybase enabled: 0
Recent Backups (Last 30 Days):
Available Disk Space:
(5 rows affected)
- Drive C: 27906 MB free
- Drive D: 185194 MB free
- Drive E: 181318 MB free
- Drive M: 60338 MB free
- Drive N: 86610 MB free
End of Report
Completion time: 2024-01-03T14:38:21.0268023+05:30
Tvenkat
I have 16+ years of experience in IT and 11+ years of experience in training. I am currently working in Bangalore and occasionally in Hyderabad as well. I have worked in various domains including Telecom, Healthcare, Pharma, Insurance, Retail, and Storage. Currently, I am working as a Cloud Principal Consultant in an MNC Product based company.
