How to Increase Agent History Rendering Timeout in SSMS

sql serversql server 2014ssms

We keep only 30 days of SQL agent job history but due to the frequent execution of jobs, SSMS will experience the following timeout error when trying to view history for a given job:

Execution Timeout Expired.  
The timeout period elapsed prior to completion of the operation or the server is not responding. 
(.Net SqlClient Data Provider)

Is there a way to increase this timeout in SQL Management Studio 18.7.1?

Best Answer

No unfortunately it is not possible. As @Newman mentioned, you can query the msdb.dbo.sp_help_jobhistory procedure to get the data back or you can adjust your jobs timeframe cutoff or total number of rows cutoff (whichever is more suitable to your business needs): https://docs.microsoft.com/en-us/sql/ssms/agent/resize-the-job-history-log?view=sql-server-ver15

Related Solutions

SQL Server 2014 – Create Failed for Index ‘ClusteredColumnStoreIndex’. Timeout Expired

If you use Query Analyzer(QA) go to Tools -> Option -> Connection.Reset all the values using 'Reset to Default' Button.By default there is no time out on QA.

Or, write your CREATE INDEX or ALTER TABLE statement in the query window and run it.

Setting the time out in SQL Server to -1 will prevent time outs. It's possible that you are changing the time out setting, but it won't take effect until a RECONFIGURE is performed.

Run:

sp_configure 'show advanced options', 1;

...and look at the time out settings there. Take note as to if the time out setting is active in the run_value column. If not, then you need to do a...

RECONFIGURE;

...to activate the changed setting.

Sql-server – SQL Server Timeout Expired in ASP.NET C# WebForms application

Sounds like a poor performing query that is crossing over the default 30 second timeout for .net connections. SQL Server will process a query for as long as it takes, but the application is deciding to stop the show after 30 seconds.

You should fire up SQL Profiler and capture long running transactions (ones that have duration > 1 second) and transactions that require more than a few thousand reads.

Once you have a few of these queries isolated, you can begin to examine them for poor performance (look at indexes, joins, etc.)

Best Answer

Related Solutions

SQL Server 2014 – Create Failed for Index ‘ClusteredColumnStoreIndex’. Timeout Expired

Sql-server – SQL Server Timeout Expired in ASP.NET C# WebForms application

Related Question