Tag Archives: sql server agent jobs

Get list of SQL Server Jobs from multiple instance via SSIS package

Get list of SQL Server Jobs from multiple instance via SSIS package

While managing multiple instance of SQL Server, it is little bit difficult to keep watch on every jobs running in every SQL Server Instances we are managing. I have created one SSIS package to list SQL Server Job of all instance I used to manage and insert that list in one database so that anytime I have list ready if any one ask me about anything related to job.

If I have list of jobs with along with its schedule, I can query every SQL Server instance to check whether every job of the instance ran or not. If any job is not running, I can send automated email with list of “Missing Jobs”.

I will cover complete sequence in two article. This article will show you how to prepare a list of all SQL Server Job from multiple instance.

We might change schedule of certain jobs, we might add/remove some of the jobs so it is necessary to have perfect updated list of jobs so the SSIS package I am going to run in this article, would be scheduled to run everyday and gather the latest/updated list of jobs from all instance and insert it in one database of one instance.

Let us start creating foundation for this SSIS package.

I used to have one database called “DBAdb” in one of the instance of SQL Server and I used to collect DBA related data in “DBAdb” database only so that none of the production database becomes big due to DBA related activity. Apart from “DBAdb” database, we need two tables inside “DBAdb” database.

1.) SQLServerInstance: this table will have details of all SQL Server instance and this will be the base table for our package as our package read detail of SQL Server Instance from this table and collect the JOB details from that particular instance.

2.) JobScheduleDetails: This table will have detail of each job along with its schedule which are collected from the each instance we have listed in “SQLServerInstance” table.

I will have only one entry in “SQLServerInstance” table as I am preparing this article from my laptop but you can have as many entries as you want. Package will be created dynamically which will iterate for each server we list.

Here is the TSQL to create database and tables we have listed above.

CREATE DATABASE DBAdb
GO

USE [DBAdb]
GO

CREATE TABLE [dbo].[SQLServerInstances](
[Sid] [INT] IDENTITY(1,1) NOT NULL,
[ServerName] [VARCHAR](100) NULL,
[ConnectionString] [VARCHAR](1000) NULL,
[IsActive] [bit] NULL,
[GetJobSchedules] [bit] NULL
) ON [PRIMARY]

INSERT INTO SQLServerInstances
--you have to give your servername alongwith its connection string
SELECT 'USER12\SQL2K12DEV','Data Source=USER12\SQL2K12DEV;Initial Catalog=msdb;Provider=SQLNCLI10.1;INTegrated Security=SSPI;Auto Translate=False;',1,1
GO

--Create table in centralize "DBADb" database in one instance
--which will collect information of job from all servers.
USE [DBAdb]
GO

CREATE TABLE [dbo].[JobSchedulesDetails](
[Seq] [INT] IDENTITY(1,1) NOT NULL,
[ServerName] [VARCHAR](100) NULL,
[JobId] [uniqueidentifier] NULL,
[JobName] [VARCHAR](500) NULL,
[IsEnabled] [VARCHAR](10) NULL,
[Occurrence] [VARCHAR](200) NULL,
[JobSchedule] [VARCHAR](4000) NULL,
[FrequencyType] [VARCHAR](100) NULL,
[Frequency] [INT] NULL,
[StartTime] [VARCHAR](20) NULL,
[EndTime] [VARCHAR](20) NULL,
[StartDateTime] AS (CONVERT([DATETIME],(CONVERT([CHAR](8),getdate(),(112))+' ')+[StartTime],0)),
[EndDateTime] AS (CONVERT([DATETIME],(CONVERT([CHAR](8),getdate(),(112))+' ')+[EndTime],0)),
[CategoryId] [INT] NULL
) ON [PRIMARY]
GO

Now we are ready with information we want so let us start creating SSIS package.

1.) Create three variables for the package as given in following screen capture.

1DeclareVariable

2.) Get one “Execute SQL Task” in “Control Flow” of your package.

2ExecuteTaskForServerList

I have used following query in “Execute SQL Task”.

SELECT
ServerName,
ConnectionString
FROM
dbo.SQLServerInstances
WHERE
IsActive = 1
AND
GetJobSchedules =1

3.) Above query will return list of SQL Server Instance where we need to collect job details from so we are going to capture result set in one of the variable we have created. Go to “Result Set” tab in Execute SQL Task as given below and following the screen capture.

3ResultSetinVariable

4.)  Take one “Foreach Loop Container” and join it with previously created “Execute SQL Task”. set properties of Foreach task as given in screen capture.

4ForEachLoopFromVariable

5.) need to set other variables for data in Foreach task. see following screen capture.

5ForEachLoopVariableMapping

6.) Since we need latest and updated detail of JOB in master table, we are going to delete old job detail from the table before we insert new details via “Execute SQL Task”.

6DeleteOldRecordOfJobScheduleForServer

Here is the query I have used in above Execute SQL Task.

DELETE FROM DBAdb.dbo.JobSchedulesDetails
WHERE ServerName  = ?

7.) Now take one “Data Flow” task and connect it with “Execute SQL Task” we created to delete data for old job detail before we capture fresh data.

7DataFlowTask

8.) As soon as you double click on newly created “Data Flow” task, you will be forwarded to “Data Flow” tab and you will have to have three task there. 1.) OLE DB Source 2.) Data Conversion 3.) OLE DB Destination.

8DataFlowInsight

OLEDB Source task here plays very crucial role. Following is the query I have used to read job details.

SELECT
cast(SERVERPROPERTY ('servername')  as nvarchar(200)) as ServerName,
a.job_id as JobId,
a.name as JobName,
a.Category_id as CategoryId,
case when a.Enabled = 1 then 'Enable' else 'Disable' end as JobStatus,
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 ) +
(CASE [freq_subday_type]
WHEN 1 THEN ' once at '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 2 THEN ' 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 ' 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 ' 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 ) as JobSchedule,

(CASE [freq_subday_type]
WHEN 1 THEN 'Once'
WHEN 2 THEN 'Second(s)'
WHEN 4 THEN 'Minute(s)'
WHEN 8 THEN 'Hour(s)'
END ) as 'Every',

(CASE [freq_subday_type]
WHEN 1 THEN NULL
WHEN 2 THEN CAST([freq_subday_interval] AS VARCHAR(3))
WHEN 4 THEN CAST([freq_subday_interval] AS VARCHAR(3))
WHEN 8 THEN CAST([freq_subday_interval] AS VARCHAR(3))
END ) as 'Between',

(CASE [freq_subday_type]
WHEN 1 THEN  STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 2 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')

WHEN 4 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')

WHEN 8 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')

END ) as StartTime ,

(CASE [freq_subday_type]
WHEN 1 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 2 THEN  STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 4 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 8 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
END ) as EndTime
FROM
[msdb].[dbo].[sysjobs] a
INNER JOIN [msdb].[dbo].[sysjobschedules] b
ON (b.job_id = a.job_id)
INNER JOIN [msdb].[dbo].[sysschedules] c
ON (c.schedule_id = b.schedule_id)
ORDER BY a.name

9.) We may have one or multiple instance where we need to collect this information from so we have to connect our OLE DB Source Task dynamically with different server instance. First use the query I gave above and create regular SQL Server connection. Once you are done, go to property of that connection task to change the name and give it dynamic connection string for different server. We have dynamic connection string coming in Foreach Loop and we have mapped that connection string in “ConnectionString” variable we have created in very first steps above.

9DynamicConnection

10.) Connect one “Data Conversion” task with OLE DB Source and see the property I have set in following screen capture.

10DataConversion

Now, we have read job details from server. Now, this is time to insert that detail in “DBAdb” database so that we can use it for our purpose.

10.) set the property of “OLE DB Destination” after connecting it to “Data Conversion Task”.

11OLEDBDestination

11.) We have to map column from source and destination in “OLE DB Destination” task.

12Mapping

Now you are ready to run. Execute the package and see its effect. If the servers you have inserted in “SQLServerInstance” table is having SQL Server Job, you will find job details in your “[JobSchedulesDetails]” table.

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)
  • Get list of failed SQL Server Agent job in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find SQL Server agent job ran on specific date with its status

It is very mandatory for SQL Server DBA to keep an eye on the SQL Server Job and take an immediate action if job gets failed. There are several ways of doing this. I will be showing one query which will return all jobs ran on the specific date along with its status like whether it was failed or succeed.
In order to gain information regarding JOBs, we have to query following list of system tables in MSDB database because all SQL Server jobs are stored in MSDB database only.
1.)     Sysjobhistory
2.)    Sysjobs
3.)    sysjobsteps
Here is the query which will return the desired result:
USE MSDB
GO

DECLARE @Today AS VARCHAR(8)
SET @Today = CONVERT(VARCHAR(8),GETDATE(),112)
SELECT * FROM (
SELECT
SysJob.name,
CASE WHEN  JobHist.run_status =1
THEN 'Success'
WHEN JobHist.run_status =0
THEN 'Fail'
END AS JobRunStatus,
JobHist.run_date,
JobHist.run_time,
JobHist.run_duration AS RunDuration,
JobStep.step_id,
JobStep.command,ROW_NUMBER() OVER(PARTITION BY SysJob.name,JobStep.step_id ORDER BY run_time DESC) AS NumberOfExecution
FROM
dbo.sysjobhistory AS jobHist
INNER JOIN
dbo.sysjobs AS SysJob
ON
JobHist.job_id = SysJob.job_id
INNER JOIN
dbo.sysjobsteps AS JobStep
ON
(JobStep.job_id = SysJob.job_id)
WHERE
JobHist.run_date = @Today
)
AS T
WHERE
NumberOfExecution=1
As a DBA, I keep this script handy as I never know when I will need this. We already have monitoring over each and every jobs and failure of any jobs will be notified to me via email though this has become time saving for me so many times.
One tip I would like to give especially when I am talking about JOB is, keep regular backup policy for MSDB database too as all the JOBs are being stored here and while restoring SQL Server from any disaster, we need to create all the JOBs again and MSDB database backup become life savior in that case.
I have explained the importance of backup in one of my past article, if you are interested to read, click here.
Happy Scripting!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

List SQL Server Agent JOBS in SQL Server 2008/2005/2000

As a DBA we may need to set the JOB for various task and those tasks will be saved under MSDB database. You can see it from EM or from SSMS GUI tools but it would help sometime to execute query to see the list of available JOB in SQL Server 2000+ versions.
There are two ways to achieve that task. Have a look at it.
–with stored procedure
EXEC MSDB..sp_HELP_JOB


–with sysjobs view
select * from msdb..sysjobs



Both of the above statements will give you list of JOB have been set in SQL Server Agent along with so many other important details.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah