SQL Server Agent日志报错（等待恢复msdb）及备份任务异常排查请求

Alright, let's break down this problem step by step to get to the root of why your maintenance plan backups stopped working after that Windows patch, especially with that msdb recovery error in the SQL Agent logs.

1. Quick Context Recap #

You confirmed all SQL Server services/Agent are running, databases are online, but maintenance plan backups aren't generating. The SQL Server Agent log is throwing: "等待SQL Server恢复数据库'msdb'" (Waiting for SQL Server to recover database 'msdb').

2. Why the msdb Recovery Error Matters #

The msdb database is the backbone of SQL Server Agent—it stores all job definitions, maintenance plan metadata, job history, and schedule info. That log error tells us a critical detail: when SQL Server Agent first started up after the patch, msdb wasn't fully recovered yet. Here's how that breaks things:

• SQL Agent can't load jobs or maintenance plans until msdb is ready. If Agent started before msdb finished recovering, it might have failed to load your backup jobs entirely.
• Even if Agent runs fine now, the initial failed load could have left your maintenance plan jobs in a disabled or unrecognized state.

Steps to Verify This: #

• Check the SQL Server error log (not just the Agent log) to find when msdb completed recovery. Compare that timestamp to when SQL Server Agent started.
  • Run this query to pull msdb's last recovery time:

    SELECT 
      name, 
      CAST(DATABASEPROPERTYEX(name, 'LastRecoveryTime') AS DATETIME) AS LastRecoveryTime
    FROM sys.databases WHERE name = 'msdb';
    

• Find SQL Agent's start time in Windows Event Viewer (Application log, source SQLSERVERAGENT) or cross-reference with SQL Server's start time:

  SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
  

  (Agent usually starts around the same time as SQL Server, but Event Viewer will give you the exact Agent start timestamp.)

3. Why Maintenance Plans Aren't Running (Even With Services Up) #

Even if services are stable now, the patch-related startup issue with msdb is likely the trigger. Let's dig into possible broken states:

a. Maintenance Plan Jobs Are Disabled or Not Loaded #

• Open SSMS, navigate to SQL Server Agent > Jobs. Look for jobs tied to your maintenance plan (they usually have names matching the plan, or start with MaintenancePlan...).
• Check if the job is enabled. If it's disabled, the initial Agent startup failure probably toggled this setting.
• If the job is missing entirely, msdb's incomplete recovery when Agent started might have corrupted the plan's metadata.

b. SQL Agent Service Account Permissions Got Reset #

Windows patches occasionally reset service account permissions (it's rare, but worth checking):

• Confirm the SQL Server Agent service account has:
  • Read/write access to your backup destination folder.
  • SQL Server permissions to perform backups (db_backupoperator role on target databases, plus full access to msdb).
• Check Windows Event Viewer around the scheduled backup time for permission-related errors (like "access denied").

c. Maintenance Plan Metadata Is Corrupted #

If msdb was still recovering when Agent tried to access the maintenance plan, the plan's metadata in msdb could be damaged:

• Try opening the maintenance plan in SSMS. If it throws an error, the plan is likely corrupted.
• Pull the maintenance plan logs to see specific failures:

  SELECT * FROM msdb.dbo.sysmaintplan_log ORDER BY log_date DESC;
  SELECT * FROM msdb.dbo.sysmaintplan_logdetail ORDER BY log_date DESC;
  

d. SQL Agent Scheduler Lost Track of Job Schedules #

Post-patch, the Agent scheduler might have dropped the job's schedule:

• For the affected job, right-click > Properties > Schedules. Double-check the frequency, time, and enabled status match your intended setup.
• Check the job history (right-click job > View History). If there are no entries for the scheduled times, the job wasn't even triggered—pointing to a schedule or loading issue.

4. Fixes to Try (Based on Your RCA) #

• If msdb recovered after Agent started: Restart SQL Server Agent. This forces it to reload all jobs from the now-healthy msdb.
• If jobs are disabled: Enable them, then run the job manually to confirm backups work.
• If permissions are missing: Restore the necessary Windows/SQL Server permissions for the Agent service account.
• If the maintenance plan is corrupted: Recreate the plan, or restore a pre-patch backup of msdb (if you have one).
• Always verify job history and backup files after any fix to ensure things are back on track.

内容的提问来源于stack exchange，提问作者user7488971