A time-out occurred while waiting for buffer latch

Question

A time-out occurred while waiting for buffer latch

balasach82

SSCrazy Eights

Points: 9653
More actions
September 24, 2011 at 1:34 am

#244394

As soon as our DBCC reindex job starts, we have below messages looged
1. BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds.
2. SQL Server has encountered 2342 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\program files\microsoft sql server\mssql\data\XXXXXXXX.NDF] in database [XXXXXXXX] (5). The OS file handle is 0x00000A28. The offset of the latest long I/O is: 0x000013841f0000
and sometimes, it fails with below message:
1. A time-out occurred while waiting for buffer latch -- type 2, bp 0689B984, page 4:8425718, stat 0xc1000f, database id: 5, allocation unit Id: 425344456785920/143869480075264, task 0x00A7B4C8 : 3, waittime 300, flags 0x1a, owning task 0x00DB4208. Not continuing to wait.
This happens even though we have tempdb in separate drive and 5 filegroups, primary, and log drives.. Any idea what the error means and how to avoid the 15 seconds error.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Which edition of SQL Server do you have? Also, any chance of you posting the code for your reindex job... sanitized, of course.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

balasach82 (9/24/2011)
we have tempdb in separate drive and 5 filegroups, primary, and log drives.. Any idea what the error means and how to avoid the 15 seconds error.

can you give a clearer meaning of this please, what is the exact setup of your tempdb?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 3

Provided clarifications below:

1. We placed the tempdb.mdf and ldf files in a separate drive.

2. dbcc dbreindex(XXXXXX,'',90) with no_infomsgs

dbcc dbreindex('XXXXXX') with no_infomsgs

Version: 2005 with SP3

Tempdb initial size: 8 MB (mdf), 1 MB (ldf)

Present size of Tempdb: 10GB

Auto Growth set to 10% with unlimited growth

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

balasach82 (9/24/2011)
Provided clarifications below:
1. We placed the tempdb.mdf and ldf files in a separate drive.
2. dbcc dbreindex(XXXXXX,'',90) with no_infomsgs
dbcc dbreindex('XXXXXX') with no_infomsgs
Version: 2005 with SP3
Tempdb initial size: 8 MB (mdf), 1 MB (ldf)
Present size of Tempdb: 10GB
Auto Growth set to 10% with unlimited growth

Just one more piece of information... is it Enterprise or Standard Edition?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 5

Its Enterprise edition.

Processors: 8

Max Server Memory: set at 10 GB

Memory: 12 GB

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Ok, for a start you should really be using

ALTER INDEX ...........REBUILD

ALTER INDEX ...........REORGANISE

The drives for the tempdb are they local RAID. SATA or SAS, SAN based storage, .........?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

could you please also return the results of the following query

SELECTNAME

, PHYSICAL_NAME

, (size * 8) / 1024 as SizeInMBs

, CASE max_size

WHEN -1 THEN 'Unrestricted'

WHEN 0 THEN 'NoGrowth'

WHEN 268435456 THEN '2TBS'

END AS MaxSize

,CASE is_percent_growth

WHEN 0 THEN CAST(((growth * 8) / 1024) AS VARCHAR(10)) + ' MBs'

WHEN 1 THEN CAST(growth AS VARCHAR(4)) + ' %'

END AS GrowthTypeSize

FROM SYS.master_files WHERE database_id = DB_ID('TEMPDB')

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 8

tempdevE:\tempdb.mdf8Unrestricted10%

templogE:\templog.ldf0Unrestricted10%

Have to chk with Network team abt the SAN, RAID .etc in which the DB's reside. will get back ASAP

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

Ok so can you supply more information regarding the d drive set up and file information for database id 5 as that is what the error is complaining about

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 10

XXXXX_Datad:\program files\microsoft sql server\mssql\data\XXXXX.mdf158972Unrestricted300 MBs

XXXXX_Logl:\program files\microsoft sql server\mssql\data\XXXXX.ldf46002TBS20 MBs

yyyyy_Data1f:\program files\microsoft sql server\mssql\data\yyyyy_Data1.NDF71892Unrestricted300 MBs

yyyyy_Data2h:\program files\microsoft sql server\mssql\data\yyyyy_Data2.NDF107838Unrestricted200 MBs

yyyyy_Data3D:\Program Files\Microsoft Sql server\Mssql\data\yyyyy_Data3.ndf48540Unrestricted200 MBs

yyyyy_Data4f:\Program Files\Microsoft Sql Server\Mssql\Data\yyyyy_Data4.ndf82186Unrestricted300 MBs

yyyyy_Data5h:\Program Files\Microsoft SQL Server\MSSQL\Data\yyyyy_Data5.ndf19776Unrestricted200 MBs

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

so now detail the other drives

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

balasach82 (9/24/2011)
Its Enterprise edition.
Processors: 8
Max Server Memory: set at 10 GB
Memory: 12 GB

In that case, you might want to look into ALTER INDEX instead of DBCC and use the "online" option. It'll run slower but you won't get an instant timeout. Details are in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 13

I have had this issue on three different SQL Servers. This problem is extremely difficult to pinpoint. I opened cases with Microsoft on two of them. One we were never able to pinpoint except for them to say it was the back end storage problem. The other one was a SQL 2005 problem and there was a CU that was supposed to fix it.