Three separate processes have failed in the last two days with very similar errors:

Time-out occurred while waiting for buffer latch type 4 for page (1:1189832), 
database ID 2.

Time-out occurred while waiting for buffer latch type 4 for page (1:1189827), 
database ID 2.

Time-out occurred while waiting for buffer latch type 4 for page (1:1189827), 
database ID 2.

Two of them are exactly the same, the other is accessing a slightly different pagenum. Database #2 is tempdb. I tried to run DBCC PAGE(tempdb, 1, 1189827, 1) but the output is just gibberish to me. I'm fairly lost.

Does anyone know what the root cause of these timeouts might be? (I've seen dozens of suggestions on other forums, among which was memory corruption)

Any help would be greatly appreciated. If you would like to read the DBCC PAGE output, ask and I will post it.

2 Answers 2


By the time that you whip out dbcc page and try to look at the page, it's possibly/probably being used for something else anyway. It is tempdb, after all.

I've been at this since SQL 6.5 and I have only ever seen these sorts of page timeouts caused by slow or misconfigured storage. Every incident I can remember offhand, it's been a SAN. If you have a SAN, you should definitely talk to your SAN guy/gal and find out if they have been seeing unusual load on the system or if they have changed anything lately. I've seen problems with flaky fiber switches and cables, for example, or maybe they moved your data onto slower disks lately.

Beyond that, Have a hard look at your drives, SAN or otherwise. Look at the "Avg. Disk Sec/Read" and "Avg. Disk Sec/Write" numbers for the drive that the tempdb MDF file is stored on while stuff is happening, preferably while you are getting errors. It's helpful to look at the bytes/second numbers as well, to get a feel for what the drives are being asked to do (a drive with 1000 ms latency numbers that is being asked to push only 1 MB/sec is one thing, a drive with 50 ms latency when pushing at 10 MB/s is another.)

If the drive is overwhelmed, you may need to reduce the I/O going to the drive. Tactics would include moving tempdb files to another device, creating additional tempdb data files on other devices, tuning queries (any unnecessary GROUP BY or DISTINCT?), moving any data files that are also on that storage device to somewhere else, etc.

  • I have the statistics that you suggested being gathered and they were being gathered at the time of the errors so I can look into that. I'll do some analysis and see if I can fix it. Thank you for your response.
    – Aushin
    Commented Jul 29, 2011 at 14:11
  • I can't seem to upvote you here. On ServerFault I could've.
    – Aushin
    Commented Jul 29, 2011 at 14:11
  • Because he's not registered here.
    – jcolebrand
    Commented Jul 30, 2011 at 4:38
  • Please consider trying the upvote again, as I've just registered here. Thx. Commented Feb 20, 2012 at 11:50

If your running 2005+, run the two scripts below and add the output to your original question.

    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
                                                   + num_of_writes) AS NUMERIC(10,
                                                              1)) AS [avg_io_stall_ms]
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf
ON  fs.database_id = mf.database_id
    AND fs.[file_id] = mf.[file_id]
    avg_io_stall_ms DESC

WITH Waits AS 
, wait_time_ms / 1000. AS wait_time_s
, 100. * wait_time_ms / SUM(wait_time_ms) OVER () AS pct
, ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn
  wait_type NOT IN (
  , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
  , CAST(W1.pct AS DECIMAL(12, 2)) AS pct
  , CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
    Waits AS W1
    Waits AS W2
ON  W2.rn <= W1.rn
  , W1.wait_type
  , W1.wait_time_s
  , W1.pct
    SUM(W2.pct) - W1.pct < 99

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.