Just so you don't think I've forgotten you, I'm working on this today.
OK, so far I've been unable to repro the issue on my side. However, I've got something for you to try and if it doesn't work then we'll hookup on webex and look at it live.
However, let's try this.
I want to reset your rotation so it starts over.
TRUNCATE TABLE minion.checkdbthreadqueue
TRUNCATE TABLE minion.CheckDBRotationTables
TRUNCATE TABLE minion.CheckDBCheckTableThreadQueue
TRUNCATE TABLE minion.CheckDBRotationTablesReload
TRUNCATE TABLE minion.CheckDBRotationDBsReload
TRUNCATE TABLE minion.CheckDBRotationDBs
Next I want you to set the rotation default. Set the DefaultTimeEstimateMins col in the CheckDBSettingsDB table. When deciding whether to continue with a rotation, MC has to know how long it takes to do a CHECKDB, but if there's never been one on that DB before then it's got to estimate *something*. So this tells the MC what you want it to use as the default estimate when there's no previous run to use. You can use overrides for individual DBs just like always so if you've got a larger DB you can use a much more appropriate default. However, let's start with something that'll make I show us a rotation. If you've got the rotation set for 60mins, lets set this setting to 30mins. This way it'll only do 2-3 DBs tops, I would hope. Anyway, try that and get back to me with the results. Also, make sure LogSkips is turned on. We'll want to see them in the log when they're skipped.
Sorry been fighting deadlocks...
Changes made, jobs set to run, so Friday morning should see if it worked.
Anders Pedersen
Backstory:
2 servers, each with 900+ databases ranging from about 10GB to a few TB.
Not much traffic at night due to the nature of our business (heavies usage would be between 7 AM East coast time, to about 7 PM West coast. No idea if we have users in Hawaii, Alaska or territories, if we do not many).
1 server with fewer databases, but one that is 7 TB. Regular CHECKDB gets through everything but this one database.
My main concern right now is the 2 servers with tons of databases.
Here is what I am thinking, with fine tuning as I see it in action:
Dynamic thresholds around 100 GB (default) for switching to TABLECHECK. This should allow most of the smaller client databases to run CHECKDB.
Rotational Scheduling, where it run 3 hours (1 AM to 4 AM) per day during weekdays, 8 hours (1 AM to 9 AM) during the weekend. Weekend some time would be interrupted earlier if there is patching on the server, being able for it to restart if it does would be optimal.
This is what I have done on my own test server to test it, hopefully not too far off.
So I set the Rotation for only Time to be IsActive = 1.
Leave the System CHECKDB active from 22:00 to 22:30.
AUTO active for the User databases, two records, one for Weekday and one for Weekend.
On these 2 servers, I THINK I could increase NumConcurrentOps and DBInternalThreads, only backups should really be running at that time of night, with maybe a few occasional users working way too late.
Comments and suggestions welcome. First time I am implementing one of your solutions.