This is a guess, but I would try enabling lazy schema validation for the linked server under heavy load:
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_name', @optname=N'lazy schema validation', @optvalue=N'true'
If this option is set to false, the default value, SQL Server checks
for schema changes that have occurred since compilation in ...
The description is not actually a value stored in the database. It is dynamically derived from the values in dbo.sysschedules. As per scsimon's answer, you can user sp_help_jobschedule, or just use sp_help_schedule within a cursor to get the description.
something like this.
CREATE TABLE #tmp
(schedule_id INT IDENTITY(1, 1) NOT NULL,
You can use sp_help_jobschedule which returns the schedule_description by calling another system proc, sp_get_schedule_description.
exec sp_help_jobschedule @job_name = 'Your Job Name', @include_description = 1
It'd probably be faster to roll this in a cursor instead of breaking apart that system proc which
The answer I put together was mostly from Querying SQL Server Agent Job Information by Dattatrey Sindol. I think part of Piotr's answer was close, but didn't exactly meet my requirements as the code below does. Thanks Piotr!
The final select joins both tables and has a lot of columns, but it shows the step number, etc. that failed.
The answer below met ...
You need to analyze data in the jobstephistory table.
You can use following query, it is slightly modified script from Analyzing SQL Agent Job and Job Step History in SQL Server by Atif Shehzad:
--Script # 1: To generate steps history of all jobs USE msdb GO
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS ...
Forget anything about DBTIMEZONE, it has no practical use. The only purpose of DBTIMEZONE is: It defines the (internal) time zone to store TIMESTAMP WITH LOCAL TIME ZONE values - nothing else. Thus you cannot modify it when you have a table with TIMESTAMP WITH LOCAL TIME ZONE column and such column contains data.
SYSTIMESTAMP (and SYSATE) is returned in ...
Scheduler jobs are timezone aware, so if you schedule job A at 13:00 +11:00 and job B at 13:05 'Australia/NSW' then job B will run 5 minutes after job A during the Australian summer and 55 minutes before job A during the Australian winter.
Use select owner, job_name, to_char(start_date,'TZR') tz from all_scheduler_jobs to determine what timezone you have ...
If you found the timezone of your database to be incorrect and you are sure this causes the issue (I have seen this before), you can change it with.
SQL> select property_value from database_properties where property_name = 'DBTIMEZONE';
You can set a named time zone like: