Use the resource timeout option to specify the number of seconds to wait for a resource to be released. resource timeout controls the overall time out of asynchronous input/output (I/O) operations. The default setting is 10; any operation that takes longer than 10 seconds to complete results in a time-out warning.
Do not set this value too low or resources may time out in an unrealistically short time. Increase this value if the Microsoft® SQL Server™ error log shows a lot of logwrite or bufwait time-out warnings; this reduces the frequency with which these errors are generated. Allowing a transaction to take more time is often better than allowing it to fail.
resource timeout is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change resource timeout only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).
To set the resource timeout option
RECONFIGURE | Setting Configuration Options |
sp_configure | Thread and Fiber Execution |