Comments
1 comment
-
The errors indicate memory buffer contention for the two pages. For some reason a process has been holding the two page buffers for an extended time (e.g. > 300 seconds). A long running transaction or hung process are examples.
This article "Posts Tagged ‘A time-out occurred while waiting for buffer latch’"http://mssqlwiki.com/tag/a-time-out-occurred-while-waiting-for-buffer-latch/ is pretty detailed but has a good description of the problem and has some tips for tracing it.
Generally, you will find that there is some bottleneck that is not "obvious" until you see it. In my case the backup was trying to write 200+GB from a file group while the application(s) were inserting/updating data at a high rate. The disk just could not keep up with the I/O so memory buffers were filled with pages waiting to be written.
HTH.
Add comment
Please sign in to leave a comment.
I am see the user is getting timeout error when database backup is running using SQL backup 7. Please suggest.
Sql backup version: SQL backup 7.70.18
SQL server version: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
users are getting timeout error. and there is no high IO job runs this time except this backup using SQL backup 7. There high IO operation done by any other jobs.
Small DML's done by other jobs failing due this error when running in parallel with backup.
Error:
Time-out occurred while waiting for buffer latch type 2 for page (1:105), database ID 6. [SQLSTATE 42000] (Error 845)
Time-out occurred while waiting for buffer latch type 2 for page (1:219652), database ID 6. [SQLSTATE 42000] (Error 845).
Database size is approx 200+ GB.
Please suggest