Time-out occurred while waiting for buffer latch type 4; tempdb (also posted on ServerFault)

Question

Post reply

Time-out occurred while waiting for buffer latch type 4; tempdb (also posted on ServerFault)

Viewing 15 posts - 16 through 29 (of 29 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Depends slightly on versions, amount of memory and architecture (32 vs64-bit), but the following articles should help:

How to Enable Lock Pages In Memory

How to enable AWE

Set Min and Max memory options to appropriate values

I've assumed from the other forum you linked from that you're actually sql 2005 rather than 2008

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

This is what Howard was reffering too.

http://msdn.microsoft.com/en-us/library/ms190730.aspx

aurato SSCrazy Points: 2575 More actions · Answer 3

Ninja's_RGR'us (7/29/2011)
This is what Howard was reffering too.
http://msdn.microsoft.com/en-us/library/ms190730.aspx

I'm assuming I have to add the sql server service account, yes?

aurato SSCrazy Points: 2575 More actions · Answer 4

Still haven't enabled it because I don't know exactly what Microsoft means by

the account that has permission to run the Sqlservr.exe file

My guess is the account that the SQL Server service runs as, but they didn't say service account and I've never heard of it described this way. Bail me out here, please!

HowardW One Orange Chip Points: 29109 More actions · Answer 5

HowardW

One Orange Chip

Points: 29109

July 29, 2011 at 10:02 am

#1360904

Yes, it means the SQL Server Service Account

aurato SSCrazy Points: 2575 More actions · Answer 6

aurato

SSCrazy

Points: 2575

July 29, 2011 at 10:22 am

#1360922

Thank you, and apologies for my ineptitude.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

aurato (7/29/2011)
Thank you, and apologies for my ineptitude.

No apologies necessary. I din't know that either ;-).

aurato SSCrazy Points: 2575 More actions · Answer 8

Ninja's_RGR'us (7/29/2011)
aurato (7/29/2011)
Thank you, and apologies for my ineptitude.
No apologies necessary. I din't know that either ;-).

Glad I'm not alone 🙂

I'll leave this thread for a few days and see if I solve the problem. If I do I'll report back with the reasons I found and if your suspicions are correct. If I don't solve it, I'll report back with whatever is still confusing me or maybe more hints as to the issue.

Thanks a ton, both of you. Great community.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Ninja's_RGR'us

SSC Guru

Points: 294069

July 29, 2011 at 11:07 am

#1360952

Happy to help, TIA for the feedback.

aurato SSCrazy Points: 2575 More actions · Answer 10

Well, it seems I can't lock pages in memory because when the server was set up the Service Account was made to be Local System. I probably have to wait for some kind of reboot to change that I imagine.

They ran a memory test of some sort on it and all tests passed. Paging file %Usage is hovering at about 8 though.

At its worst in my perfmon logs, just before the reboot, the %Usage was at 50 (!!!).

Keep in mind this thing has about 70GB of Ram and according to task manager it's using less than 30. I'm pretty lost. Could the queries we run be THAT bad?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Yes they could.

I've seen a simple 3 left join query kill a decent server on sql 7 by filling up temp db.

The base tables had less than 0.001% of the data needed to fill tempdb, but it still happened.

Maybe this is a monster like this. But right I'm more poking in the dark than anything else.

msumption SSC Enthusiast Points: 183 More actions · Answer 12

Hopefully it's ok to glom onto this thread, but I had a similar error situation while a database maintenance job was running and failed on the "Rebuilding indices" step of the job. The errors from the event log are listed below in order. There have been HD failures in the past, but on the C: drive, not the F: drive (which is actually a SAN) where the DB files are located. Any insights would be much appreciated.

1. SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [f:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SCCMS.mdf] in database [SCCMS] (11).

2. A time-out occurred while waiting for buffer latch -- type 4, bp 036E9578, page 1:1616250, stat 0x2c0010f, database id: 11

3. SQL Server has encountered 306 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [f:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SCCMS.mdf] in database [SCCMS] (11).

4. Time out occurred while waiting for buffer latch -- type 2, bp 03758560, page 1:27814, stat 0x7c0040d, database id: 11

5. Time out occurred while waiting for buffer latch -- type 2, bp 0375CD38, page 1:348668, stat 0x5c0040d, database id: 11

Michael

aurato SSCrazy Points: 2575 More actions · Answer 13

I would make a new topic with a different title. Seems like a very different issue to me.

msumption SSC Enthusiast Points: 183 More actions · Answer 14

aurato (9/7/2011)
I would make a new topic with a different title. Seems like a very different issue to me.

Hmmm, ok, if you say so even though the error message is the same, which is how I found this thread in the first place.

Michael

aurato SSCrazy Points: 2575 More actions · Answer 15

msumption (9/7/2011)
aurato (9/7/2011)
I would make a new topic with a different title. Seems like a very different issue to me.
Hmmm, ok, if you say so even though the error message is the same, which is how I found this thread in the first place.
Michael

Different page being locked up is probably a different issue. I don't know exactly, I'm not an expert. The guys who were helping me are silent though so who knows! Good luck!