We are experiencing an issue on one of our servers where CheckDB is blowing the resource pool 'default' ... let me elaborate..
Windows 2012 R2 Standard - 16GB RAM SQL Server 2012 Enterprise [11.0.5532.0] MAX memory 14GB RAM, MIN memory 0GB This is a dedicated SQL Server running 1 default instance of Microsoft SQL Server. Vanilla install with no special requirements or configuration.
We can CheckDB all the other databases (max size of 3GB per database, 9 user databases) however when we CheckDB the 5GB database it fails after 10 minutes reporting
"There is insufficient system memory in resource pool 'default' to run this query. [SQLSTATE 42000] (Error 50000) DBCC execution completed. If DBCC printed error messages, contact your system administrator."
This is a vendor database and cannot be altered .. it consists of 2 filegroups, both filegroups contain 248 tables that each contain 1,000 partitions.
I have tried
DBCC CheckDB with combinations of
ALL_ERRORMSGS NO_INFOMSGS in case that made a difference to memory requirements but there was no change in behaviour. We cannot use
TABLOCK as we will not be able to lock tables. I have tried DBCC CheckFileGroup and this returns the same error message.
We can try running PhysicalOnly for a lightweight check but we want to do a full CheckDB once a week.
The total disk space used (not allocated) to all the databases is 8,797 MB .. the RAM allocation to SQL Server and available is 14GB [14,336 MB]
My question is .... the entire 5GB database should fit into memory (and I have checked that more than 5GB of RAM is available..... would this volume of partitions and partitioned tables in a database using 1 or 2 filegroups cause excessive memory consumption? I cannot find any Microsoft documentation that says partitioned tables require more memory...only that partitioned tables over many filegroups can help reduce CheckDB time/resource requirements for VLDB.
I am running
DBCC CheckDB WITH ESTIMATEONLY now and will update once I have the results.
UPDATE: ESTIMATEONLY returns the following messages:
DBCC results for ''. CHECKDB found 0 allocation errors and 0 consistency errors in database ''. Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query.
Any input will be greatly appreciated!