none
Programacion de Jobs RRS feed

  • Pregunta

  • Hola.

    En un SQL 2005.

    Tengo un script que tiene los detalles de los jobs y sus pasos con su hora de inicio.

    Lo que no puedo encontrar y/o determinar es los jobs que se ejecutan cada N minutos por ejemplo y los que tienen mas de una programación.

    Básicamente necesito mostrar la programación de los jobs y sus pasos.

    Saludos.


    DBA SQL Server Santiago/Chile

    miércoles, 29 de junio de 2016 18:57

Respuestas

  • Usa el siguiente codigo:

    SELECT DISTINCT substring(a.name,1,100) AS [Job Name], 
    	'Enabled'=case 
    	WHEN a.enabled = 0 THEN 'No'
    	WHEN a.enabled = 1 THEN 'Yes'
    	end, 
        	substring(b.name,1,30) AS [Name of the schedule],
    	'Frequency of the schedule execution'=case
    	WHEN b.freq_type = 1 THEN 'Once'
    	WHEN b.freq_type = 4 THEN 'Daily'
    	WHEN b.freq_type = 8 THEN 'Weekly'
    	WHEN b.freq_type = 16 THEN 'Monthly'
    	WHEN b.freq_type = 32 THEN 'Monthly relative'	
    	WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
    	END,	
    	'Units for the freq_subday_interval'=case
    	WHEN b.freq_subday_type = 1 THEN 'At the specified time' 
    	WHEN b.freq_subday_type = 2 THEN 'Seconds' 
    	WHEN b.freq_subday_type = 4 THEN 'Minutes' 
    	WHEN b.freq_subday_type = 8 THEN 'Hours' 
    	END,	
    	cast(cast(b.active_start_date as varchar(15)) as datetime) as active_start_date,	
    	cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,	
    	Stuff(Stuff(right('000000'+Cast(c.next_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time,	
    	convert(varchar(24),b.date_created) as Created_Date
    	
    FROM  msdb.dbo.sysjobs a 
    INNER JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id 
    INNER JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id
    GO

    En este link puedes encontrar la consulta antes planteada.

    https://community.spiceworks.com/scripts/show/2143-list-all-the-sql-server-jobs


    "Oh, the wind, the wind is blowing,through the graves the wind is blowing,Freedom soon will come; then well come from the shadows".The Partisan(Leonard Cohen) Email: me[at]geohernandez.net Blog:www.geohernandez.net

    • Marcado como respuesta José De Alva jueves, 30 de junio de 2016 14:58
    miércoles, 29 de junio de 2016 20:35

Todas las respuestas

  • Usa el siguiente codigo:

    SELECT DISTINCT substring(a.name,1,100) AS [Job Name], 
    	'Enabled'=case 
    	WHEN a.enabled = 0 THEN 'No'
    	WHEN a.enabled = 1 THEN 'Yes'
    	end, 
        	substring(b.name,1,30) AS [Name of the schedule],
    	'Frequency of the schedule execution'=case
    	WHEN b.freq_type = 1 THEN 'Once'
    	WHEN b.freq_type = 4 THEN 'Daily'
    	WHEN b.freq_type = 8 THEN 'Weekly'
    	WHEN b.freq_type = 16 THEN 'Monthly'
    	WHEN b.freq_type = 32 THEN 'Monthly relative'	
    	WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
    	END,	
    	'Units for the freq_subday_interval'=case
    	WHEN b.freq_subday_type = 1 THEN 'At the specified time' 
    	WHEN b.freq_subday_type = 2 THEN 'Seconds' 
    	WHEN b.freq_subday_type = 4 THEN 'Minutes' 
    	WHEN b.freq_subday_type = 8 THEN 'Hours' 
    	END,	
    	cast(cast(b.active_start_date as varchar(15)) as datetime) as active_start_date,	
    	cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,	
    	Stuff(Stuff(right('000000'+Cast(c.next_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time,	
    	convert(varchar(24),b.date_created) as Created_Date
    	
    FROM  msdb.dbo.sysjobs a 
    INNER JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id 
    INNER JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id
    GO

    En este link puedes encontrar la consulta antes planteada.

    https://community.spiceworks.com/scripts/show/2143-list-all-the-sql-server-jobs


    "Oh, the wind, the wind is blowing,through the graves the wind is blowing,Freedom soon will come; then well come from the shadows".The Partisan(Leonard Cohen) Email: me[at]geohernandez.net Blog:www.geohernandez.net

    • Marcado como respuesta José De Alva jueves, 30 de junio de 2016 14:58
    miércoles, 29 de junio de 2016 20:35
  • Hola.

    Muchas gracias, un solo punto:

    Programe un paso que se ejecuta cada 2 horas todos los dias, pero me dice que se ejecuta por intervalo de Hours, pero no me indica la cantidad de horas

    test Yes 1 Daily Hours 2016-06-29 00:00:00.000 9999-12-31 00:00:00.000 00:00:00 Jun 29 2016  4:55PM

    Todo lo demas esta fantastico.

    Saludos.


    DBA SQL Server Santiago/Chile


    • Editado CMAPM miércoles, 29 de junio de 2016 20:57
    miércoles, 29 de junio de 2016 20:57