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

Question

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

This is an error message I got for five times while trying to create an index

CREATE NONCLUSTERED  INDEX YearIndx ON dbo.Papers
(   
    PublicationYear
)

the papers table is about 20,000,000 records and 175 GB

Thank you in advance.

Answer 1

I've never had this happen to me and searches on the great Google didn't hit me immediately, so here is what I would try:

Please verify that you are running this query from an SSMS query window. If so, then go to Query, Query Options, Execution. What is the value for Execution time-out?

CREATE NONCLUSTERED  INDEX YearIndx ON dbo.Papers
(   
    PublicationYear
)
WITH 
(
SORT_IN_TEMPDB = ON
--, ONLINE=ON               --Enterprise only
, ALLOW_ROW_LOCKS =  OFF    --default is on
, ALLOW_PAGE_LOCKS =  OFF   --default is on
,  MAXDOP = 1               --all that you can allow
)

• oh, and make sure that you read up on the create index statement to make sure that the options I chose apply to your situation. for instance, if tempdb is on the same set of disks as db_name(6) is, then the situation will probably only get worse

• another option is to create an identical, empty table which has all of the indexes you want on it. then you can gently fill that table by selecting out of dbo.Papers. When you are finished, then with one batch, transaction wrapped, rename dbo.Papers to dbo.Papers_old and dbo.Papers_new to dbo.Papers.

• finally, I was thinking about your index. Are you absolutely certain that an index on just PublicationYear will solve whatever problem appearing? Just struck me that most folks probably are not searching for all of anything written in one year. More likely, all papers written by john in this year, or all papers written last month.

GOOD LUCK!

Answer 2

Hi, please note, if your problem is involving a temporary table rather than index creation and there's "type 4" in your error message, the problem is likely to be a SQL Server 2008 bug - http://support.microsoft.com/kb/968543

As Index creation actually involves tempdb if "SORT_IN_TEMPDB = ON", these issues may well be related.

The recommendation is to apply cumulative updates for your version of SQL server.

Our error looked like this:

Time out occurred while waiting for buffer latch -- type 4, bp 00000000FEAFB028, page 12:312752, stat 0xbc00e09, database id: 2, allocation unit id: 724228131807060/351413755606409, task 0x00000000D2220838 : 0, waittime 20700, flags 0x100000001a, owning task 0x0000000D22302808. Continuing to wait.