Hola, chicos,
¿Cómo estás?

Hoy recibí una pregunta muy común que la gente tiene cuando usa trabajos del Agente SQL Server, que es cuando el historial de ejecución de los trabajos del Agente SQL desaparece después de un tiempo. Tienes varios trabajos en ejecución y cuando necesitas analizar el historial de un trabajo específico, no muestra nada en el historial de ejecución. ¿Te ha pasado esto alguna vez?

La buena noticia es que entender y solucionar este problema es muy fácil.

Los parámetros de retención del historial de trabajos del Agente SQL Server

Para ver los parámetros de retención del historial de trabajos del Agente SQL Server, vaya a las propiedades del Agente SQL Server.

Y ahora seleccione la opción "Historial" en el panel izquierdo.

Verá algunas opciones para administrar su historial. Compruebe si la opción "Limitar tamaño del registro del historial de trabajos" está habilitada. De lo contrario, el banco almacenará el historial de trabajos indefinidamente, a menos que la opción “Eliminar historial del agente” esté habilitada. En este caso, el historial se borrará en el intervalo definido cuando haga clic en Aceptar en esta pantalla.

Importante: Esta casilla de verificación "Eliminar historial del agente" es prácticamente un error de la interfaz SSMS, porque no hace que el registro se elimine automáticamente dentro del período definido.

Esta casilla de verificación solo marca que desea eliminar los registros, dentro de este intervalo definido, cuando hace clic en el botón Aceptar para confirmar, pero no existe un proceso automático que elimine estos datos, es manual. Tienes que ingresar nuevamente a esta pantalla, marcar la casilla, definir el período y hacer clic en Aceptar para eliminar nuevamente.

Puede ver que si hace clic en esta casilla de verificación, cierra esta pantalla y la vuelve a abrir, la casilla de verificación vuelve a estar sin marcar.

Si está habilitada la opción “Limitar tamaño del registro del historial de trabajos”, que es la predeterminada, se deben observar los valores de los parámetros “Tamaño máximo del registro del historial de trabajos (en filas)” y “Registro máximo del historial de trabajos por trabajo”, cuyos valores predeterminados son 1000 y 100, respectivamente.

Posiblemente este sea tu problema, especialmente si no has cambiado la configuración predeterminada.

¿Por qué está desapareciendo el historial de ejecución de los trabajos del Agente SQL?

Imagine que tiene 20 trabajos ejecutándose en la instancia, 10 de ellos se ejecutan cada minuto y el resto se ejecuta una vez al día.

En un intervalo de apenas 100 minutos (1h y 40 minutos), a todos los trabajos que se ejecuten una vez al día se les borrará su historial de ejecución, ya que, aunque el Agente está almacenando hasta 100 registros por trabajo, debido al parámetro “Registro máximo del historial de trabajos por trabajo” = 100, el límite total de líneas del historial "Tamaño máximo de registro del historial de trabajos (en filas)" está establecido en 1000 registros y este segundo límite puede superponerse con el límite por trabajo si se alcanza el número total de filas.

Es decir, si se ejecutan 10 trabajos cada 1 minuto, en 100 minutos ya alcanzarán el límite de 1.000 líneas de historial y empezarán a sobrescribir registros anteriores, y como resultado, los trabajos que se ejecutan sólo una vez al día acaban perdiendo su historial, porque serán sobrescritos por otros trabajos, que se ejecutan con más frecuencia.

El cálculo es sencillo:
(número de trabajos en la instancia) x (límite de líneas por trabajos) < (límite total de líneas del historial)

Con la configuración predeterminada, en un entorno con 20 trabajos:
20 x 100 tendría que ser menor que 1000, pero esto es falso, por lo tanto, la configuración de 1000 líneas en total no es suficiente para un entorno con 20 trabajos y 100 líneas por trabajo, ya que el historial de trabajos se superpondrá en algún momento.

Aunque la configuración del agente limita cada trabajo para almacenar hasta 100 filas, este límite no se garantiza cuando el número total de filas del historial excede el límite general del Agente (“Tamaño máximo de registro del historial de trabajos (en filas)”)

Cómo aumentar la retención del historial de ejecución de los trabajos del Agente SQL

Resolver este problema es bastante fácil: simplemente cambie los parámetros de retención del historial. Lo que suelo implementar en los entornos es utilizar el valor máximo permitido en el parámetro "Tamaño máximo de registro del historial de trabajos (en filas)", que es 999999, y limitar el número de filas por trabajo a 1000.

Esto significará que cada trabajo puede almacenar hasta 1000 ejecuciones por trabajo y el límite máximo del Agente no anulará el valor de este parámetro (excepto si tiene más de 999 trabajos en la instancia; en este caso, reduzca el límite máximo por trabajo según la cantidad de trabajos).

¿Cuáles son las consecuencias de aumentar el tamaño del historial laboral?

Ahora que ha aprendido cómo aumentar la cantidad de líneas de historial almacenadas, debemos comprender las consecuencias de esto.

Tamaño en disco
El primer punto que se debe considerar es el espacio en disco que se necesitará para almacenar estos datos, ya que como sabrás, este historial se almacena en la base de datos msdb. Para una cantidad de 1000 registros en la tabla de historial, el tamaño medio ocupado es de aproximadamente 1 MB. Para el tamaño máximo permitido (999.999), se debería necesitar alrededor de 1 GB de espacio en disco, lo que no suele ser muy significativo.

Cabellos
Otro punto que se debe considerar es en relación con los bloqueos y la posibilidad de una caída en el rendimiento debido al aumento en el tamaño de la base msdb. Para entornos con muchos puestos de trabajo, esto puede acabar siendo un problema, principalmente porque en cada ejecución de trabajo, el Agente SQL ejecuta el procedimiento dbo.sp_agent_log_job_history para almacenar el registro de ejecución, que ejecuta internamente msdb.dbo.sp_sqlagent_log_jobhistory y llama al msdb.dbo.sp_jobhistory_row_limiter.

El procedimiento sp_jobhistory_row_limiter tiene un check en el que solo se ejecuta en cada ejecución de trabajo en la instancia, si la casilla de verificación para limitar el número de filas (Limitar tamaño del registro del historial de trabajos) está activada:

Si la casilla de verificación está habilitada, el procedimiento sp_jobhistory_row_limiter se ejecutará en cada ejecución de trabajo e inicia una transacción para contar cuántas filas de historial en la tabla msdb.dbo.sysjobhistory tiene ese trabajo, usando una sugerencia CON(TABLOCKX), bloqueando toda la tabla exclusivamente durante esta verificación y eliminando registros que excedieron el límite máximo total o el límite máximo por trabajo.

Aunque esto es muy rápido, si la instancia tiene muchos trabajos ejecutándose al mismo tiempo, y con una tabla de historial más grande (porque el límite total de filas ha aumentado), esto podría terminar generando un problema de bloqueo en la administración del Agente SQL Server.

Trabajos que no se ejecutan
Otro problema que puede ocurrir, en entornos con muchos trabajos ejecutándose al mismo tiempo, es que los trabajos no se puedan ejecutar a la hora programada debido al problema previo (bloqueos), donde el Agente está esperando por mucho tiempo para poder asignar la tabla y registrar los datos de ejecución del trabajo, ocurre un tiempo de espera y la ejecución del trabajo devuelve un error.

Una mejor alternativa para borrar el historial de ejecución de trabajos

Si prefieres una alternativa definitiva a este problema, has venido al lugar indicado 🙂

  • Si intentas limitar el historial de trabajos por número de filas, usando la interfaz del Agente SQL, te encontrarás con el problema de que al habilitar la limitación por filas, cada ejecución de trabajo llamará al procedimiento almacenado sp_jobhistory_row_limiter, lo que puede generar problemas de bloqueo en la tabla msdb.dbo.sysjobhistory debido al uso de la sugerencia CON(TABLOCKX) y, por tanto, también puede perjudicar la ejecución de trabajos en el momento correcto en entornos con mucha ejecución de trabajos simultáneamente.
  • Si marca la casilla en la interfaz para eliminar el historial de trabajos anteriores a un intervalo definido, este proceso es manual y tendrá que volver a esta pantalla cada vez para borrar los datos.
  • Y si no activa ninguna de las 2 casillas de verificación, los datos crecerán indefinidamente, lo que aumentará el espacio utilizado para almacenar estos datos y ralentizará las consultas MSDB relacionadas con el historial de trabajos.

¿Qué hacer para resolver esto?

Para resolver todos estos problemas a la vez, la mejor alternativa es DESACTIVAR las 2 casillas de verificación en la pantalla de retención del historial (así es, eliminar la limitación del historial) y crear un trabajo diario que limpie los registros de ejecución utilizando el procedimiento almacenado. msdb.dbo.sp_purge_jobhistory, que borrará todo el historial de ejecución de todos los trabajos anteriores a una fecha específica.

Ejemplo de uso:

DECLARE @Dt_Limite DATETIME = DATEADD(DAY, -180, CONVERT(DATE, GETDATE()));

-- Mantém apenas o histórico de execução dos jobs dos últimos 180 dias
EXEC [msdb].[dbo].[sp_purge_jobhistory]
    @oldest_date = @Dt_Limite -- datetime

El ejemplo anterior solo mantendrá el historial de ejecución del trabajo durante los últimos 180 días y eliminará los registros anteriores. Al usar este comando T-SQL, no necesitará preocuparse por la cantidad de filas que desea conservar (lo cual ni siquiera tiene mucho sentido), solo preocuparse por la cantidad de días del historial que conservará.

Ahora SQL Server no ejecutará el procedimiento almacenado sp_jobhistory_row_limiter con cada ejecución de trabajo, evitando posibles problemas de bloqueo y aún tendrá control sobre el crecimiento del tamaño de la tabla del historial de trabajos.

¿Necesita hacer esto para todos los entornos? Sería ideal y recomendable, pero si tu entorno no tiene muchos trabajos ni mucha competencia, puedes seguir dejando esta responsabilidad al propio limitador del Agente SQL Server, pero si empiezas a tener problemas no olvides seguir este consejo, ¿vale?

Espero que hayas disfrutado de este consejo rápido y ¡hasta luego!