July 1, 2010 at 10:17 pm
I have a 2008 issue and I realize this is the 2005 forum, but the 2008 forum doesn't have a corruption section so I decided to post here...
I have been working / researching / testing for 3 days on this problem...any thoughts our suggestions would be highly welcome. Ok, forget that, I'm begging for help at this point...
When I run DBCC CHECKDB (myDatabase)
The error message:
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 701, Level 17, State 123, Line 1
There is insufficient system memory in resource pool 'internal' to run this query.
The specs are:
SQL Server 2008 Express
Windows 2003
4 GB Memory
Main table is 1.5 GB with 7m rows
Table has some datetime2 columns, 2 geography columns, and 1 varchar(max)
The error also occurs on DBCC CHECKTABLE (theBigTable)
But here is where it gets weird...I can duplicate this error with a new database by recreating the tables and loading in the original data.
These are some the tests I ran:
1) Exported all data from original table into a BCP file
2) Created a new database from scratch
3) Tested DBCC CHECKDB (passed)
4) Created the main table
4) Loaded the data
5) Tested DBCC CHECKDB again and failed
I also tried this:
1) Created new database from scratch
2) Tested DBCC CHECKDB (passed)
3) Created the table
3) Loaded 2 million rows of data
4) Tested DBCC CHECKDB again and passed
And finally this:
1) Created new database from scratch
2) Tested DBCC CHECKDB (passed)
3) Created three copies of the same table
3) Loaded all 6 million rows into 3 tables (2 million rows each)
4) Tested DBCC CHECKDB again and passed
Does this mean I need to horizontally partition this table in order to pass my DBCC checks?
Has anyone ever seen this before?
I've read through the existing posts on SSC that reference this issue (as well as various Google results) but I've near seen a clear resolution...
Some other information:
1) We are not using Resource Governor
2) AWE is not enabled
3) We are not using CLR
4) Many of the stored procedures use "sp_executesql." I've read that this issue can be attributed to extended stored procedures, but I wasn't sure if that applied to dynamic SQL
5) I have duplicated the issue with SQL Server 2008 Developer Edition on the same machine
July 2, 2010 at 2:35 am
Yup - if CHECKDB can't get the memory it needs, it will fail. It processes tables in batches, with at least one table plus indexes per batch. I'm surprised it's failing though - what indexes does the table have?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 2, 2010 at 10:08 am
There are three indexes:
Clustered index on composite primary key (datetime2 and int)
Spatial index on first geography column
Spatial index on second geography column
Here are the sizes:
Clustered: 1638464 KB
Spatial1: 245496 KB
Spatial2: 245752 KB
These were calculated by doing sum(page_count * 8) from the sys.dm_db_index_physical_stats DMV and joining against sys.indexes (found the query online).
I don't know if any of this additional information matters, but I'm going to throw it out anyway:
1) I am able to run a successful DBCC CHECKDB on the database on my laptop. The laptop is running SQL Server 2008 Express, Windows 7 and has 4GB of memory.
2) The server that is getting the error is a virtual machine.
3) There are 8 other instances of SQL Server installed (some Express, some SQL Server 2005 Development) but they have all been shutdown. These instances existed before I started working on the machine.
4) I also tried running DBCC CHECKDB WITH TABLOCK but got the same error.
5) The server was originally using 2GB of virtual memory. I changed the option to "System Settings" and now it's configured for 4GB. But that didn't help.
I read many posts online about how the MemToLeave value might be too low but:
1) I still don't think I fully undertand what it is.
2) I'm not sure if you can even do memory configurations on SQL Server Express since it has a 1GB limit on RAM in the first place
July 2, 2010 at 10:25 am
Yup - I think this table is just too big for the memory limitations of Express under pressure from the other instances on the box. Splitting things up should allow it to work.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 2, 2010 at 10:52 am
I guess I'll split up the tables...
Can I infer from your answer that we wouldn't have this problem with Standard Edition? I have a feeling the additional development time get around many of our Express limitations will end up costing for than $5-7K.
And I don't even know if this is possible, but is there a DMV, PerfMon counter, or something in DBCC MEMORYSTATUS that could allow me to show someone else where / when the memory was running out?
I'm not looking for specific details, but maybe blog posting or white paper or something to point me in the right direction.
Thanks very much for your time.
July 3, 2010 at 3:27 am
No - you can't 100% infer that. With 8 instances installed on a 4GB memory server, you're going to run into memory constraints and you may still have issues like this.
You're going to have to monitor the memory clerk that DBCC is using (see here[/url]) using the sys.dm_os_memory_clerks DMV. Look for the MEMORYCLERK_SQLUTILITIES memory clerk.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 3, 2010 at 11:07 am
This is great. Thanks again for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply