Question

You administer a SQL Server on an Azure VM that runs dozens of SQL Server Agent jobs every few minutes. Developers complain that when they open a job's history in SQL Server Management Studio, older execution records disappear and they cannot troubleshoot failures from earlier in the day. The msdb database is not full and the jobs themselves succeed. What is the MOST likely cause, and how should you correct it?

Accepted Answer

The SQL Server Agent history log limits are too restrictive; increase the 'Maximum job history log size (in rows)' and 'Maximum job history rows per job' settings, or disable the row-count limits, in the Agent properties.

Answer

The msdb transaction log is auto-truncating history rows; switch msdb to the FULL recovery model to preserve job history.

Answer

The jobs are missing a notification operator; add an operator so that history entries are retained after each run.

Answer

The SQL Server Agent service account lacks db_owner on msdb; grant it so history writes are not rolled back.