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

Ok, this is usually seen when reading Job history details through SSMS. So what are your options?

One way is to go and see how many records you have in the history table. It is usually safe to remove some which go back several years:

EXEC sp_purge_jobhistory @oldest_date = @JobHistoryDeleteDate

You could also use a filter on the SSMS window to return a range of dates. Perhaps you only want to know if the job was successful yesterday. In SQL 2005, you can only graphically select a date, but not the time. Instead, open the same in SQL 2008 and now you can specify a time also.

Now if you are really short of time and you need the results, you have two approaches. Either write a complex query to get a quick look at the details. Or else, create an index on the sysjobhistory system table to get the details fast. Lets look at the index first :

CREATE NONCLUSTERED INDEX IX_SysJobHistory
ON [dbo].[sysjobhistory] ([job_id])
INCLUDE ([instance_id],[step_id],[sql_message_id],[sql_severity],
[run_status],[run_date],[run_time],[run_duration],[operator_id_emailed],
[operator_id_netsent],[operator_id_paged],[retries_attempted],[server])

The code above returned more than 50k records without any filters. Thx, @SQLFool ! ref: http://sqlfool.com/2009/04/a-look-at-missing-indexes/

The other approach is to write a lengthy code :

SELECT top 10

CONVERT(DATETIME, RTRIM(run_date)) +

((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime

, CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)

+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime,

server,

step_id, step_name, message,

case run_status

when 0 then ‘Failed’

when 1 then ‘Succeeded’

when 2 then ‘Retry’

when 3 then ‘Cancelled’

when 4 then ‘In Progress’

end as Run_Status

FROM msdb.dbo.sysjobhistory

where job_id = ‘50947C95-DE44-4BD7-AA06-92D419004F5F’

and step_name = ‘(Job outcome)’

order by run_date desc

Code courtesy Timothy J Hartford :

http://www.sqlservercentral.com/Forums/Topic542581-145-1.aspx

Hopefully, one of these will work for you!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

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

  1. rajvision says:

    Update : If the job history still times out, rebuild the index and it will work fine.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s