DBCC CHECKDB failed with the following error: "There is insufficient system memory to run this query"

I'm a newbie to SQL Server and have a maintenance plan that failed with this  error. It is SQL Server 2005.

Failed:(-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "There is insufficient system memory to run this query.
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I found more messages in the log file like these errors:
Error 701, Severity: 17, State: 123
Error 17053, Severity: 16, State: 1
Error 17312, Severity: 16, State: 1
Error 17300, Severity: 16, State: 1
DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by NT AUTHORITY\SYSTEM terminated abnormally due to error state 5.

It looks like we have plenty of free space out on the OS and seems to have enough memory. What could be causing this and how do I "fix it"?

TIA!

Bits
data_bitsdbaAsked:
Who is Participating?
 
venk_rCommented:
I would allocate 3G to the SQL Server max memory which will leave 1G for the OS.
– Turn on advanced options
EXEC  sp_configure‘Show Advanced Options’,1;
GO
RECONFIGURE;
GO

– Set max server memory = 3072MB for the server
EXEC  sp_configure 'max server memory (MB)',3072;
GO
RECONFIGURE;
GO

Let me know how it goes.
You dont need to restart the sql server,Its dynamic.
0
 
venk_rCommented:
Is this 64 bit server?If else is the AWE Configured to use more memory?
0
 
data_bitsdbaAuthor Commented:
It is 32-bit. I don't know if AWE is configured to do this
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
venk_rCommented:
Whats the memory on this machine?
0
 
data_bitsdbaAuthor Commented:
There is 4GB of memory on the server.
0
 
data_bitsdbaAuthor Commented:
What will be the impact on the OS of 1GB of RAM? Can I fall back to the original configuration? Could there be a rogue process that is stealing memory?
0
 
venk_rCommented:
If you dont have any other major  software tools apart from SQL Server ,then it should be sufficient.
You can change the memory back to where  it was on sql server if you see any issues.
0
 
data_bitsdbaAuthor Commented:
I issued this sql: select * from sys.configurations order by name
It shows that max server memory (MB) is 2,147,483,647. Is that bytes or kilobtyes or megabytes? If it's bytes, then max server memory would be about 2GB and I would follow the steps you have outlined. If its KB or MB, it's huge. What to do?
0
 
venk_rCommented:
Thats the default value you see  when you install sql  server. Meaning it will consume all the memory available in the system if necessary.We need to allocate just  3GB to the sql server so that the rest is utilized by the OS.Please follow my initial steps to do it.And you can revoke it anytime if at all you see any issues.So dont worry.
0
 
data_bitsdbaAuthor Commented:
How would I rollback to the original setting if needed?
0
 
venk_rCommented:
Right click the server from SSMS ->Properties->Memory
Change the max memory back to 2,147,483,647.
You dont need to restart the service.
0
 
venk_rCommented:
Also one more thing.
DBCC CHECKDB is very memory intensive operation. So make sure you run them during off peak hours.
0
 
data_bitsdbaAuthor Commented:
The tempdb is sitting at 30GB. Is there a way to shrink that down? I'm just getting ready to follow your suggestion with setting max server memory when we noticed that. I want to make sure we're solving the correct problem. :-)
0
 
venk_rCommented:
How much Tempdb size is available on the disk?
You can use the below to shrink each file

   -- this command shrinks the primary data file


   dbcc shrinkfile (tempdev, <target size in MB>)
   -- this command shrinks the primary log file


   dbcc shrinkfile (templog,<target size in MB>)
0
 
data_bitsdbaAuthor Commented:
There seems to be sufficient disk available. If there isn't anything running, how small do you typicall shrink tempdb down to? Maybe I'll do this after the sp_config steps. What do you recommend?
0
 
venk_rCommented:
Try to shink the file in increments instead of doing it one shot.
May be do it 1GB increment.You can shrink it until it allows to shrink.
You can do the sp_config steps first and then do the shrink.
0
 
data_bitsdbaAuthor Commented:
Thank you venk_r for being so patient with all the questions. This is a production server and like I said, I'm very new to SQL Server.  I don't know what I'd do if I did something and the server crashed and wouldn't come back up.
0
 
venk_rCommented:
Don't worry the steps that you r'e doing isnt something new. Its quite common.
0
 
data_bitsdbaAuthor Commented:
Do I need to issue the USE MASTER  before issuing these commands?
– Turn on advanced options
EXEC  sp_configure‘Show Advanced Options’,1;
GO
RECONFIGURE;
GO

– Set max server memory = 3072MB for the server
EXEC  sp_configure 'max server memory (MB)',3072;
GO
RECONFIGURE;
GO
I ralso an across an article that talks about doing this tofree up sql server memory, do you recommend these in addition to the above?
  DBCC FREESYSTEMCACHE
  DBCC FREESESSIONCACHE
  DBCC FREEPROCCACHE
0
 
venk_rCommented:
Not necessary to USE MASTER

EXEC  sp_configure 'Show Advanced Options',1;
GO
RECONFIGURE;
GO

– Set max server memory = 3072MB for the server
EXEC  sp_configure 'max server memory (MB)',3072;
GO
RECONFIGURE;
GO

Well, regarding clearing the cache, there is no harm in it except it clears the execution/plan cache  and recompiles.Running these commands will just clear the cache memory  and they will fill up soon eventually.So setting the max server memory is still mandatory.
Its safer to run during the off hours when you think there are minimal users.

 DBCC FREESYSTEMCACHE
 DBCC FREESESSIONCACHE
 DBCC FREEPROCCACHE



0
 
data_bitsdbaAuthor Commented:
I just issued the sp_configure commands. Now we wait and see what happens.

Do I need to leave the Advanced Options enabled from now on?

Can this error message, "There is insufficient system memory to run this query"  be interpreted as SQL Server has taken up all of the memory and not left anything for the OS to use? Hence, limiting it to 3GB will allow more for the OS to use?
0
 
venk_rCommented:

Yes you can leave the advance option on. No harm done.
Did you run the clear cache commands too?If not run them as well.
Its always good practice to allocate SQL Server memory so it doesnt content with OS.
Even after performing these steps if you still see the problem if all the steps dont work then I would highly recommend upgrading the RAM.

All the Best!!
0
 
data_bitsdbaAuthor Commented:
I didn't run the free cache commands. I was waiting on doing that so as to not make too many changes at once.

We can't apply more memory to the server. Unfortunately, its a Standard Edition Windows Server 2003 box and the limit is 4GB.

Thanks!
0
 
venk_rCommented:
Its safe to run the cache commands.
One quick question
Why is it you want to run the DBCC CHECKDB?Are there any issues on the database?
0
 
venk_rCommented:
Take a look at this .You can run DBCC CHECKDB in more effecient ways.

http://venkatsqlserverclub.blogspot.com/2011/08/faster-way-to-use-dbcc-checkdb.html
0
 
data_bitsdbaAuthor Commented:
I inherited the environment and this was set up already.

I don't know why the DBCC CHECKDB is included as part of the maintenance plan for full backups. I'm still learning about this and would like to removed the DBCC CHECKDB so the backups would run cleanly.

Do you know of a valid reason why DBCC CHECKDB would be included before a backup? Or why one would not include it?
0
 
data_bitsdbaAuthor Commented:
I think that the  DBCC CHECKDB was included because the schedule is to do a full backup of each database once a week and differentials the other times. Supposedly it is a good practice to do a DBCC CHECKDB once a week. But probably in our case, separate it from the backups?
0
 
venk_rCommented:
Follow the link that I sent you. There should be code which will use DBCC command to check the basic feature.Which should be enough going forward.And you can run them weekly once.That should be sufficient.
0
 
venk_rCommented:
One more way to run DBCC commands to restore the backup on a different server(if you have one) and run the checks there.
0
 
data_bitsdbaAuthor Commented:
The maintenance plan failed again with the same errors:
Task start: 2011-09-22T20:00:14.
Task end: 2011-09-22T23:55:22.
Failed:(-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS " failed with the following error: "There is insufficient system memory to run this query.
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 
0
 
venk_rCommented:
Its unfortunate.I think its just doesn't have enough resource on that server to support operations like this.

Few suggestions (may be I have mentioned some  before):
Keep the max server settings  . Dont change them back to default value.
Try to restore the backup everyday and do the dbcc check on a different server if possible
I have attached the t-sql which  identifies the most common IO errors significantly faster than DBCC CHECKDB.Thats from the same URL that I sent.I tried it on my machine too. Works just fine.

All the Best.
CheckdataIntegrity.sql
0
 
data_bitsdbaAuthor Commented:
venk_r was very patient and informative
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.