/* ===================================================================== Pacific IT Solutions - SQL Server Health Check v1.0 https://pacificit.solutions/cs/free-check Skript POUZE PRO CTENI: cte konfiguraci a systemove statistiky, nikdy necte data vasich tabulek a nic nemeni. Pozadavky: SQL Server 2016 SP2 nebo novejsi, opravneni VIEW SERVER STATE a cteni z msdb (nejjednodussi: spustte jako sysadmin). Pouziti: otevrete v SSMS nebo Azure Data Studio, stisknete Spustit, zkopirujte bunku "vysledek" a vlozte ji na web. ===================================================================== */ SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DECLARE @json nvarchar(max); ;WITH bk AS ( SELECT database_name, MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full, MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff, MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log FROM msdb.dbo.backupset GROUP BY database_name ) SELECT @json = ( SELECT /* ---------- server ---------- */ CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(50)) AS [server.version], CAST(SERVERPROPERTY('ProductLevel') AS nvarchar(50)) AS [server.level], CAST(SERVERPROPERTY('ProductUpdateLevel') AS nvarchar(50)) AS [server.cu], CAST(SERVERPROPERTY('Edition') AS nvarchar(128)) AS [server.edition], CAST(SERVERPROPERTY('IsHadrEnabled') AS int) AS [server.hadr], (SELECT cpu_count FROM sys.dm_os_sys_info) AS [server.cpus], (SELECT CAST(physical_memory_kb / 1024 AS bigint) FROM sys.dm_os_sys_info) AS [server.ram_mb], (SELECT DATEDIFF(hour, sqlserver_start_time, SYSDATETIME()) FROM sys.dm_os_sys_info) AS [server.uptime_h], (SELECT COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0) AS [server.tempdb_files], (SELECT TOP 1 CAST(cntr_value AS bigint) FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND object_name LIKE '%Buffer Manager%') AS [server.ple], /* ---------- konfigurace ---------- */ JSON_QUERY(( SELECT name, CAST(value_in_use AS bigint) AS v FROM sys.configurations WHERE name IN ('max server memory (MB)', 'max degree of parallelism', 'cost threshold for parallelism', 'xp_cmdshell', 'clr enabled', 'optimize for ad hoc workloads', 'remote admin connections', 'backup compression default') FOR JSON PATH)) AS config, /* ---------- zabezpeceni ---------- */ (SELECT CASE WHEN is_disabled = 0 THEN 1 ELSE 0 END FROM sys.server_principals WHERE sid = 0x01) AS [sec.sa_enabled], (SELECT COUNT(*) FROM sys.server_role_members rm WHERE rm.role_principal_id = (SELECT principal_id FROM sys.server_principals WHERE name = 'sysadmin')) AS [sec.sysadmins], (SELECT COUNT(*) FROM sys.sql_logins WHERE is_policy_checked = 0 AND is_disabled = 0) AS [sec.no_policy], /* ---------- vykon ---------- */ (SELECT COUNT(*) FROM sys.dm_db_missing_index_group_stats WHERE avg_user_impact > 80 AND user_seeks > 1000) AS [perf.missing_idx], /* ---------- ulohy SQL Agenta ---------- */ (SELECT COUNT(DISTINCT job_id) FROM msdb.dbo.sysjobhistory WHERE run_status = 0 AND step_id = 0 AND run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -7, GETDATE()), 112))) AS [jobs.failed7], /* ---------- disky ---------- */ JSON_QUERY(( SELECT DISTINCT vs.volume_mount_point AS vol, CAST(vs.total_bytes / 1048576 AS bigint) AS total_mb, CAST(vs.available_bytes / 1048576 AS bigint) AS free_mb FROM sys.master_files f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs FOR JSON PATH)) AS disks, /* ---------- hlavni cekani ---------- */ JSON_QUERY(( SELECT TOP 8 wait_type AS w, CAST(wait_time_ms / 1000 AS bigint) AS s FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0 AND wait_type NOT LIKE 'SLEEP%' AND wait_type NOT LIKE 'XE%' AND wait_type NOT LIKE 'BROKER%' AND wait_type NOT LIKE 'QDS%' AND wait_type NOT LIKE 'PREEMPTIVE_XE%' AND wait_type NOT LIKE 'PARALLEL_REDO%' AND wait_type NOT IN ('CHECKPOINT_QUEUE','CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT', 'CLR_SEMAPHORE','DBMIRROR_DBM_EVENT','DBMIRROR_EVENTS_QUEUE','DBMIRROR_WORKER_QUEUE', 'DBMIRRORING_CMD','DIRTY_PAGE_POLL','DISPATCHER_QUEUE_SEMAPHORE','EXECSYNC','FSAGENT', 'FT_IFTS_SCHEDULER_IDLE_WAIT','FT_IFTSHC_MUTEX','KSOURCE_WAKEUP','LAZYWRITER_SLEEP', 'LOGMGR_QUEUE','MEMORY_ALLOCATION_EXT','ONDEMAND_TASK_QUEUE', 'PREEMPTIVE_OS_FLUSHFILEBUFFERS','PWAIT_ALL_COMPONENTS_INITIALIZED', 'PWAIT_DIRECTLOGCONSUMER_GETNEXT','REDO_THREAD_PENDING_WORK', 'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK', 'SNI_HTTP_ACCEPT','SOS_WORK_DISPATCHER','SP_SERVER_DIAGNOSTICS_SLEEP', 'SQLTRACE_BUFFER_FLUSH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP','SQLTRACE_WAIT_ENTRIES', 'WAIT_FOR_RESULTS','WAITFOR','WAITFOR_TASKSHUTDOWN','WAIT_XTP_RECOVERY', 'WAIT_XTP_HOST_WAIT','WAIT_XTP_OFFLINE_CKPT_NEW_LOG','WAIT_XTP_CKPT_CLOSE', 'SOS_WORKER_MIGRATION','HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE', 'HADR_TIMER_TASK','HADR_LOGCAPTURE_WAIT','HADR_CLUSAPI_CALL', 'HADR_NOTIFICATION_DEQUEUE','CXCONSUMER') ORDER BY wait_time_ms DESC FOR JSON PATH)) AS waits, /* ---------- databaze ---------- */ JSON_QUERY(( SELECT TOP 100 d.name, d.recovery_model_desc AS rec, d.compatibility_level AS compat, d.is_auto_close_on AS ac, d.is_auto_shrink_on AS ash, d.page_verify_option_desc AS pv, d.is_trustworthy_on AS tw, d.state_desc AS st, SUSER_SNAME(d.owner_sid) AS own, DATEDIFF(hour, b.last_full, SYSDATETIME()) AS full_h, DATEDIFF(hour, b.last_diff, SYSDATETIME()) AS diff_h, DATEDIFF(minute, b.last_log, SYSDATETIME()) AS log_m, DATEDIFF(day, CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime), SYSDATETIME()) AS chk_d, (SELECT SUM(CAST(size AS bigint)) * 8 / 1024 FROM sys.master_files f WHERE f.database_id = d.database_id AND f.type = 0) AS data_mb, (SELECT SUM(CAST(size AS bigint)) * 8 / 1024 FROM sys.master_files f WHERE f.database_id = d.database_id AND f.type = 1) AS log_mb, (SELECT COUNT(*) FROM sys.master_files f WHERE f.database_id = d.database_id AND f.is_percent_growth = 1) AS pct_growth FROM sys.databases d LEFT JOIN bk b ON b.database_name = d.name WHERE d.database_id <> 2 ORDER BY d.name FOR JSON PATH)) AS dbs, /* ---------- informace o skriptu ---------- */ '1.0' AS [meta.v], CONVERT(nvarchar(30), SYSDATETIME(), 126) AS [meta.at] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ); SELECT @json AS vysledek;