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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply