Minion CheckDB by MinionWare is a free stand-alone integrity check solution that can be deployed on 
any number of servers.  Minion CheckDB is comprised of SQL Server tables, stored procedures, and 
SQL Agent jobs.  For links to downloads, tutorials, and articles, see http://MinionWare.net.
 
This document explains Minion CheckDB by MinionWare (“Minion CheckDB”), its uses, features, moving 
parts, and examples. 
 
ImageFor video tutorials on Minion CheckDB, see the Minion CheckDB playlist on our YouTube channel: 
https://www.youtube.com/MidnightDBA   
 
Minion CheckDB is one module of  the Minion suite of products.  There are three easter eggs in this 
documentation; find all three and email us at  MinionWareSales@MidnightDBA.com for three free 
licenses of Minion Enterprise!   (First time winners only, please.) 
 

System requirements

  • SQL Server 2008 or above.
  • The sp_configure setting xp_cmdshell must be enabled*.
  • PowerShell 3.0 or above; execution policy set to RemoteSigned.
Once the installer has been run, nothing else is required.  From here on, Minion CheckDB will run 
regularly for all non-TempDB databases.  The CheckDB routine automatically handles databases as 
they are created, dropped, or renamed.  
 
* xp_cmdshell can be turned on and off with the database PreCode / PostCode options, to help comply with security policies. For more information on xp_cmdshell, see “Security Theater” on www.MidnightDBA.com/DBARant.
 
This entire document is also available within the installed Minion CheckDB database using the SQL stored procedure Minion.HELP.

Read the "Minion Install Guide.docx" (contained within the MinionMaintenance1.1.zip file) for full instructions and information on using the installer. The basic steps to installing Minion CheckDB  are:
 
  1. Download MinionMaintenance1.1.zip from MinionWare.net and extract all files to the location of your choice, replacing any existing MinionWare installer folders from previous downloads.
  2. Open Powershell as an administrator, and use Get-ExecutionPolicy to verify the current execution policy is set to Unrestricted or RemoteSigned. If it is not, use Set-ExecutionPolicy RemoteSigned to allow the installer to run.
  3. Right-click on each of the following files, select Properties, and then “Unblock” the file if necessary. (This allows you to run scripts downloaded from the web using the RemoteSigned execution policy.)
    1. …\MinionWare\MinionSetupMaster.ps1
    2. …\MinionWare\MinionSetup.ps1
    3. …\MinionWare\Includes\CheckDBInclude.ps1
  4. Run MinionSetupMaster.ps1 in the PowerShell administrator window as follows: .\MinionSetupMaster.ps1 <servername> <DBName> <Product>
Examples: 
.\MinionSetupMaster.ps1 localhost master CheckDB
or
.\MinionSetupMaster.ps1 YourServer master CheckDB
 
Note that you can install multiple products, and to multiple servers. For more information, see the Minion Install Guide.docx.
 
For simplicity, this Quick Start guide assumes that you have installed Minion CheckDB on one server, named “YourServer”.

Customizing Schedules

Minion CheckDB offers a choice of scheduling options. This quick start section covers the default method of scheduling: table based scheduling. We will cover parameter based schedules, and hybrid schedules, in the section titled “How To: Change Schedules”. For more information, see “About: Scheduling”.

Table based scheduling

In conjunction with the “MinionCheckDB-AUTO” job, the Minion.CheckDBSettingsServer table allows you to configure flexible CheckDB scheduling scenarios. By default, Minion CheckDB is installed with the following configuration: 
 
The MinionCheckDB-AUTO job runs hourly, checking the Minion.CheckDBSettingsServer table to determine what operation should be run.
 
In the Minion.CheckDBSettingsServer table:
  • System database CheckDB operations are scheduled daily at 10:00pm.
  • User database CheckDB operations are scheduled for Saturdays at 11:00pm.
The following table displays the first few columns of this default scenario in Minion.CheckDBSettingsServer: 
ID  DBType   OpName   Day       ReadOnly  BeginTime EndTime   MaxForTimeframe

1   System   CHECKDB  Daily     1         22:00:00  22:30:00  1

2   User     CHECKDB  Saturday  1         23:00:00  23:30:00  1
Note: There is also an inactive row for User databases to run AUTO operations Saturday at 11:00 pm. For information about OpName = AUTO, see “About: Dynamic Thresholds” and “How to: Configure Dynamic Thresholds”. For an example of a complex scenario that includes OpName=AUTO, see “About: Minion CheckDB Operations”.
 
Let’s walk through two different schedule change scenarios:
 
Scenario 1: Run CheckDB on user databases daily. To change the default setup to run daily CheckDBs on all user databases, update the row with DBType=’User’ & OpName=‘CHECKDB’, setting the Day field to “Daily”.
 
Scenario 2: Run CheckTable twice daily for specific schemas.  To change the default setup in order to run CheckTable twice daily on two specific schemas (in this example, Import and Ace), insert a new row to Minion.CheckDBSettingsServer for CheckDBType=’CheckTable’ and Schemas=’Import,Ace’: 
 
INSERT  INTO Minion.CheckDBSettingsServer
        ( DBType
        , OpName
        , Day
        , ReadOnly
        , BeginTime
        , EndTime
        , MaxForTimeframe
        , FrequencyMins
        , Schemas
        , Debug
        , FailJobOnError
        , FailJobOnWarning
        , IsActive
        , Comment 
        )
VALUES  ( 'User' -- DBType 
        , 'CHECKTABLE'   -- OpName 
        , 'Daily' -- Day 
        , 1 -- ReadOnly 
        , '04:00:00'   -- BeginTime 
        , '18:00:00'   -- EndTime 
        , 2 -- MaxForTimeframe 
        , 720 -- FrequencyMins 
        , 'Import,Ace' -- Schemas
        , 0 -- Debug 
        , 0 -- FailJobOnError 
        , 0 -- FailJobOnWarning 
        , 1 -- IsActive 
        , 'Twice daily CHECKTABLE operations'  -- Comment 
        );
In the scenario above there are a few critical concepts to understand:
  • Execution Window: The BeginTime and EndTime settings will restrict this CheckTable entry to between 4:00am and 6:00pm.  Minion CheckDB will ignore this entry outside of that execution window.
  • Frequency: FrequencyMins=720 means that this schedule (row) will only run once in any 720 minute (12 hour) period, regardless of how many times Minion CheckDB is schedule to run. 
  • Always set the MaxForTimeframe field. This setting determines the maximum number of times an operation may be executed in the defined timeframe. In the insert statement above, MaxForTimeframe is set to 2, because we only want to allow a maximum of 2 CheckTable operations during the daily window (between 4am and 6pm).
  • The Schemas setting applies to all databases: What’s more, Schemas=’Import,Ace’. This means that the run will only apply to tables within the “Import” and “Ace” schemas in any database on the system. (The Schemas and Tables fields apply to all databases.)

Default Settings

Minion CheckDB stores default settings for the entire instance in two rows (where DBName=’MinionDefault’) in the Minion.CheckDBSettingsDB table.
 
Warning: Do not delete the MinionDefault rows, or rename the DBName for the MinionDefault row, in Minion.CheckDBSettingsDB!
 
To change the default settings, run an update statement on the MinionDefault / CHECKDB row (or the MinionDefault / CHECKTABLE row) in Minion.CheckDBSettingsDB.  For example:
UPDATE  Minion.CheckDBSettingsDB
SET     NoInfoMsgs = 1
      , HistRetDays = 75
      , ResultMode = 'Summary'
WHERE   DBName = 'MinionDefault'
        AND OpName = 'CHECKDB';