| Name | Type | Description |
|---|---|---|
| ID | int |
Primary key row identifier. |
| DBType | varchar |
Database name. |
| OpName | varchar |
The name of the operation (usually, as passed into Minion.CheckDBMaster).
The AUTO option allows Minion CheckDB to choose the appropriate operation per database, based on settings in the Minion.CheckDBSettingsAutoThresholds table. For more information on this, see the section titled “How to: Configure Minion CheckDB Dynamic Thresholds”.
Valid values: CHECKTABLE CHECKDB AUTO CHECKALLOC |
| Day | varchar |
The day or days to which the settings apply.
See the discussion below for information about Day hierarchy and precedence.
Note that the least frequent “Day” settings – FirstOfYear, LastOfYear, FirstOfMonth, LastOfMonth – only apply to user databases, not to system databases.
Valid values: Daily Weekday Weekend [an individual day, e.g., Sunday] FirstOfMonth LastOfMonth FirstOfYear LastOfYear |
| ReadOnly | tinyint |
Readonly option; this decides whether or not to include ReadOnly databases in the operation, or to perform operations on only ReadOnly databases.
A value of 1 includes ReadOnly databases; 2 excludes ReadOnly databases; and 3 only includes ReadOnly databases.
Valid values: 1 2 3 |
| BeginTime | varchar |
The start time at which this schedule applies.
IMPORTANT: Must be in the format hh:mm:ss, or hh:mm:ss:mmm (where mmm is milliseconds), on a 24 hour clock. This means that both ’00:00:00’ and ’08:15:00:000’ are valid times, but ‘8:15:00:000’ is not (because single digit hours must have a leading 0). |
| EndTime | varchar |
The end time at which this schedule applies.
IMPORTANT: Must be in the format hh:mm:ss, or hh:mm:ss:mmm (where mmm is milliseconds), on a 24 hour clock. This means that both ’00:00:00’ and ’08:15:00:000’ are valid times, but ‘8:15:00:000’ is not (because single digit hours must have a leading 0). |
| MaxForTimeframe | int |
Maximum number of iterations within the specified timeframe (BeginTime to EndTime).
For more information, see “Table based scheduling” in the “Quick Start” section. |
| FrequencyMins | int |
The frequency (in minutes) that the operation should occur.
Note that actual frequency also depends on the SQL Agent job schedule. If FrequencyMins = 60, but the job runs every 12 hours, you will only get this operation every 12 hours.
However, if FrequencyMins = 720 (12 hours) and the job runs every hour, this CheckDB will occur every 720 minutes. |
| CurrentNumOps | int |
Count of operation attempts for the particular DBType, OpName, and Day, for the current timeframe (BeginTime to EndTime). |
| NumConcurrentOps | tinyint |
The number of concurrent processes used. This is the number of databases that will be processed simultaneously. This applies to both DBCC CheckDB or DBCC CheckTable. Warning: You can max out server resources very quickly if you use too many concurrent operations. For more information, see “About: Multithreading operations”. |
| DBInternalThreads | tinyint |
The number of tables that will be processed in parallel. This only applies to DBCC CheckTable operations. This setting overrides the DBInternalThreads column in Minion.CheckDBSettingsDB. Warning: You can max out server resources very quickly if you use too many concurrent operations. For more information, see “About: Multithreading operations”. |
| TimeLimitInMins | int |
The time limit to impose on this opertion, in minutes. |
| LastRunDateTime | datetime |
The last time an operation ran that applied to this particular scenario (DBType, OpName, Day, and timeframe). |
| Include | nvarchar |
The value to pass into the @Include parameter of the Minion.CheckDBMaster job; in other words, the databases to include in this attempt. This may be left NULL (meaning “all databases”). |
| Exclude | nvarchar |
The value to pass into the @Exclude parameter of the Minion.CheckDBMaster job; in other words, the databases to exclude from this attempt. This may be left NULL (meaning “no exclusions”). |
| Schemas | nvarchar |
The schemas on which to perform operations. May be a single schema, an explicit list, and/or LIKE expressions.
Applies only to CHECKTABLE operations.
Note that schemas apply to all databases. If you choose to limit to the dbo schema, the operation is limited to the dbo schema in all applicable databases. |
| Tables | nvarchar |
The tables on which to perform operations. May be a single schema, an explicit list, and/or LIKE expressions.
Applies only to CHECKTABLE operations.
Note that tables apply to all databases. If you choose to limit to tables named ‘T%’ schema, the operation is limited to ‘T%’ tables in all applicable databases. |
| BatchPreCode | varchar |
Precode to run before the entire operation. |
| BatchPostCode | varchar |
Precode to run after the entire operation. |
| Debug | bit |
Enable logging of special data to the debug tables.
For more information, see “Minion.CheckDBDebug” and “Minion.CheckDBDebugLogDetails”. |
| FailJobOnError | bit |
Cause the job to fail if an error is encountered. If an error is encountered, the rest of the batch will complete before the job is marked failed. |
| FailJobOnWarning | bit |
Cause the job to fail if a warning is encountered. If a warning is encountered, the rest of the batch will complete before the job is marked failed. |
| IsActive | bit |
Whether the current row is valid (active), and should be used in the Minion CheckDB process. |
| Comment | varchar |
For your reference only. You can label each row with a short description and/or purpose. |
Note that if you have two operations slated for the same window of time, a System database operation takes precedence over a User database operation; and a CHECKDB or AUTO operation takes precedence over a CHECKTABLE operation.
Configure the settings, and when they apply, in Minion.CheckDBSettingsDB. The schedule above doesn’t actually cover the “PHYSICAL_ONLY” aspect for our scenario. So, we must configure PHYSICAL_ONLY in Minion.CheckDBSettingsDB, with the proper time window. The following statement inserts rows for PHYSICAL_ONLY that applies to Weekdays (one row for CheckDB settings and one for CheckTable settings):
INSERT INTO [Minion].CheckDBSettingsDB
( [DBName], [OpLevel], [OpName], [Exclude], [GroupOrder], [GroupDBOrder],
[NoIndex], [RepairOption], [RepairOptionAgree], [AllErrorMsgs],
[ExtendedLogicalChecks], [NoInfoMsgs], [IsTabLock], [IntegrityCheckLevel],
[IsRemote], [ResultMode], [HistRetDays], [DefaultSchema], [DBInternalThreads],
[LogSkips], [BeginTime], [EndTime], [DayOfWeek], [IsActive], [Comment]
)
VALUES ('MinionDefault' -- DBName
, 'DB' -- OpLevel
, 'CHECKDB' -- OpName
, 0 -- Exclude
, 0 -- GroupOrder
, 0 -- GroupDBOrder
, 0 -- NoIndex
, 'NONE' -- RepairOption
, 0 -- RepairOptionAgree
, 1 -- AllErrorMsgs
, 0 -- ExtendedLogicalChecks
, 0 -- NoInfoMsgs
, 0 -- IsTabLock
, 'PHYSICAL_ONLY' -- IntegrityCheckLevel
, 0 -- IsRemote
, 'Full' -- ResultMode
, 60 -- HistRetDays
, 'dbo' -- DefaultSchema
, 1 -- DBInternalThreads
, 1 -- LogSkips
, '00:00:00' -- BeginTime
, '23:59:00' -- EndTime
, 'Weekday' -- DayOfWeek
, 1 -- IsActive
, 'MinionDefault PHYSICAL_ONLY CHECKDB on weekdays.') -- Comment
,
('MinionDefault' -- DBName
, 'DB' -- OpLevel
, 'CHECKTABLE' -- OpName
, 0 -- Exclude
, 0 -- GroupOrder
, 0 -- GroupDBOrder
, 0 -- NoIndex
, 'NONE' -- RepairOption
, 0 -- RepairOptionAgree
, 1 -- AllErrorMsgs
, 0 -- ExtendedLogicalChecks
, 0 -- NoInfoMsgs
, 0 -- IsTabLock
, 'PHYSICAL_ONLY' -- IntegrityCheckLevel
, 0 -- IsRemote
, 'Full' -- ResultMode
, 60 -- HistRetDays
, 'dbo' -- DefaultSchema
, 1 -- DBInternalThreads
, 1 -- LogSkips
, '00:00:00' -- BeginTime
, '23:59:00' -- EndTime
, 'Weekday' -- DayOfWeek
, 1 -- IsActive
, 'MinionDefault PHYSICAL_ONLY CheckTable on weekdays.'); -- Comment
We also need to update the two existing ‘MinionDefault’ rows, so they only apply to the weekend:
UPDATE Minion.CheckDBSettingsDB
SET DayOfWeek = 'Weekend'
WHERE DBName = 'MinionDefault'
AND IntegrityCheckLevel IS NULL;
The final result in Minion.CheckDBSettingsDB is:
| DBName | OpLevel | OpName | IntegrityCheckLevel | BeginTime | EndTime | DayOfWeek |
| MinionDefault | DB | CHECKDB | NULL | 00:00:00 | 23:59:00 | Weekend |
| MinionDefault | DB | CHECKTABLE | NULL | 00:00:00 | 23:59:00 | Weekend |
| MinionDefault | DB | CHECKDB | PHYSICAL_ONLY | 00:00:00 | 23:59:00 | Weekday |
| MinionDefault | DB | CHECKTABLE | PHYSICAL_ONLY | 00:00:00 | 23:59:00 | Weekday |