4

Understanding the circumstances under which SQL Server creates a snapshot when running CHECKDB, I followed a link to this KB that states: 

Database snapshots are available only in SQL Server Enterprise.

I'm trying to plan some maintenance work and need to understand potential I/O bottlenecks that could extend this maintenance window.

Does this snapshot limitation imply that running DBCC commands on SQL Server Standard SKUs will always run against the actual database, or is an internal snapshot still used?

Elliot Rodriguez
  • 227
  • 1
  • 2
  • 8

1 Answers1

2

You can refer to Paul Randal's blog (the guy who wrote DBCC CHECKDB) :

CHECKDB From Every Angle: Complete description of all CHECKDB stages :

For SQL Server 2005, CHECKDB uses an internal database snapshot to provide the required transactional consistency.

So when CHECKDB starts in SQL Server 2005, the first thing it does is work out whether it can run online – if so it creates a hidden database snapshot of the source database (i.e. CHECKDB’s target database). As the database snapshot is hidden, there is no control over where the snapshot files are placed – in fact they are created as alternate streams of the files comprising the source database.

From SQL Server 2014 onward, alternate streams are not used, although the database snapshot is created in the same location as the existing database.

As a side note, since you are using Standard edition, DBCC CHECKDB will not perform parallel checking of objects as opposed to Enterprise edition wherein it honors the MAXDOP setting configured for the server instance.

Also, BOL has a clear writeup on CHECKDB Internal Database Snapshot

Community
  • 1
Kin Shah
  • 60,908
  • 5
  • 111
  • 230