 |
|
Querying SQL Server Agent Job Information
|
By: Dattatrey Sindol | Read Comments (9) | Print Datta has over 5 years of experience in the IT industry working with SQL Server, SSIS, SSAS, SSRS. Related Tips: More |
|
Problem
I am a BI developer and have been moved recently to a DBA role. Among the many tasks that I am responsible for, two major ones are: monitoring the SQL Server Agent Jobs running on a set of servers by creating a few standard SSRS reports and creating a Reference Manual/One Stop Guide containing the details of jobs on each of the servers. As I am new to the DBA role, to start with I would like to know some basics of what are the various SQL Server Agent Job related details which need to be reported as part of reports/documentation. Also, is there an easy/automated way to get this information instead of connecting to each server and manually getting the information through the SSMS GUI.
Solution
It is a common scenario in IT the industry where people move from a dev/test role to a DBA role and vice-versa due to various reasons. It is very essential for the person to be able to adapt easily when he/she is moved to a different role. SQL Server is known for its usability and is one of the most liked tools in the community because of its user friendliness. In this tip, we will see how to query some basic job monitoring and job setup information. The intention of this tip is to act as a good starting point for people who are new to the DBA role and for those who need to monitor the SQL Server Agent Jobs.
Before we get into querying the required information, let's get a basic understanding of msdb database. msdb is a SQL Server System Database and 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, etc.
- Service Broker, Log Shipping, database backups and restore information, Maintenance Plan, Database Mail, Policy Based Information of SQL Server 2008, etc.
- SQL Server Integration Services (SSIS) packages stored in Integration Services.
In this tip we will retrieve the following information related to SQL Server Agent Jobs by querying some of the system tables in the msdb database:
- Job Setup/Configuration Information
- Job Execution Information
- Job Step(s) Setup/Configuration Information
- Job Step(s) Execution Information
- Schedule Information
SQL Server Agent Job Setup and Configuration Information
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. Irrespective of the approach used, this information is stored in a set of SQL Server System Tables present in the msdb database.
The following query gives us the Job Level Setup and Configuration information which is also found in the SQL Server Agent Job Properties window in SSMS.
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]
, [sCAT].[name] AS [JobCategory]
, [sJOB].[description] AS [JobDescription]
, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, [sJOB].[date_created] AS [JobCreatedOn]
, [sJOB].[date_modified] AS [JobLastModifiedOn]
, [sSVR].[name] AS [OriginatingServerName]
, [sJSTP].[step_id] AS [JobStartStepNo]
, [sJSTP].[step_name] AS [JobStartStepName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]
, [sSCH].[schedule_uid] AS [JobScheduleID]
, [sSCH].[name] AS [JobScheduleName]
, CASE [sJOB].[delete_level]
WHEN 0 THEN 'Never'
WHEN 1 THEN 'On Success'
WHEN 2 THEN 'On Failure'
WHEN 3 THEN 'On Completion'
END AS [JobDeletionCriterion]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]
The following is a brief description of each of the fields returned from the above query:
- [JobID]: A unique identifier for the SQL Server Agent job (GUID).
- [JobName]: Name of the SQL Server Agent job.
- [JobOwner]: Owner of the job.
- [JobCategory]: Category to which the job belongs like Replication Snapshot, Database Maintenance, Log Shipping, etc.
- [JobDescription]: Description of the job.
- [IsEnabled]: Indicator representing whether the job is enabled or disabled.
- [JobCreatedOn]: Date and time when the job was created.
- [JobLastModifiedOn]: Date and time when the job was last modified.
- [OriginatingServerName]: Server from which the job executed.
- [JobStartStepNo]: Step number from which the job is set to start. SQL Server allows us to have multiple steps within a job and the job can be set to start from whichever step the user wants it to start from.
- [JobStartStepName]: Name of the step from which the job is set to start.
- [IsScheduled]: Indicator representing whether the job is scheduled or not. The jobs can be either scheduled to run on specified day(s) at a specified time or can be invoked through code like T-SQL, etc.
- [JobScheduleID]: Unique identifier of the schedule associated with the job (GUID).
- [JobScheduleName]: Name of the schedule associated with the job. SQL Server allows us to associate multiple schedules with one job, in which case, the above query would return one row for each schedule associated with each job.
- [JobDeletionCriterion]: The criterion for deleting the job. SQL Server Agent has a feature which allows us to delete/drop the job based on a certain criterion so that there is no need to manually delete/cleanup the jobs.
SQL Server Agent Job Execution Information
SQL Server Agent stores the history of job execution in system tables in msdb database.
The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled). This information can also be found in the Job History/Job Activity Monitor windows in SSMS.
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, CASE
WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(
CAST([sJOBH].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
, CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [LastRunStatus]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
, [sJOBH].[message] AS [LastRunStatusMessage]
, CASE [sJOBSCH].[NextRunDate]
WHEN 0 THEN NULL
ELSE CAST(
CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [NextRunDateTime]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN (
SELECT
[job_id]
, MIN([next_run_date]) AS [NextRunDate]
, MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]
) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN (
SELECT
[job_id]
, [run_date]
, [run_time]
, [run_status]
, [run_duration]
, [message]
, ROW_NUMBER() OVER (
PARTITION BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC
) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]
The following is a brief description of each of the fields returned from the above query:
- [JobID]: A unique identifier for the SQL Server Agent job (GUID) for which the execution history is being reported.
- [JobName]: Name of the SQL Server Agent job.
- [LastRunDateTime]: Date and time when the job was run last time (corresponds to the most recent run).
- [LastRunStatus]: Status or outcome of the last job run.
- [LastRunDuration (HH:MM:SS)]: Duration of the last run represented in Hours:Minutes:Seconds format.
- [LastRunStatusMessage]: More descriptive message about the job status/outcome.
- [NextRunDateTime]: Date and time when the job will run next time. This information is available only for the jobs which are scheduled (a schedule is associated with a job).
SQL Server Agent Job Steps Setup and Configuration Information
In SQL Server Agent, a job is the parent level entity, which contains one or more steps. A step is the child/low level entity, which contains the actual commands/instructions for performing a specific task. Whereas a job defines the sequence of execution of steps, schedule for running steps, etc.
The following query gives us the Job Step level Setup and Configuration information, which can also be found in the Job Step Properties window in SSMS.
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sJSTP].[step_uid] AS [StepID]
, [sJSTP].[step_id] AS [StepNo]
, [sJSTP].[step_name] AS [StepName]
, CASE [sJSTP].[subsystem]
WHEN 'ActiveScripting' THEN 'ActiveX Script'
WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
WHEN 'PowerShell' THEN 'PowerShell'
WHEN 'Distribution' THEN 'Replication Distributor'
WHEN 'Merge' THEN 'Replication Merge'
WHEN 'QueueReader' THEN 'Replication Queue Reader'
WHEN 'Snapshot' THEN 'Replication Snapshot'
WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
ELSE sJSTP.subsystem
END AS [StepType]
, [sPROX].[name] AS [RunAs]
, [sJSTP].[database_name] AS [Database]
, [sJSTP].[command] AS [ExecutableCommand]
, CASE [sJSTP].[on_success_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
+ ' '
+ [sOSSTP].[step_name]
END AS [OnSuccessAction]
, [sJSTP].[retry_attempts] AS [RetryAttempts]
, [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
, CASE [sJSTP].[on_fail_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
+ ' '
+ [sOFSTP].[step_name]
END AS [OnFailureAction]
FROM
[msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
ON [sJSTP].[job_id] = [sJOB].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
ON [sJSTP].[job_id] = [sOSSTP].[job_id]
AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
ON [sJSTP].[job_id] = [sOFSTP].[job_id]
AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
ORDER BY [JobName], [StepNo]
The following is a brief description of each of the fields returned from the above query:
- [JobID]: A unique identifier for the SQL Server Agent job (GUID) to which the step(s) belongs.
- [JobName]: Name of the SQL Server Agent job.
- [StepID]: A unique identifier for the SQL Server Agent Job Step (GUID).
- [StepNo]: Sequence number of the step representing the position of the step in the job.
- [StepName]: Name of the SQL Server Agent Job Step.
- [StepType]: Subsystem/Type of the Job Step like SQL Server Integration Services Package, Transact-SQL Script (T-SQL), ActiveX Script etc.
- [RunAs]: Account under which the job step should be run/executed. This will contain a value in the above query output only when run through a proxy.
- [Database]: Name of the database in which the command is executed. This applies only when the Step Type is Transact-SQL Script (T-SQL).
- [ExecutableCommand]: The actual command which will be executed by the subsystem.
- [OnSuccessAction]: Action to be taken by SQL Server Agent when the job step succeeds.
- [RetryAttempts]: Number of retry attempts made by SQL Server Agent in case the job step fails.
- [RetryInterval (Minutes)]: Time interval in minutes between each retry attempt in case the job step fails and SQL Server Agent tries to re-run it.
- [OnFailureAction]: Action to be taken by SQL Server Agent when the job step fails.
SQL Server Agent Job Steps Execution Information
SQL Server Agent stores the history of the execution of each of the job steps in system tables in msdb database.
The following query gives us the details of last/latest execution of the job step. This information can also be found in the Job History/Log File Viewer windows in SSMS.
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sJSTP].[step_uid] AS [StepID]
, [sJSTP].[step_id] AS [StepNo]
, [sJSTP].[step_name] AS [StepName]
, CASE [sJSTP].[last_run_outcome]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 5 THEN 'Unknown'
END AS [LastRunStatus]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
, [sJSTP].[last_run_retries] AS [LastRunRetryAttempts]
, CASE [sJSTP].[last_run_date]
WHEN 0 THEN NULL
ELSE
CAST(
CAST([sJSTP].[last_run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
FROM
[msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
ON [sJSTP].[job_id] = [sJOB].[job_id]
ORDER BY [JobName], [StepNo]
The following is a brief description of each of the fields returned from the above query:
- [JobID]: A unique identifier for the SQL Server Agent job (GUID) to which the step(s) belongs.
- [JobName]: Name of the SQL Server Agent job.
- [StepID]: A unique identifier for the SQL Server Agent Job Step (GUID).
- [StepNo]: Sequence number of the step representing the position of the step in the job.
- [StepName]: Name of the SQL Server Agent Job Step.
- [LastRunStatus]: Status or Outcome of the step execution when the job/step executed last time.
- [LastRunDuration (HH:MM:SS)]: Duration of the last run represented in Hours:Minutes:Seconds format.
- [LastRunRetryAttempts]: Number of retry attempts made by SQL Server Agent during the last run of the job step.
- [LastRunDateTime]: Date and time when the job step was last run.
SQL Server Agent Job Schedule Information
SQL Server allows creating schedules for performing various tasks at a specified date and time. This can be a one time schedule or a recurring schedule with or without an end date. Each schedule can be associated with one or more SQL Server Agent Jobs.
The following query gives us the list of schedules created/available in SQL Server and the details (Occurrence, Recurrence, Frequency, etc.) of each of the schedules.
SELECT
[schedule_uid] AS [ScheduleID]
, [name] AS [ScheduleName]
, CASE [enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, 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(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]
, [date_created] AS [ScheduleCreatedOn]
, [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]
The following is a brief description of each of the fields returned from the above query:
- [ScheduleID]: Unique identifier of the schedule (GUID).
- [ScheduleName]: Name of the schedule. SQL Server allows one schedule to be associated with more than one job.
- [IsEnabled]: Indicator representing whether a schedule is enabled or disabled.
- [ScheduleType]: The type of the schedule.
- [Occurrence]: Occurrence of the schedule like Daily, Weekly, Monthly, etc.
- [Recurrence]: Recurrence of the schedule like specific day(s), Specific Days of the Week, Number of weeks, etc.
- [Frequency]: How frequently the job should run on the day(s) when it is scheduled to run such as: Occurs only once on the scheduled day(s), Occurs every 2 hours on the scheduled day(s) etc. between specified start and end times.
- [ScheduleUsageStartDate]: Effective start date from when the schedule will be used.
- [ScheduleUsageEndDate]: Effective end date after which the schedule will not be used.
- [ScheduleCreatedOn]: Date and time when the schedule was created.
- [ScheduleLastModifiedOn]: Date and time when the schedule was last modified.
Next Steps
- Review the scripts in this tip and start extending them to meet your monitoring and documentation requirements.
- Write scripts to get the following information:
- Get the list of long running jobs
- Get the list of jobs scheduled to run in next 24/48 hours
- Get the list of jobs failed in last 24/48 hours
- Check out other SQL Server Agent related tips
- Check out these additional resources:
Last Update: 12/9/2011
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Friday, December 09, 2011 - 8:44:27 AM - PJ | Read The Tip |
|
Great set of scripts. |
|
| Friday, December 09, 2011 - 11:57:43 AM - The Happy DBA | Read The Tip |
|
Amazing information! Very useful, very complete! Thanks a lot! |
|
| Monday, January 02, 2012 - 11:09:45 AM - Stephane Thinel | Read The Tip |
|
I will keep this for future references. Thank you! |
|
| Tuesday, January 03, 2012 - 3:27:31 PM - Willis Johnson | Read The Tip |
|
Exceptionally helpful. Kudos! |
|
| Saturday, January 28, 2012 - 5:53:26 AM - Dattatrey Sindol | Read The Tip |
|
Thanks everyone for the feedback!
Best Regards, Dattatrey Sindol (Datta) |
|
| Tuesday, March 27, 2012 - 8:47:18 AM - Prasana | Read The Tip |
|
Good one man :) |
|
| Tuesday, April 03, 2012 - 11:59:20 PM - Balaji | Read The Tip |
|
Can anyone please help me!!! Now we monitoring the jobs by manually.Am trying to monitor it automatically.So i built query to find status of all jobs.And my query finds well all the status of jobs such as(completed,failed,cancelled,rerunned) but except skipped jobs.Yes my query couldn't find the skipped jobs details from the history.
Skipped jobs means all jobs scheduled to execute in a particular time but unfortunately some jobs may not getting executed at the scheduled time.So also I need to retrieve these criteria records in my automation query.Because of this issue my task was in pending status for the past 3 months.Please give ur assisstance if any1 knows,
|
|
| Monday, April 09, 2012 - 5:28:17 AM - Dattatrey Sindol | Read The Tip |
|
Hi Balaji, For your scenario, you can approach something like this. Hope that helps. Best Regards, |
|
| Saturday, May 26, 2012 - 6:48:51 PM - India | Read The Tip |
|
can you help me with query to list all the ssis packages executed between a time / stamp and also some more details like recurring every 5 mins / 1 hr etc., |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |