Start Free Trial

asked on

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

Is this 64 bit server?If else is the AWE Configured to use more memory?

ASKER

It is 32-bit. I don't know if AWE is configured to do this

Whats the memory on this machine?

ASKER

There is 4GB of memory on the server.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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?

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.

ASKER

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?

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.

ASKER

How would I rollback to the original setting if needed?

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.

Also one more thing.
DBCC CHECKDB is very memory intensive operation. So make sure you run them during off peak hours.

ASKER

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. :-)

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>)

ASKER

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?

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.

ASKER

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.

Don't worry the steps that you r'e doing isnt something new. Its quite common.

ASKER

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

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

ASKER

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?

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!!

ASKER

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!

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?

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

ASKER

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?

ASKER

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?

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.

One more way to run DBCC commands to restore the backup on a different server(if you have one) and run the checks there.

ASKER

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.

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

ASKER

venk_r was very patient and informative