SQL Server: Timeout When Viewing SQL Server Agent Job History

Too many times when I try to look at the history of a SQL Server Agent Job, I get this error:

TITLE: .Net SqlClient Data Provider
——————————

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.2210&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

——————————
ADDITIONAL INFORMATION:

The wait operation timed out

——————————
BUTTONS:

OK
——————————

This has a knack to popup at the most critical times when I want to troubleshoot something quick and can be quite annoying. To avoid this, I captured the SQL run by the Agent Job History Viewer and is below. You can run this in SSMS and it will not timeout.

You can also add additional conditions to limit it on say the “Message” column if you are looking for something specific. You could also adjust the various optional parameters available in sp_help_jobhistory that are not shown in the call below.

Substitute your Agent Job Name in the highlighted line below:

DECLARE @jobId BINARY(16)

SELECT @jobId = CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs
WHERE name = 'YOUR_AGENT_JOB_NAME_GOES_HERE'

declare @tmp_sp_help_jobhistory table
(
    instance_id int null,
    job_id uniqueidentifier null,
    job_name sysname null,
    step_id int null,
    step_name sysname null,
    sql_message_id int null,
    sql_severity int null,
    message nvarchar(4000) null,
    run_status int null,
    run_date int null,
    run_time int null,
    run_duration int null,
    operator_emailed sysname null,
    operator_netsent sysname null,
    operator_paged sysname null,
    retries_attempted int null,
    server sysname null
)

insert into @tmp_sp_help_jobhistory
exec msdb.dbo.sp_help_jobhistory
    @job_id = @jobId,
    @mode='FULL' 

SELECT
    tshj.instance_id AS [InstanceID],
    tshj.sql_message_id AS [SqlMessageID],
    tshj.message AS [Message],
    tshj.step_id AS [StepID],
    tshj.step_name AS [StepName],
    tshj.sql_severity AS [SqlSeverity],
    tshj.job_id AS [JobID],
    tshj.job_name AS [JobName],
    tshj.run_status AS [RunStatus],
    CASE tshj.run_date WHEN 0 THEN NULL ELSE
    convert(datetime,
            stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
            stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
            120) END AS [RunDate],
    tshj.run_duration AS [RunDuration],
    tshj.operator_emailed AS [OperatorEmailed],
    tshj.operator_netsent AS [OperatorNetsent],
    tshj.operator_paged AS [OperatorPaged],
    tshj.retries_attempted AS [RetriesAttempted],
    tshj.server AS [Server],
    getdate() as [CurrentDate]
FROM @tmp_sp_help_jobhistory as tshj
ORDER BY [InstanceID] ASC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s