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