4

Last Saturday night, our site went down for about 10 minutes. Analysis of the logs showed the following errors on the principal SQL Server (in a mirrored pair), during the outage and not outside the outage:

  • 06/06/2015 23:14:41,spid[various],Unknown,Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT' id [various] type 4 Task [various] : [various] waittime 300 flags 0x1a owning task [various]. Continuing to wait.
  • 06/06/2015 23:06:54,spid19s,Unknown,Time-out occurred while waiting for buffer latch type 3 for page (5:159076157) database ID 6.
  • 06/06/2015 23:06:54,spid19s,Unknown,Error: 845 Severity: 17 State: 1.
  • 06/06/2015 23:06:54,spid19s,Unknown,A time-out occurred while waiting for buffer latch -- type 3 bp 0000000518FA1200 page 5:159076157 stat 0xc0000b database id: 6 allocation unit Id: 72057793340899328 task 0x0000000008EDA748 : 0 waittime 300 flags 0x100000001a owning task 0x0000000004472BC8. Not continuing to wait.

There were over a hundred of the first error mostly before but also after the others, which only occurred once. These errors occurred about 2 hours after doing a failover, applying OS updates, and failing back to the original server. We've been running on these servers for about 2 years now and have never seen this issue. The software calling into the servers was most recently updated on Thursday afternoon (about 55 hours before the outage).

I'm finding very little information here or through google about this timeout. The closest thing I've found is the second answer to this question: https://stackoverflow.com/questions/3149310/time-out-occurred-while-waiting-for-buffer-latch-type-2-error-in-sql-server, which talks about type 4 latch error being tempdb-related and caused by a bug in 2008, but that bug was resolved in 2009, before 2008 R2 was released. The exact version reports as:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)    
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

The servers both use mirrored Intel Enterprise SSDs for everything (dual RAID 10 arrays for the data), and none of the drives are reporting issues. The tempdb volume has 200GB free. There are 20 tempdb files (it's a BIG server). SQL traffic is down significantly from our peak over a year ago due to optimizations in the schema, stored procedures, and the software calling SQL, so it's probably not a load-related issue.

Is type 4 definitely related to the tempdb as many posts seem to indicate (database 6 referenced in the error message is our main database, not the tempdb)? What can I do to prevent this issue from happening again?

Improve this question
10
  • The database id is 6. You should have memory dump generated that can be found using sys.dm_server_memory_dumps (since you are running 2008R2 SP1). What is the MAXDOP setting ? I would bet that that is set to 0 (default). Seems to me related to non-Yielding scheduler.
    – Kin Shah
    Commented Jun 9, 2015 at 15:09
  • SQL Server 2008 R2 SP1 well I consider this as main issue. SP1 is not supported at all and there were few fixes in subsequent release you must first update SQL Server 2008 r2 to SP3 and then see how SQL Server behaves
    – Shanky
    Commented Jun 9, 2015 at 15:29
  • @Kin: I didn't know about sys.dm_server_memory_dumps. Thanks! Unfortunately, there isn't one for this. There are three from 2012 and 2013.
    – James
    Commented Jun 9, 2015 at 16:13
  • @James can you check the log folder .. there has to be memory dump. Also what is the MAXDOP setting ?
    – Kin Shah
    Commented Jun 9, 2015 at 16:16
  • @Kin: MAXDOP is 0, and I'm VERY hesitant to change that. This server has 40 cores (80 virtual cores). The log folder has a bunch of log files from 90 minutes before (at the time of the reboot/failover) and one from 3 hours later (daily log backups). Nothing inside that window. The dumps listed from the query are the only three dumps in that folder, and they are numbered sequentially, 001-003.
    – James
    Commented Jun 9, 2015 at 16:48

1 Answer 1

Reset to default
0

We had another mysterious SQL crash yesterday afternoon (no logs for that one either--just a SQL error message). We failed over to the mirror and ran some burn-in tests on this system and it turns out this server had a memory module that must have recently developed issues. I believe this is likely the cause of these issues. Thanks to all who looked into this!

Improve this answer

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.