Etiqueta: sql-server

Historial de ejecuciones de jobs SSIS en SQL Server Agent

SQL Server Agent guarda el historial completo de ejecuciones de todos sus jobs en la base de datos del sistema msdb, pero la información viene en un formato poco amigable — fechas y horas como enteros separados, duraciones codificadas como HHMMSS sin separadores, y estados representados por números. Esta consulta traduce todo eso a algo legible, enfocado específicamente en pasos de tipo SSIS.

Tablas utilizadas

Tabla	Descripción
`msdb.dbo.sysjobs`	Definición de los jobs del Agente
`msdb.dbo.sysjobsteps`	Pasos de cada job (tipo, comando, etc.)
`msdb.dbo.sysjobhistory`	Historial de ejecuciones (fecha, duración, estado)

Las tres se relacionan por job_id (y step_id entre pasos e historial), ya que un job puede tener varios pasos, y cada paso acumula múltiples ejecuciones a lo largo del tiempo.

La query

USE msdb;
GO

SELECT
    j.name                                              AS JobName,
    js.step_id                                          AS StepNo,
    js.step_name                                        AS StepName,
    js.subsystem                                        AS StepType,       -- 'SSIS' para paquetes SSIS
    js.command                                          AS SSISCommand,     -- ruta del paquete o comando

    -- Fecha y hora de inicio legibles
    msdb.dbo.agent_datetime(h.run_date, h.run_time)     AS RunStart,

    -- Duración formateada HH:MM:SS
    STUFF(STUFF(
        RIGHT(REPLICATE('0', 6) + CAST(h.run_duration AS VARCHAR(6)), 6),
    3, 0, ':'), 6, 0, ':')                              AS Duration_HHMMSS,

    -- Duración en segundos (para cálculos / ORDER BY)
    (h.run_duration / 10000 * 3600)
    + ((h.run_duration / 100) % 100 * 60)
    + (h.run_duration % 100)                            AS Duration_Seconds,

    -- Fecha fin estimada
    DATEADD(
        SECOND,
        (h.run_duration / 10000 * 3600)
        + ((h.run_duration / 100) % 100 * 60)
        + (h.run_duration % 100),
        msdb.dbo.agent_datetime(h.run_date, h.run_time)
    )                                                   AS RunEnd,

    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
    END                                                 AS RunStatus,

    h.message                                           AS ResultMessage,
    j.enabled                                           AS JobEnabled

FROM msdb.dbo.sysjobs        j
JOIN msdb.dbo.sysjobsteps    js ON js.job_id = j.job_id
JOIN msdb.dbo.sysjobhistory  h  ON h.job_id  = j.job_id
                                AND h.step_id = js.step_id

WHERE js.subsystem = 'SSIS'
  -- AND j.enabled  = 1          -- descomentar: solo jobs activos
  -- AND h.run_date >= 20250101  -- descomentar: filtrar por fecha (YYYYMMDD)

ORDER BY
    j.name,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) DESC;

USE msdb;
GO

SELECT
    j.name                                              AS JobName,
    js.step_id                                          AS StepNo,
    js.step_name                                        AS StepName,
    js.subsystem                                        AS StepType,       -- 'SSIS' para paquetes SSIS
    js.command                                          AS SSISCommand,     -- ruta del paquete o comando

    -- Fecha y hora de inicio legibles
    msdb.dbo.agent_datetime(h.run_date, h.run_time)     AS RunStart,

    -- Duración formateada HH:MM:SS
    STUFF(STUFF(
        RIGHT(REPLICATE('0', 6) + CAST(h.run_duration AS VARCHAR(6)), 6),
    3, 0, ':'), 6, 0, ':')                              AS Duration_HHMMSS,

    -- Duración en segundos (para cálculos / ORDER BY)
    (h.run_duration / 10000 * 3600)
    + ((h.run_duration / 100) % 100 * 60)
    + (h.run_duration % 100)                            AS Duration_Seconds,

    -- Fecha fin estimada
    DATEADD(
        SECOND,
        (h.run_duration / 10000 * 3600)
        + ((h.run_duration / 100) % 100 * 60)
        + (h.run_duration % 100),
        msdb.dbo.agent_datetime(h.run_date, h.run_time)
    )                                                   AS RunEnd,

    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
    END                                                 AS RunStatus,

    h.message                                           AS ResultMessage,
    j.enabled                                           AS JobEnabled

FROM msdb.dbo.sysjobs        j
JOIN msdb.dbo.sysjobsteps    js ON js.job_id = j.job_id
JOIN msdb.dbo.sysjobhistory  h  ON h.job_id  = j.job_id
                                AND h.step_id = js.step_id

WHERE js.subsystem = 'SSIS'
  -- AND j.enabled  = 1          -- descomentar: solo jobs activos
  -- AND h.run_date >= 20250101  -- descomentar: filtrar por fecha (YYYYMMDD)

ORDER BY
    j.name,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) DESC;

Cómo funciona cada parte

Fecha y hora legibles

SQL Server Agent guarda run_date y run_time como dos enteros separados (por ejemplo 20260622 y 143000). La función del sistema msdb.dbo.agent_datetime() los combina y convierte a un DATETIME normal, evitando tener que parsear los enteros a mano.

Duración: de entero codificado a HH:MM:SS

run_duration también viene como entero, pero en formato HHMMSS concatenado (por ejemplo, 13045 significa 1 hora, 30 minutos, 45 segundos). El doble STUFF() rellena con ceros a la izquierda hasta 6 dígitos y luego inserta los dos puntos en las posiciones correctas, para que se lea como una duración real.

Duración en segundos

Para poder ordenar o hacer cálculos con la duración (algo que el formato HH:MM:SS como texto no permite bien), la query descompone el mismo entero run_duration en horas, minutos y segundos por separado usando división entera y módulo, y los suma todos convertidos a segundos.

Fecha de fin estimada

Como sysjobhistory solo guarda la hora de inicio y la duración (no la hora de fin), RunEnd se calcula sumando la duración en segundos (ya calculada arriba) a la fecha de inicio con DATEADD.

Estado de la ejecución

El campo run_status es un entero sin significado evidente por sí solo. El CASE lo traduce a los cinco estados posibles que maneja SQL Server Agent: Failed, Succeeded, Retry, Cancelled e In Progress.

Filtros opcionales

La query trae dos filtros comentados, útiles según el caso:

AND j.enabled = 1 — descomentar para ver únicamente jobs que están activos actualmente (excluye jobs deshabilitados que quedaron en el historial).
AND h.run_date >= 20250101 — descomentar para acotar el historial a partir de una fecha específica, en formato YYYYMMDD. Útil en instancias con mucho tiempo de historial acumulado, donde la consulta completa puede ser lenta.

Variante: solo las ejecuciones fallidas

Para monitoreo o alertas, suele ser más útil filtrar directamente por las ejecuciones que fallaron, en vez de traer todo el historial:

-- Agregar a la cláusula WHERE de la query original:
AND h.run_status = 0  -- solo ejecuciones fallidas

-- Agregar a la cláusula WHERE de la query original:
AND h.run_status = 0  -- solo ejecuciones fallidas

Notas

run_duration está en formato HHMMSS como entero — requiere conversión manual, no es un tipo de dato de duración nativo.
agent_datetime(run_date, run_time) convierte los campos enteros a DATETIME legible.
Los filtros opcionales comentados permiten activar jobs y/o rango de fechas según la necesidad puntual de cada consulta.

23/09/2023

Diagnóstico de memoria y versión en SQL Server

Contexto

Servidor de ejemplo con las siguientes características: 30GB de disco, 2 vCPU, 4GB de RAM, collation Modern_Spanish_CI_AS. Este tipo de diagnóstico es útil cuando un servidor de SQL Server empieza a mostrar señales de presión de memoria, o simplemente para confirmar exactamente qué versión y edición está corriendo antes de planificar un cambio.

1. Versión y edición del motor

Lo primero es confirmar exactamente qué se está ejecutando — la edición determina límites como cuánta RAM puede usar el buffer pool:

SELECT
    SERVERPROPERTY('Edition')       AS edition,
    SERVERPROPERTY('ProductVersion') AS version,
    SERVERPROPERTY('EngineEdition') AS engine_edition;

SELECT
    SERVERPROPERTY('Edition')       AS edition,
    SERVERPROPERTY('ProductVersion') AS version,
    SERVERPROPERTY('EngineEdition') AS engine_edition;

2. Límite de memoria configurado (max server memory)

SQL Server, por defecto, puede intentar usar prácticamente toda la RAM disponible del servidor si no se configura un límite explícito. Esta consulta muestra qué valor está configurado actualmente:

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'max server memory (MB)';

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'max server memory (MB)';

3. Uso total de memoria del proceso SQL Server

Para ver cuánta memoria está usando realmente el proceso completo, incluyendo memoria virtual comprometida:

SELECT
    physical_memory_in_use_kb   / 1024 AS memory_in_use_MB,
    large_page_allocations_kb   / 1024 AS large_page_MB,
    locked_page_allocations_kb  / 1024 AS locked_page_MB,
    total_virtual_address_space_kb / 1024 AS total_virtual_MB,
    virtual_address_space_committed_kb / 1024 AS committed_virtual_MB
FROM sys.dm_os_process_memory;

SELECT
    physical_memory_in_use_kb   / 1024 AS memory_in_use_MB,
    large_page_allocations_kb   / 1024 AS large_page_MB,
    locked_page_allocations_kb  / 1024 AS locked_page_MB,
    total_virtual_address_space_kb / 1024 AS total_virtual_MB,
    virtual_address_space_committed_kb / 1024 AS committed_virtual_MB
FROM sys.dm_os_process_memory;

4. Uso de memoria por tipo (memory clerks)

SQL Server organiza internamente su consumo de memoria en «clerks» según el componente que la usa (buffer pool, caché de planes de ejecución, etc.). Esta consulta agrupa el consumo por tipo, de mayor a menor:

SELECT
    type,
    SUM(pages_kb) / 1024 AS memory_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY memory_MB DESC;

SELECT
    type,
    SUM(pages_kb) / 1024 AS memory_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY memory_MB DESC;

5. Buffer pool específico

El buffer pool es el componente que cachea páginas de datos en memoria para evitar lecturas a disco — es justo lo que las ediciones Express y Standard limitan agresivamente. Esta consulta aísla específicamente cuánto está usando:

SELECT
    SUM(pages_kb) / 1024 AS buffer_pool_MB
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL';

SELECT
    SUM(pages_kb) / 1024 AS buffer_pool_MB
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL';

Resumen de lo que responde cada consulta

Pregunta	DMV / Vista utilizada
¿Qué edición y versión está corriendo?	`SERVERPROPERTY`
¿Cuánto está configurado como límite de RAM?	`sys.configurations`
¿Cuánta RAM usa el proceso completo de SQL?	`sys.dm_os_process_memory`
¿Qué componentes están consumiendo más memoria?	`sys.dm_os_memory_clerks`
¿Cuánto usa el buffer pool (el que se limita)?	`sys.dm_os_memory_clerks`

22/06/2022