“A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated.” - MSDN article “Database Snapshots” (https://msdn.microsoft.com/en-us/library/ms175158.aspx)
When you run DBCC CheckDB or DBCC CheckTable, behind the scenes SQL Server creates a snapshot of the database to run the operation against. SQL Server decides where to place the files for these snapshots, and deletes the snapshot after the operation is complete.
If your version of SQL Server supports it, you can also choose to create a custom snapshot (CustomSnapshot=1). For more information, and to learn how to configure custom snapshots, see “About: Custom Snapshots” and “How to: Configure Custom Snapshots”.
Note: SQL Server 2016 and earlier versions only allow custom snapshots for Enterprise edition. SQL Server 2016 SP1 allow custom snapshots in any edition.
Note that Minion CheckDB comes with two “MinionDefault” rows in this table – one for CHECKDB and one for CHECKTABLE – both with CustomSnapshot = 0. These are example rows so you can easily enable custom snapshots.
| Name | Type | Description |
|---|---|---|
| ID | int |
Primary key row identifier. |
| DBName | varchar |
Database name. |
| OpName | varchar |
The name of the operation (usually, as passed into the Minion.CheckDBMaster procedure from Minion.CheckDBSettingsDB).
Valid values: CHECKTABLE CHECKDB |
| CustomSnapshot | bit |
Enable or disable custom snapshots.
IMPORTANT: If custom snapshots are enabled, MC requires active rows in Minion.CheckDBSnapshotPath to determine where the custom snapshot will go.
Note: If CustomSnapshot is enabled and your version of SQL Server doesn’t support it, that integrity check operation will complete using the default internal snapshot. For more information, see the “Custom snapshots fail” section under Troubleshooting. |
| SnapshotRetMins | int |
The number of minutes to retain a snapshot, before recreating it. This only applies to rows with OpName=’CHECKTABLE’.
For more information, see “How to: Configure Custom Snapshots”. |
| SnapshotRetDeviation | int |
This field is not yet in use. |
| DeleteFinalSnapshot | bit |
Whether to delete the last snapshot taken during an operation. |
| SnapshotFailAction | varchar |
The action to take if the custom snapshot fails. For example, if you the custom snapshot location doesn’t exist, or you don’t have permissions to it, or some other problem exists, then this field determines how to proceed.
FAIL will fail with a logged error. Default behavior. CONTINUE will allow MC to continue with an internal snapshot, and will log the error in the Warnings column of the log table.
Valid values: NULL <this is the same as FAIL> |
| BeginTime | Varchar |
The start time at which these settings apply. Can be NULL, meaning “no start limit”.
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 these settings apply. Can be NULL, meaning “no end limit”.
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). |
| DayOfWeek | varchar |
The day or days to which the settings apply.
Valid inputs: NULL (meaning, all days) Daily Weekday Weekend [an individual day, e.g., Sunday] |
| 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. |