When you run DBCC CheckDB or DBCC CheckTable, behind the scenes, SQL Server creates a snapshot of the database to run the operation against. If your version of SQL Server supports it, you can also choose to create a custom snapshot and configure where its files are created.

Note: SQL Server 2016 and earlier versions only allow custom snapshots for Enterprise Edition. SQL Server 2016 SP1 allow custom snapshots in any edition.

You might want to create a custom snapshot if an operation takes long enough that the internal snapshot would grow too large (and risk filling up the drive), which would stop the operation. You can also – for CheckTable operations only – create and recreate “Custom Dynamic Snapshots” (see the following section) at timed intervals, to prevent the snapshot file from getting too large.

Minion CheckDB provides several options for custom snapshots:

  • Assign a different drive for each file, or put them all onto a single drive.
  • Change the location for just one file.
  • Delete the snapshot after your operation is done, or keep it to fold it into your normal snapshot rotation.

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.

IMPORTANT: SQL Server does not allow you to specify log files or filestream files in a CREATE SNAPSHOT statement. The MSDN article “FILESTREAM Compatibility with Other SQL Server Features” provides more information: “When you are using FILESTREAM, you can create database snapshots of standard (non-FILESTREAM) filegroups. The FILESTREAM filegroups are marked as offline for those database snapshots.”

For more information, see:

Custom Dynamic Snapshots

Custom snapshots allow you to determine where the snapshot file(s) will be located. For CheckTable, custom snapshots allow you both to set the file locations, and to drop and recreate the snapshot every few minutes (which we call “custom dynamic snapshots”).

IMPORTANT: Custom dynamic snapshots for CheckTable are only available for single-threaded operations. This means that you must set DBInternalThreads in Minion.CheckDBSettingsDB, and DBInternalThreads in Minion.CheckDBSettingsServer, to 1 for custom dynamic snapshots.

Note: The only difference between custom snapshots for CheckTable, and “rotating” custom dynamic snapshots for CheckTable – those that drop and recreate every few minutes – is that a rotating snapshot has “SnapshotRetMins” set to a value greater than zero.

Important notes:

  • Minion.CheckDBSettingsSnapshot (DeleteFinalSnapshot): It’s a good idea to delete the snapshot after your operation is done, but it’s not necessary. You might want to fold it into your normal snapshot rotation.
  • Minion.CheckDBSettingsSnapshot (SnapshotRetMins): You can set up dynamic snapshots that are dropped and recreated every N minutes, for CheckTable oeprations. (The SnapshotRetMins column does not apply to CheckDB operations, as you can only drop and recreate the snapshot for CheckTable.)
  • Hierarchy rules: The same rules apply in both Minion.CheckDBSettingsSnapshot and Minion.CheckDBSnapshotPath for database overrides: Make sure you have one row for CheckDB and one for CheckTable for MinionDefault, and CheckDB/CheckTable rows for each individual database you configure in these tables.
  • Logging: The Minion.CheckDBSnapshotLog table shows you all the files and the statement used to create the snapshot. This is mostly for troubleshooting, but it also has a column that shows you the maximum size that each of the files reached. This is for planning; you can make sure that any given disk will have enough space. You’re welcome.

Notes for troubleshooting:

  • This table is where we store the “create database” snapshot command for custom snapshots.
  • You can read from this table to make sure the files are being created, that they’re being created in the right location, and with the correct name, and so on.
  • One of the last columns in this table is the MaxSizeInMB column, which shows you the size of the snapshot. That can help you plan the size of the drives you need to put the snapshots on.

Note: If you run a CheckDB operation from SvrA remotely (in disconnected mode) on SvrB, and if SvrB has custom snapshots configured, then this table will hold records of the custom snapshot file(s) in this table on SvrB.

For more information, see: