1

I am using SQL Server 2016 Standard Edition that shows the following errors on DBCC CHECKDB and also on updating statistics. 

 Version: Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64) 
 Jun 28 2018 17:24:28 Copyright (c) Microsoft Corporation Standard Edition 
 (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

MAX Memory set at 215GB from 244GB x1e.2xlarge AWS EC2 There are around 260 databases. Locked Pages in Memory is on. Page File size is 8GB.

Memory optimized tables that uses less than 10GB of memory.

Failed to reserve pages: FAIL_PAGE_RESERVATION

Error: 8645, Severity: 17, State: 1. A timeout occurred while waiting for memory resources to execute the query in resource pool 'internal' (1). Rerun the query.

Error: 701, Severity: 17, State: 135. There is insufficient system memory in resource pool 'internal' to run this query.

After multiple errors like these, the server shutdown itself.

Page life expectancy is 1297050, no memory grants pending, doesn't appear to have any memory pressure.

Any ideas on this issue?

Improve this question
9
  • Is it a DBCC CHECKDBon a specific database? How big is the database? Same for the size of the table where update statistics runs on. Also, is the update statistics on a mem optimized table when the error happens?
    – Randi Vertongen
    Apr 18, 2019 at 7:59
  • @RandiVertongen 2016SP2CU4; checkdb on MSDB size was around 5GB; update statistics were on normal databases
    – lemniscate
    Apr 18, 2019 at 8:53
  • Are there any SQL Dump files near the error log / Information in the error log on a sql dump when dbcc checkdb completes?
    – Randi Vertongen
    Apr 18, 2019 at 9:06
  • @lemniscate Please add output of select @@version, select * from sys.dm_os_process_memory in the question. Also to actually find root cause please upload output of sp_readerrorlog on shared drive and post the link in question. Solving OOM error sometime requires excessive analysis of logs. Do mask imp details in errorlog before uploading. Normally when you get OOM message the output of dbcc memorystatus() is dumped in the errorlog.
    – Shanky
    Apr 18, 2019 at 11:39
  • 2
    You said you have set max memory to 215 GB but SQL Server 2016 standard has maximum limit of 128GB for database engine. Are you aware about this ? Could this not be the reason for what you are seeing ?
    – Shanky
    Apr 18, 2019 at 11:46

2 Answers 2

Reset to default
1

Just so we have 100% clarity, memory-optimized tables:

  1. don't use the buffer pool, so PLE is not relevant
  2. are ignored by CHECKB
  3. memory consumed by memory-optimized objects cannot be swapped out for any reason, so page file is not relevant

The limit of 128GB for Standard Edition applies only to the buffer pool. Comments by others that say SQL Server cannot use more than 128GB for Standard are incorrect.

For Standard Edition, there is a cap on the total size of all memory-optmized data/indexes and clustered columnstore. The combined total cannot exceed 32GB per database.

Improve this answer
2
  • Yep, all combined usage is less than 10GB so the issue probably isn't related to memory-optimized tables
    – lemniscate
    Apr 19, 2019 at 7:09
  • Speaking on behalf of the others, I'm responding to the comment in the question: MAX Memory set at 215GB. This should not set higher than 128MB. You can, but you'll run into memory errors like are being displayed.
    – John Eisbrener
    Apr 23, 2019 at 13:39
0

You state:

MAX Memory set at 215GB

As @Shanky stated in the comments, set max server memory (MB) to 128 GB via sp_configure as that's the limit of usable memory for SQL Server Standard Edition.

I've run into a similar issue with SQL Server Express edition when I had it set higher than it's usable limit.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.