1

Topic: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

The full test
Error: Time-out occurred while waiting for buffer latch type 3 for page (11:485280), database ID 2.
The version of Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 6:17:30 PM Copyright (c) Standard Edition Microsoft Corporation (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600:)
Checkdb produces 0 errors.
The error came up the second time for couple of weeks.
As it tempDb, and a time interval, between the previous error. The server a minimum of 1 times was overloaded. And also tempDb was rehay on other disk (that is a question to "" the disk as though cannot be).
In a broad gull sql
For 4 minutes of time about 12 similar errors. With buffer latch type 3 and buffer latch type 4. All concern to tempdb
And for 6 minutes of the order 20 type messages
Process 0:0:0 (0xad8) Worker 0x00000049EC400160 appears to be non-yielding on Scheduler 17. Thread creation time: 13150541410851. Approx Thread CPU Used: kernel 0 ms, user 124703 ms. Process Utilization 60 %. System Idle 38 %. Interval: 130882 ms.
The previous time. Met a mention that in 2005 versions it can be the multithreading conflict for disk resources and the recommendation to increase an amount of files in basis. In tempdb now consists of 12 files (on 3 independent IO)
Where further to dig to search for the reason?

2

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

All the information which I find, concerns or to 2008 and earlier, a maximum to 2012 versions.

3

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

Dzianis wrote:

Error: Time-out occurred while waiting for buffer latch type 3 for page (11:485280), database ID 2.

to begin with it is necessary to look that at this such

dbcc page (2, 11, 485280)

Type 3 is any update - some processes change the same page

4

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

wrote:

it is passed...
To begin with it is necessary to look that at this such

dbcc page (2, 11, 485280)

Type 3 is any update - some processes change the same page

wrote:

PAGE: (11:485280)
BUFFER:
BUF @0x0000004A11E10000
bpage = 0x00000049CAB42000 bhash = 0x0000004A6E919A80 bpageno = (11:485280)
bdbid = 2 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 38830 bstat = 0x10b
blog = 0x8a8a8a8a bnext = 0x0000000000000000 bDirtyContext = 0x000000449AAE61A0
bstat2 = 0x0
PAGE HEADER:
Page @0x00000049CAB42000
m_pageId = (11:485280) m_headerVersion = 1 m_type = 11
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 2 m_freeData = 8188
m_reservedCnt = 0 m_lsn = (669:1696:422) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 648380467
DB Frag ID = 1
Allocation Status
GAM (11:2) = NOT ALLOCATED SGAM (11:3) = NOT ALLOCATED
PFS (11:485280) = 0x40 ALLOCATED 0_PCT_FULL DIFF (11:6) = CHANGED
ML (11:7) = NOT MIN_LOGGED

5

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

Dzianis;
And show

exec sp_helpdb tempdb

6

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

komrad wrote:

Dzianis;
And show

exec sp_helpdb tempdb
wrote:

 tempdb 51241.75 MB sa 2 Sep 22 2017 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=852, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 130 
tempdev 1 E:\SQLData\tempdb.mdf PRIMARY 3896448 KB Unlimited 65536 KB data only
templog 2 E:\SQLLogs\templog.ldf NULL 139264 KB Unlimited 65536 KB log only
temp2 3 D:\SQLData\tempdb2.mdf PRIMARY 3638720 KB Unlimited 65536 KB data only
temp3 4 D:\SQLData\tempdb3.mdf PRIMARY 3495232 KB Unlimited 65536 KB data only
temp4 5 D:\SQLData\tempdb4.mdf PRIMARY 3976320 KB Unlimited 65536 KB data only
temp5 6 D:\SQLData\tempdb5.mdf PRIMARY 3980416 KB Unlimited 65536 KB data only
temp6 7 C:\SQLData\tempdb6.mdf PRIMARY 3008320 KB Unlimited 65536 KB data only
temp7 8 C:\SQLData\tempdb7.mdf PRIMARY 3638976 KB Unlimited 65536 KB data only
temp8 9 C:\SQLData\tempdb8.mdf PRIMARY 4849792 KB Unlimited 65536 KB data only
temp10 10 C:\SQLData\tempdb10.mdf PRIMARY 7282688 KB Unlimited 65536 KB data only
temp11 11 E:\SQLData\tempdb11.mdf PRIMARY 7282688 KB Unlimited 65536 KB data only
temp9 12 E:\SQLData\tempdb9.mdf PRIMARY 7282688 KB Unlimited 65536 KB data only

7

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

Dzianis wrote:

where further to dig to search for the reason?

It read - https://mssqlwiki.com/2012/09/07/latch- … er-latch/?

8

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

invm wrote:

it is passed...
It read - https://mssqlwiki.com/2012/09/07/latch- … er-latch/?

Under this article we come only to https://support.microsoft.com/en-us/hel … b-database
That is necessary to increase further an amount  the data at , we admit to 24 pieces.

9

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

Dzianis wrote:

under this article we come only to https://support.microsoft.com/en-us/hel … b-database

In article is described as to use a debugger for search of the reason of this error.

10

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

invm wrote:

it is passed...
In article it is described as to use a debugger for search of the reason of this error.

Tomorrow I will try to walk.
Though it seems that  can not approach to the user DB to .
All the same it is the pages used implicitly for "the temporal data".
Errors appear at performance of standard requests in the user databases

11

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

Dzianis wrote:

it is passed...
it is passed...

Something at you the basis is spread on three disks, one of them system
For localization of a problem I at first  all files (to reduce to 8 kol-in date of files) on one selected disk to level loading from other bases and applications

12

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

invm wrote:

In article it is described as to use a debugger for search of the reason of this error.

File search  for  ended with failure.
In a file of a broad gull there are no mentions of a way and a name. File search in a disk gave nothing the same.

13

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

komrad wrote:

something at you the basis is spread on three disks, one of them system
For localization of a problem I at first  all files (to reduce to 8 kol-in date of files) on one selected disk to level loading from other bases and applications

3 disks at everyone the raid.
on one disk easily. But there is no possibility to make the selected disk for .
To level loading
- It is unique big basis on sql the server.
- Other application is used only in working hours. The error on a broad gull does not coincide on time with loading, it was at night when almost all resources are free and  sql.

14

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

On a broad gull as there is an error.
External dump process return code 0x20000001. External dump process returned no errors.

15

Re: Error in tempdb: Time-out occurred while waiting for buffer latch type 3 for page...

Dzianis wrote:

it is passed...
File search  for  ended with failure.
In a file of a broad gull there are no mentions of a way and a name. File search in a disk gave nothing the same.

And that shows it?

select * from sys.dm_server_memory_dumps