SQL Serverownership gaps guide

SELECT
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    c.name AS category_name,
    j.date_created,
    j.date_modified,
    s.name AS schedule_name,
    CASE WHEN s.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS schedule_status,
    js.next_run_date,
    js.next_run_time
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.syscategories AS c
    ON c.category_id = j.category_id
LEFT JOIN msdb.dbo.sysjobschedules AS js
    ON js.job_id = j.job_id
LEFT JOIN msdb.dbo.sysschedules AS s
    ON s.schedule_id = js.schedule_id
ORDER BY job_status, job_owner, j.name;

SELECT TOP (150)
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    h.step_id,
    h.step_name,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'In progress'
        ELSE CONVERT(varchar(20), h.run_status)
    END AS 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.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobhistory AS h
    ON h.job_id = j.job_id
   AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -30, GETDATE()), 112))
WHERE j.enabled = 0
   OR h.run_status <> 1
   OR h.run_duration >= 3000
ORDER BY job_status, run_start_time DESC, duration_seconds DESC;

SELECT
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    s.step_id,
    s.step_name,
    CASE
        WHEN s.command LIKE '%DBCC CHECKDB%' THEN 'CHECKDB'
        WHEN s.command LIKE '%BACKUP DATABASE%' THEN 'Database backup'
        WHEN s.command LIKE '%BACKUP LOG%' THEN 'Log backup'
        WHEN s.command LIKE '%ALTER INDEX%' THEN 'Index maintenance'
        WHEN s.command LIKE '%UPDATE STATISTICS%' THEN 'Statistics update'
        WHEN s.command LIKE '%sp_delete_backuphistory%' THEN 'Backup history cleanup'
        WHEN s.command LIKE '%sp_purge_jobhistory%' THEN 'Job history cleanup'
        WHEN s.command LIKE '%xp_delete_file%' THEN 'File cleanup'
        ELSE 'Other possible maintenance'
    END AS maintenance_type,
    LEFT(REPLACE(REPLACE(s.command, CHAR(13), ' '), CHAR(10), ' '), 700) AS command_preview
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s
    ON s.job_id = j.job_id
WHERE s.command LIKE '%DBCC CHECKDB%'
   OR s.command LIKE '%BACKUP DATABASE%'
   OR s.command LIKE '%BACKUP LOG%'
   OR s.command LIKE '%ALTER INDEX%'
   OR s.command LIKE '%UPDATE STATISTICS%'
   OR s.command LIKE '%sp_delete_backuphistory%'
   OR s.command LIKE '%sp_purge_jobhistory%'
   OR s.command LIKE '%xp_delete_file%'
ORDER BY j.name, s.step_id;

WITH LastBackups AS (
    SELECT
        bs.database_name,
        bs.type,
        bs.backup_start_date,
        bs.backup_finish_date,
        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, -60, GETDATE())
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    CASE lb.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE lb.type
    END AS backup_type,
    lb.backup_finish_date,
    DATEDIFF(hour, lb.backup_finish_date, GETDATE()) AS hours_since_backup,
    DATEDIFF(minute, lb.backup_start_date, lb.backup_finish_date) AS duration_minutes
FROM sys.databases AS d
LEFT JOIN LastBackups AS lb
    ON lb.database_name = d.name
   AND lb.rn = 1
WHERE d.name <> N'tempdb'
ORDER BY d.name, backup_type;

SELECT
    destination_database_name,
    MAX(restore_date) AS last_restore_date,
    COUNT(*) AS restore_records
FROM msdb.dbo.restorehistory
GROUP BY destination_database_name
ORDER BY last_restore_date DESC;

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff_backup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup,
    CASE
        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL
            THEN 'No full backup found'
        WHEN d.recovery_model_desc IN ('FULL', 'BULK_LOGGED')
             AND MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL
            THEN 'Full/Bulk-logged with no log backup found'
        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) < DATEADD(day, -7, GETDATE())
            THEN 'Full backup older than 7 days'
        ELSE 'Review against RPO'
    END AS review_note
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.database_name = d.name
   AND bs.backup_finish_date >= DATEADD(day, -60, GETDATE())
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

SELECT
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    CASE j.notify_level_email
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On success'
        WHEN 2 THEN 'On failure'
        WHEN 3 THEN 'On completion'
        ELSE CONVERT(varchar(20), j.notify_level_email)
    END AS email_notification,
    o.name AS email_operator,
    CASE o.enabled
        WHEN 1 THEN 'Enabled'
        WHEN 0 THEN 'Disabled'
        ELSE 'No operator'
    END AS operator_status
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysoperators AS o
    ON o.id = j.notify_email_operator_id
ORDER BY j.notify_level_email, j.name;

SELECT
    p.name AS profile_name,
    pp.is_default,
    a.name AS account_name,
    a.email_address
FROM msdb.dbo.sysmail_profile AS p
LEFT JOIN msdb.dbo.sysmail_principalprofile AS pp
    ON pp.profile_id = p.profile_id
LEFT JOIN msdb.dbo.sysmail_profileaccount AS pa
    ON pa.profile_id = p.profile_id
LEFT JOIN msdb.dbo.sysmail_account AS a
    ON a.account_id = pa.account_id
ORDER BY p.name, a.name;

SELECT
    sp.name AS login_name,
    sp.type_desc,
    sp.is_disabled,
    sl.is_policy_checked,
    sl.is_expiration_checked,
    STRING_AGG(rp.name, ', ') WITHIN GROUP (ORDER BY rp.name) AS server_roles
FROM sys.server_principals AS sp
LEFT JOIN sys.sql_logins AS sl
    ON sl.principal_id = sp.principal_id
LEFT JOIN sys.server_role_members AS srm
    ON srm.member_principal_id = sp.principal_id
LEFT JOIN sys.server_principals AS rp
    ON rp.principal_id = srm.role_principal_id
WHERE sp.type IN ('S', 'U', 'G')
  AND sp.name NOT LIKE '##%'
GROUP BY sp.name, sp.type_desc, sp.is_disabled, sl.is_policy_checked, sl.is_expiration_checked
ORDER BY CASE WHEN STRING_AGG(rp.name, ', ') LIKE '%sysadmin%' THEN 0 ELSE 1 END, sp.name;

SELECT
    j.name AS job_name,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    sp.is_disabled AS owner_login_disabled
FROM msdb.dbo.sysjobs AS j
LEFT JOIN sys.server_principals AS sp
    ON sp.sid = j.owner_sid
ORDER BY owner_login_disabled DESC, job_owner, j.name;

SELECT
    DB_NAME() AS database_name,
    dp.name AS database_user,
    dp.type_desc,
    dp.authentication_type_desc,
    dp.create_date,
    dp.modify_date
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON sp.sid = dp.sid
WHERE dp.type IN ('S', 'U', 'G')
  AND dp.authentication_type_desc = 'INSTANCE'
  AND sp.sid IS NULL
  AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
ORDER BY dp.name;

SELECT
    @@SERVERNAME AS server_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;

EXEC sys.xp_readerrorlog 0, 1, N'failed';
EXEC sys.xp_readerrorlog 0, 1, N'severity';
EXEC sys.xp_readerrorlog 0, 1, N'recovery';
EXEC sys.xp_readerrorlog 0, 1, N'timeout';
EXEC sys.xp_readerrorlog 0, 1, N'deadlock';
EXEC sys.xp_readerrorlog 0, 1, N'I/O';

SELECT
    ag.name AS availability_group_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.synchronization_health_desc AS replica_health,
    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;