SQL Server 2005 info

[beechill4]

Hi,

i am trying to write a script that pulls out what sql server jobs are setup on sql server 2005.

In sql 2000 i used the following script:

PRINT ' BACKUP Plans'
PRINT '---------------------------------------------------'

SELECT [name],
[originating_server],
[msdb].[dbo].[sysdbmaintplan_databases].[database_name] as "dbname",
(SELECT command
FROM msdb..sysjobsteps
WHERE job_id = [msdb].[dbo].[sysjobs_view].job_id
) as "command"
FROM [msdb].[dbo].[sysjobs_view]
INNER JOIN [msdb].[dbo].[sysdbmaintplan_jobs]
ON [msdb].[dbo].[sysjobs_view].[job_id] = [msdb].[dbo].[sysdbmaintplan_jobs].[job_id]
INNER JOIN [msdb].[dbo].[sysdbmaintplan_databases]
ON [msdb].[dbo].[sysdbmaintplan_jobs].[plan_id] = [msdb].[dbo].[sysdbmaintplan_databases].[plan_id]
WHERE enabled = 1
ORDER BY [name]

PRINT '---------------------------------------------------'
PRINT ' BACKUP Schedule'
PRINT '---------------------------------------------------'

SELECT CONVERT(VARCHAR(30), plan_name) AS Plan_Name,
CONVERT(VARCHAR(20), Database_Name) AS Database_Name,
sj.name AS Job_Name,
sj.enabled,
CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly Relative'
WHEN 64 THEN 'SQL Server Restarts'
END AS Frequency,
CASE freq_interval
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Sun, Mon'
WHEN 4 THEN 'Tues'
WHEN 5 THEN 'Sun, Tues'
WHEN 6 THEN 'Mon, Tues'
WHEN 7 THEN 'Sun, Mon, Tues'
WHEN 8 THEN 'Wed'
WHEN 9 THEN 'Sun, Wed'
WHEN 10 THEN 'Mon, Wed'
WHEN 11 THEN 'Sun, Mon, Wed'
WHEN 16 THEN 'Thur'
WHEN 32 THEN 'Fri'
WHEN 62 THEN 'Mon - Fri'
WHEN 64 THEN 'Sat'
WHEN 126 THEN 'Mon - Sat'
END AS "Weekday(s)",
active_start_time/10000 AS "start time"
FROM msdb..sysdbmaintplans smp
LEFT JOIN msdb..sysdbmaintplan_databases smpd ON smp.plan_id = smpd.plan_id
LEFT JOIN msdb..sysdbmaintplan_jobs smpj ON smp.plan_id = smpj.plan_id
LEFT JOIN msdb..sysjobs sj ON smpj.job_id = sj.job_id
LEFT JOIN msdb..sysjobschedules sjs ON sj.job_id = sjs.job_id
WHERE Plan_Name <> 'All ad-hoc plans'
ORDER BY [Job_Name]


If someone has something similar for 2005 I would really appreciate it being posted.

 

[mrdenny]

Run sp_help_job. It'll tell you what jobs are setup on the server.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog