Sql-server – DBCC CHECKDB ran out of memory

dbcc-checkdbsql server

We have a routine DBCC CHECKDB running at night when the database is still accessible but with very little traffic. Since last month we had several occasions of DBCC CHECKDB crashing to this error:

DBCC CHECKDB (Database1) WITH no_infomsgs executed by sqladmin terminated abnormally due to error state 5. Elapsed time: 0 hours 47 minutes 9 seconds.

This is preceded by several SQL Alert Severity 17, which is insufficient resources, as well as output from DBCC MEMORYSTATUS in the SQL server log. Thus I believe DBCC CHECKDB crash due to insufficient memory.

Running DBCC CHECKDB again would return no error. A non DBA even did it once during business hour, while it drag down the performance and it took nearly 3 hours to complete, it did not result in a memory problem. (He was told not to do it again).

The server itself has 12GB of RAM, but there is no minimum and maximum limit set for SQ server. SQL Server itself use about 10GB of memory, while all other processes use 1GB. I am not aware of anything else taxing the server at that time.

edit:

  1. there is no min or max memory limit set, original question is missing the key word "no"
  2. there is enough room for temp DB to grow
  3. there was no disk space alert on any of the volume, so I assume disk space is not an issue
  4. database is about 50GB in size
  5. I am not aware of any system changes other than Windows Update
  6. Normally CHECKDB would take 60-90 minutes to complete at night

Best Answer

The statement "Since last month we had several occasions of DBCC CHECKDB crashing to this error..." makes me think that there was a change done on your system last month that may have caused this. Was anything implemented last month to your knowledge? For example any patches, updates to SQL Server, and applications were installed or upgraded on the SQL Server, etc...

Have you tried the below post to see if the issue is an OS Memory issue or if the error is caused by SQL?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ff936d46-d210-4326-a112-0501a366ea41/dbcc-checkdb-failure-sql-2012-there-is-insufficient-system-memory-in-resource-pool-internal-to?forum=sqldatabaseengine

You could try to split out the DBCC CheckDB command to help alleviate any resource constraints.

Dividing DBCC CHECKDB over multiple days

Related Question