Custom Snapshot for CheckTable

You can take custom snapshots for CheckTable operations as well. Not only that, but you can take dynamic rotating snapshots to keep from running out of snapshot space. This is a tremendous feature for those of you who have huge DBs and have trouble running CheckDB. To download Minion CheckDB and to see full docs, go to http://MinionWare.net

Notes for this video:

Custom snapshots are only supported on SQL Server Enterprise up to 2016; 2016 SP1 lets you do it in Standard.

Why would you want to create a custom snapshot? On busy databases, internal snapshot can fill up.
Custom Dynamic Snapshots: With DBCC CheckTables, you can also continually drop and recreate the snapshot (between CheckTable operations) to keep the snapshot small. Enable dynamic snapshots by setting SnapshotRetMins greater than 0 (suggested: 30, 60, or 90 minutes) in Minion.CheckDBSettingsSnapshot. NOTE that in MC 1.0, custom dynamic snapshots must be SINGLE-threaded (@DBInternalThreads=1 or NULL).

Basic process:
1. Tell MC you want to take custom snapshot.
2. Tell MC where you want the files to go.

— 1, Minion.CheckDBSettingsSnapshot —

This table accepts default rows (DBName=MinionDefault). The most important columns: CustomSnapshot, DeleteFinalSnapshot, SnapshotFailAction.

(Why would a snapshot fail? Edition doesn’t allow it, or disk doesn’t exist, or acct doesn’t have permissions to create files or folders, etc.)

— 2, Minion.CheckDBSnapshotPath —
Allows default rows: DBName=MinionDefault (all databases) and FileName=Miniondefault (all files). You can insert DB level overrides, too. (Note that process creates the folder for you.)

Note: See Minion.CheckDBLogDetails for information on CheckDB/CheckTable runs. You can see custom snapshots in the log by noting the CheckDBNames (snapshot name) that are different from the DBNames. You can see custom dynamic snapshots in the log, because the CheckDBName changes within the same batch of operations.

Rate this video

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Leave a Reply