|Message:||A time-out occurred while waiting for buffer latch — type %d, bp %p, page %d:%d, stat %#x, database id: %d, allocation unit Id: %I64d%ls, task 0x%p : %d, waittime %d, flags 0x%I64x, owning task 0x%p. Not continuing to wait.|
A computer might stop responding (hang), or a time-out or some other disruption of regular operations might occur at the same time that SQL Server writes buffer latch errors to the SQL Server error log.
If the stat field in the message has the value of 0x04 on, SQL Server is waiting for an I/O operation. You may also receive message MSSQLSERVER_833 in the SQL Server error log.
If the stat field in the message has the value 0x04 off, there is heavy contention for a page. If the object is a data page, this can be caused by inefficient code design. If the page is nondata, the error might be caused by server bottlenecks, such as insufficient hardware resources. For more information about SQL Server page types, see Pages and Extents.
To work around this problem, depending on your environment, one or more of the following steps might reduce or eliminate the error messages:
Determine whether you have any hardware bottlenecks. If it is necessary, upgrade your hardware so that it can support the configuration, query, and load requirements of your environment. For more information about bottlenecks, see Identifying Bottlenecks.
Check for any logged errors and run any diagnostics provided by your hardware vendor.
Make sure that your disk drives are not compressed. Storing data or log files on compressed drives is not supported. For more information about physical files, see Physical Database Files and Filegroups.
See whether the error messages disappear when you set the following options to off:
SQL Server priority boost configuration option
Lightweight pooling (fiber mode) option
Set working set size option
The previous settings can frequently be counter-productive if you change them from their default setting of OFF. For more information about the settings, see Setting Server Configuration Options and sp_dboption (Transact-SQL).
Tune queries to reduce resources used on the system. Performance tuning will help reduce the stress on a system and improve response time for individual queries. For more information about how to tune queries, see Query Tuning Recommendations.
Set the AUTO_SHRINK option to OFF to reduce the overhead of changes to the database size.
Make sure that you set the FILEGROWTH option to increments that are large enough to be infrequent. Schedule a job to check the available space in the databases, and then increase the database size during nonpeak hours.