Minion CheckDB offers you a choice of scheduling options:

  • You can use the Minion.CheckDBSettingsServer table to configure flexible scheduling scenarios;
  • Or, you can use the traditional approach of one job per integrity check schedule;
  • Or, you can use a hybrid approach that employs a bit of both options.

For more information, see “Changing Schedules” in the Quick Start section, and “How To: Change Schedules”.
 

Table based scheduling

When Minion CheckDB is installed, it uses a single job (MinionCheckDB-AUTO) to run the stored procedure Minion.CheckDBMaster with no parameters, once every hour.

When the Minion.CheckDBMaster procedure runs without parameters, it uses the Minion.CheckDBSettingsServer table to determine its runtime parameters (including the schedule of jobs per database type). This is how MC operates by default, to allow for the most flexible scheduling with as few jobs as possible.

Table based scheduling presents multiple advantages:

  • A single job – Multiple jobs are, to put it simply, a pain. They’re a pain to update and slow to manage, as compared with using update and insert statements on a table.
  • Fast, repeatable configuration – Keeping your schedules in a table saves loads of time, because you can enable and disable schedules, change frequency and time range, etc. all with an update statements. This also makes standardization easier: write one script to alter your schedules, and run it across all Minion CheckDB instances (instead of changing dozens or hundreds of jobs).
  • Mass updates across instances – With a simple PowerShell script, you can take that same script and run it across hundreds of SQL Server instances, standardizing your entire enterprise all at once.
  • Transparent scheduling – Multiple jobs tend to obscure the maintenance scenario, because each piece of the configuration is displayed in separate windows. Table based scheduling allows you to see all aspects of the schedule in one place, easily and clearly.
  • Boundless flexibility – Table based scheduling provides an amazing degree of flexibility that would be very troublesome to implement with multiple jobs. With one job, you can schedule all of the following:
    • System DBCC CheckDBs three days a week.
    • User DBCC CheckDBs on weekend days and Wednesday.
    • User DBCC CheckTables twice daily for specific schemas.
    • …and each of these can also use Dynamic Thresholds, which can also be slated for different sizes, applicable at different times and days of the week and year.
 

 

Parameter Based Scheduling

Other SQL Server native integrity check solutions traditionally use one job per schedule. Typically and at a minimum, that means one job for system database CheckDBs, and another job for user database CheckDBs.

Note: Whether you use table based or parameter based scheduling, we highly recommend always using the Minion.CheckDBMaster stored procedure to run integrity check operations. While it is possible to use the Minion.CheckDB procedure or Minion.CheckDBCheckTable to execute integrity checks, doing so will bypass much of the configuration and logging benefits that Minion CheckDB was designed to provide.
 

Discussion: Hierarchy and Precedence

There is an order of precedence to the schedule settings in Minion.CheckDBSettingsServer, from least frequent (First/LastOfYear) to most frequent (daily); the least frequent setting, when it applies, takes precedence over all others. For example, if today is the first of the year, and there is a FirstOfYear setting, that’s the one it runs.

The full list, from most frequent, to least frequent (and therefore of highest precedence), is:

  1.  Daily
  2. Weekday / Weekend
  3. Monday / Tuesday / Wednesday / Thursday / Friday / Saturday / Sunday
  4. FirstOfMonth / LastOfMonth
  5. FirstOfYear / LastOfYear

Note that the least frequent “Day” settings – FirstOfYear, LastOfYear, FirstOfMonth, LastOfMonth – only apply to user databases, not to system databases. System databases may have “Day” set to a day of the week (e.g., Tuesday), WeekDay, WeekEnd, Daily, or NULL (which is equivalent to “Daily”).
 

 

Discussion: Overlapping Schedules, and MaxForTimeframe

The Minion.CheckDBSettingsServer table allows you to have integrity check schedule settings that overlap.

Note: We recommend against overlapping schedules, as there is no guarantee of precedence. If you have a day and time window scheduled for DB1 CheckDB, for example, and an overlapping window for DB1 CheckTable, there is no set precedence for which one will run.

Use adjacent day and time windows for individual databases or sets of databases. For example, we could perform DBCC CheckTable operations on specific DB1 tables every 6 hours from 1am to 7pm, and then run a full DBCC CheckDB every night at 11pm. For this scenario, we would:

  • Insert 1 row for the DB1 CheckTable, with a MaxForTimeframe value of 4 and FrequencyMins = 360 (6 hours). Set BeginTime = 01:00:00, and EndTime = 19:00:00.
  • Insert one row for the DB1 CheckDB, with a MaxForTimeframe value of 1. Set BeginTime = 23:00:00, and EndTime = 23:59:00.

The sequence of job executions then goes like this:

  1. The MinionCheckDB-AUTO job kicks off at 1:00 am.
  2. MC determines that a CheckTable operation is slated for DB1 tables, and executes the CheckTable operation.
  3. MC also increments the CheckTable row’s CurrentNumCheckDBs for that timeframe.
  4. The MinionCheckDB-AUTO job continues to run hourly until 7am, when MC sees that it’s time for another CheckTable run (based on the MaxForTimeframe field).
  5. Steps 2-4 repeat, CheckTable running again at 1pm and 7pm.
  6. At 11pm, MC sees that the CheckDB is due, runs it, and increments the CheckDB row’s CurrentNumCheckDBs.

 

Discussion: Using FrequencyMins

The FrequencyMins column allows you to run the “MinionCheckDB-AUTO” SQL Agent job as often as you like, but to space operations out by a set interval. Let’s say that the job runs every hour, but DBCC CheckDB (PHYSICAL_ONLY) for DB1 should only run every 12 hours. Just set FrequencyMins = 720 for the CheckDB/DB1 row.