Redaguoti

Bendrinti naudojant


MSSQLSERVER_846

Applies to: SQL Server

Details

Attribute Value
Product Name SQL Server
Event ID 846
Event Source MSSQLSERVER
Component SQLEngine
Symbolic Name N/A
Message Text 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.

Explanation

A computer might stop responding, 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.

User Action

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 Identify 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 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

    Note

    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 Server Configuration Options (SQL Server).

  • 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.

  • 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.