Solved

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

Posted on 2011-09-16
32
3,520 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:data_bits
  • 17
  • 15
32 Comments
 
LVL 8

Expert Comment

by:venk_r
ID: 36550683
Is this 64 bit server?If else is the AWE Configured to use more memory?
0
 

Author Comment

by:data_bits
ID: 36551311
It is 32-bit. I don't know if AWE is configured to do this
0
 
LVL 8

Expert Comment

by:venk_r
ID: 36551493
Whats the memory on this machine?
0
 

Author Comment

by:data_bits
ID: 36564199
There is 4GB of memory on the server.
0
 
LVL 8

Accepted Solution

by:
venk_r earned 500 total points
ID: 36564363
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
 

Author Comment

by:data_bits
ID: 36574555
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36574844
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
 

Author Comment

by:data_bits
ID: 36580062
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36580121
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
 

Author Comment

by:data_bits
ID: 36580263
How would I rollback to the original setting if needed?
0
 
LVL 8

Expert Comment

by:venk_r
ID: 36580279
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36580282
Also one more thing.
DBCC CHECKDB is very memory intensive operation. So make sure you run them during off peak hours.
0
 

Author Comment

by:data_bits
ID: 36581777
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36581800
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
 

Author Comment

by:data_bits
ID: 36582225
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36582527
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:data_bits
ID: 36582582
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36582641
Don't worry the steps that you r'e doing isnt something new. Its quite common.
0
 

Author Comment

by:data_bits
ID: 36583040
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36583179
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
 

Author Comment

by:data_bits
ID: 36583571
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36583629

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
 

Author Comment

by:data_bits
ID: 36583678
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36583730
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36583766
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
 

Author Comment

by:data_bits
ID: 36583775
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
 

Author Comment

by:data_bits
ID: 36583864
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36583934
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36583943
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
 

Author Comment

by:data_bits
ID: 36586500
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
 
LVL 8

Expert Comment

by:venk_r
ID: 36587489
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
 

Author Closing Comment

by:data_bits
ID: 36589275
venk_r was very patient and informative
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now