1

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
  • So, what do you have the Max Server Mem setting on the Sql instance set to - (ssms>rt click on instance>properties>memory)? Is there only 1 instance of sql server on this server? – rvsc48 Mar 15 '16 at 15:03
  • 2
    DBCC CheckDB makes pretty heavy use of TempDB, have you checked whether it's sized correctly and has room to grow if it needs to? – Fred Shope Mar 15 '16 at 15:13
  • What is the size of the database that you are checking? Could you try running it with the ESTIMATEONLY option? – Aaron Mar 15 '16 at 15:30
  • Sound memory related to me, but I would add that DBCC CHECKDB utilizes a hidden database snapshot, in an effort to avoid concurrency problems. Given your runtime (47 minutes) I would also check the volumes supporting the database for sufficient space. Do you have any history of free space for your volumes over the duration of execution of the command to rule that out? – Andrew Loree Mar 15 '16 at 16:23
  • 1
    Since you're getting memory status dumps, can you post them here? The answer is in them. – Remus Rusanu Mar 15 '16 at 18:47
1

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

  • So I decided to set a lower max memory setting and monitor the server. After running CHECKDB for 50 minutes one of the svchost.exe start getting page fault and free memory quickly drop from 2GB to 0 (but the process itself was only taking 300MB). The chaos last about a minute and then the system settle down. CHECKDB wasn't interrupted this time, and the sysadmin is asked to check which underlying service is causing this issue - a new monitoring tool was installed about a month ago. – elty123 Mar 22 '16 at 14:20
1

So I decided to set a lower max memory setting and monitor the server. After running CHECKDB for 50 minutes one of the svchost.exe start getting page fault and free memory quickly drop from 2GB to 0 (but the process itself was only taking 300MB). The chaos last about a minute and then the system settle down. CHECKDB wasn't interrupted this time, and the sysadmin is asked to check which underlying service is causing this issue - a new monitoring tool was installed about a month ago.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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