Introduction and a little background
Recently I was assigned a task to create a Jobs Inventory file containing the details of SQL Agent jobs and tasks scheduled in Windows Scheduler on each of the servers. I wanted to extract this information from the system through a script rather than having to use the GUI and going through each individual item to extract the required information.
Windows scheduled tasks
The DOS command schtasks holds the key for working with Windows scheduler jobs. The command has a number of parameters, but for the purpose of accomplishing my task, the /query parameter will be used.
Open the command prompt, and type the following command:
schtasks /query
It returns a list of all the scheduled tasks, including Task Name, Next Run Time and Status. The /query parameter can be used in a number of ways. It has its own list of parameters that it can be used along with. To see the complete list of parameters for /query, use the following command:
schtasks /query /?
Since for my task I wanted a bit more detail about the scheduled tasks, specifically the file path of what was scheduled, so I used the command as follows:
schtasks /query /v /fo LIST
This provided a detailed list of all the scheduled tasks. It included the additional information that I required, such as the task being run, schedule information, etc. But the resultant information was in list format and it is difficult to apply any kind of filter on it.
To use the data in a better way and to filter it as per my requirements, I needed to get the output in CSV format. For this, I used the following command:
schtasks /query /v /fo CSV
This produced the same result set in a columnar format and saved it as a CSV file at the given path. Using the CSV file with MS Excel, I was able to manipulate data easily using Excel’s different features, like sorting and filtering etc.
schtasks /query /v /fo CSV > D:\tasks.csv
Finally, if you want to extract similar data for a remote machine, you can do so by using the command as follows:
schtasks /query /S SERVERNAME /U username /P password /FO CSV /V > exportedjobs.csv
SQL Agent jobs
In this section, I will show how to obtain similar information for SQL Agent jobs.
SQL Server allows creation of jobs for performing various tasks in an automated fashion like Replication, running SSIS Packages, Stored Procedures, Batch Commands, etc. These jobs can be created either using SSMS GUI or using T-SQL queries. The details of these jobs are stored in different tables of MSDB system database.
MSDB database
The MSDB is a SQL Server system database which is used to store the Configuration, Processing, & Metadata information related to the SQL Server features, including: SQL Server Agent Jobs, Job Steps, Job schedules, Alerts, Operators, database backups and restore information, Maintenance Plan and Database Mail etc.
Script for retrieving SQL Server Agent Job data
The following query gives us the Job Level Setup and Configuration information for all SQL Server Agent Jobs. This information can also be found in the SQL Server Agent Job Properties window in SSMS.
| SELECT HOST_NAME() as ServerName
,j.[Name] AS [JobName] , CASE j.[enabled] WHEN 1 THEN ‘Yes’ WHEN 0 THEN ‘No’ END AS [IsJobEnabled] ,CASE WHEN c.name = ‘[Uncategorized (Local)]’ THEN ” ELSE c.name END ‘Category’ , CASE j.[description] WHEN ‘No Description available.’ THEN ” ELSE [description] END AS ‘Description’ , s.[name] AS [ScheduleName] , CASE s.[enabled] WHEN 1 THEN ‘Yes’ WHEN 0 THEN ‘No’ END AS [IsScheduleEnabled] , CASE WHEN [freq_type] = 64 THEN ‘Start automatically when SQL Server Agent starts’ WHEN [freq_type] = 128 THEN ‘Start whenever the CPUs become idle’ WHEN [freq_type] IN (4,8,16,32) THEN ‘Recurring’ WHEN [freq_type] = 1 THEN ‘One Time’ END [ScheduleType] , CASE [freq_type] WHEN 1 THEN ‘One Time’ WHEN 4 THEN ‘Daily’ WHEN 8 THEN ‘Weekly’ WHEN 16 THEN ‘Monthly’ WHEN 32 THEN ‘Monthly – Relative to Frequency Interval’ WHEN 64 THEN ‘Start automatically when SQL Server Agent starts’ WHEN 128 THEN ‘Start whenever the CPUs become idle’ END [Occurrence] , CASE [freq_type] WHEN 4 THEN ‘Occurs every ‘ + CAST([freq_interval] AS VARCHAR(3)) + ‘ day(s)’ WHEN 8 THEN ‘Occurs every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ week(s) on ‘ + CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday’ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘, Monday’ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘, Tuesday’ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘, Wednesday’ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘, Thursday’ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘, Friday’ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘, Saturday’ ELSE ” END WHEN 16 THEN ‘Occurs on Day ‘ + CAST([freq_interval] AS VARCHAR(3)) + ‘ of every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ month(s)’ WHEN 32 THEN ‘Occurs on ‘ + CASE [freq_relative_interval] WHEN 1 THEN ‘First’ WHEN 2 THEN ‘Second’ WHEN 4 THEN ‘Third’ WHEN 8 THEN ‘Fourth’ WHEN 16 THEN ‘Last’ END + ‘ ‘ + CASE [freq_interval] WHEN 1 THEN ‘Sunday’ WHEN 2 THEN ‘Monday’ WHEN 3 THEN ‘Tuesday’ WHEN 4 THEN ‘Wednesday’ WHEN 5 THEN ‘Thursday’ WHEN 6 THEN ‘Friday’ WHEN 7 THEN ‘Saturday’ WHEN 8 THEN ‘Day’ WHEN 9 THEN ‘Weekday’ WHEN 10 THEN ‘Weekend day’ END + ‘ of every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ month(s)’ END AS [Recurrence] , CASE [freq_subday_type] WHEN 1 THEN ‘Occurs once at ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) WHEN 2 THEN ‘Occurs every ‘ + CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Second(s) between ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) + ‘ & ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) WHEN 4 THEN ‘Occurs every ‘ + CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Minute(s) between ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) + ‘ & ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) WHEN 8 THEN ‘Occurs every ‘ + CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Hour(s) between ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) + ‘ & ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) END [Frequency] ,STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) as active_start_time ,STUFF( STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) as active_end_time , STUFF( STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, ‘-‘) , 8, 0, ‘-‘) AS [ScheduleUsageStartDate] , STUFF( STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, ‘-‘) , 8, 0, ‘-‘) AS [ScheduleUsageEndDate] , s.[date_created] AS [ScheduleCreatedOn] , s.[date_modified] AS [ScheduleLastModifiedOn] ,STUFF( STUFF(CAST(h.Last_Run_Date AS VARCHAR(8)), 5, 0, ‘-‘) , 8, 0, ‘-‘) AS [Last_Run_Date]
FROM msdb..sysjobs j LEFT JOIN msdb..sysJobschedules js ON j.job_id = js.job_id LEFT OUTER JOIN [msdb].[dbo].[sysschedules] s ON js.Schedule_id = s.Schedule_id LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id LEFT OUTER JOIN (SELECT Job_Id, MAX(Run_Date) as Last_Run_Date FROM msdb..sysjobhistory GROUP BY Job_Id) h On h.Job_id = j.Job_id ORDER BY j.Name
|
Tayyab Siddiqui 