SQL Serverpatching guide

SELECT
    @@SERVERNAME AS server_name,
    SERVERPROPERTY(N'MachineName') AS machine_name,
    SERVERPROPERTY(N'InstanceName') AS instance_name,
    SERVERPROPERTY(N'Edition') AS edition,
    SERVERPROPERTY(N'ProductVersion') AS product_version,
    SERVERPROPERTY(N'ProductLevel') AS product_level,
    SERVERPROPERTY(N'ProductUpdateLevel') AS product_update_level,
    SERVERPROPERTY(N'ProductUpdateReference') AS product_update_reference,
    SERVERPROPERTY(N'EngineEdition') AS engine_edition;

SELECT
    si.sqlserver_start_time,
    DATEDIFF(hour, si.sqlserver_start_time, SYSDATETIME()) AS uptime_hours,
    si.cpu_count,
    si.scheduler_count,
    si.physical_memory_kb / 1024 AS physical_memory_mb,
    si.virtual_machine_type_desc
FROM sys.dm_os_sys_info AS si;

SELECT
    name AS database_name,
    state_desc,
    compatibility_level,
    recovery_model_desc,
    is_read_only,
    is_query_store_on
FROM sys.databases
WHERE name NOT IN (N'master', N'model', N'msdb', N'tempdb')
ORDER BY name;

SELECT
    d.name AS database_name,
    d.is_query_store_on,
    qso.actual_state_desc,
    qso.desired_state_desc,
    qso.readonly_reason,
    qso.current_storage_size_mb,
    qso.max_storage_size_mb
FROM sys.databases AS d
LEFT JOIN sys.database_query_store_options AS qso
    ON qso.database_id = d.database_id
WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
ORDER BY d.name;

SELECT
    ag.name AS availability_group_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.synchronization_health_desc AS replica_health,
    drs.database_id,
    DB_NAME(drs.database_id) AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc AS database_health,
    drs.log_send_queue_size,
    drs.redo_queue_size
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
    ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars
    ON ars.replica_id = ar.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states AS drs
    ON drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, database_name;

WITH LastBackups AS (
    SELECT
        bs.database_name,
        bs.type,
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.backup_size,
        bs.compressed_backup_size,
        ROW_NUMBER() OVER (
            PARTITION BY bs.database_name, bs.type
            ORDER BY bs.backup_finish_date DESC
        ) AS rn
    FROM msdb.dbo.backupset AS bs
    WHERE bs.backup_finish_date >= DATEADD(day, -14, GETDATE())
)
SELECT
    database_name,
    CASE type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE type
    END AS backup_type,
    backup_start_date,
    backup_finish_date,
    DATEDIFF(minute, backup_start_date, backup_finish_date) AS duration_minutes,
    CONVERT(decimal(18,2), compressed_backup_size / 1048576.0) AS compressed_size_mb
FROM LastBackups
WHERE rn = 1
ORDER BY database_name, backup_type;

SELECT TOP (100)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS duration_seconds,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -14, GETDATE()), 112))
  AND (h.run_status <> 1 OR h.run_duration >= 3000)
ORDER BY run_start_time DESC, duration_seconds DESC;

SELECT
    SERVERPROPERTY(N'ProductVersion') AS product_version,
    SERVERPROPERTY(N'ProductLevel') AS product_level,
    SERVERPROPERTY(N'ProductUpdateLevel') AS product_update_level,
    SERVERPROPERTY(N'ProductUpdateReference') AS product_update_reference;

SELECT
    name,
    state_desc,
    user_access_desc,
    recovery_model_desc
FROM sys.databases
ORDER BY name;

SELECT TOP (50)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -2, GETDATE()), 112))
ORDER BY run_start_time DESC;