SQL Server disaster recovery 31-1st days: Does the DBCC check Command automatically use existing database snapshots?

Note:Disaster recovery SeriesOfArticleYesby Robert Davis Written in sqlsoldier,I personally think it is quite good, so according to my own understanding, testing and sorting are not directly translated. If there is any inaccuracy, please correct me.

 

As the first article in the disaster recovery series, let's take a look at whether an existing Snapshot database is automatically used when running the DBCC check command if a database has a snapshot database? I always thought it would not, and told others like this. To prove to myself and others, this article will ultimately prove that the DBCC check Command will not use an existing database snapshot.

When running the DBCC check command, will the existing Snapshot database be used automatically?

I did a large number of DBCC check commands and tried to find a way to check whether the DBCC check Command implicitly creates and uses database snapshots. Finally, I found that the snapshots were not displayed in SYS. databases, sys. master_file and other system directories. In addition, creating database snapshots does not trigger server-level events, database-level events, and SQL tracking and scaling events.

In the end, I found that I can see it through the databases_dbcc_logical_scan event of the expanded display piece. This event will return the ID of the database that is currently running for a database and the ID of the database on which the operation is actually being performed, the actual database operated on is a snapshot of the hidden data. Its database_id is not displayed in SYS. databases, and the db_name () function will return the name of its source database. I set an extended Event session to collect the databases_dbcc_logical_scan event, which contains the database_id and database_name columns. I use ring buffer target because I do not intend to keep any data and execute the following script. It is not activated yet.

CreateEvent session[Testsnap] OnServerAddEvent sqlserver. databases_dbcc_logical_scan (action (sqlserver. database_id, sqlserver. database_name ))AddTarget package0.ring _ BufferWith(Max_dispatch_latency=30Seconds, max_event_size=0KB );
 

Create a snapshot library awsnap for the adventureworks2012 database, so that we have an existing database and its snapshot library.

Create DatabaseAwsnapOn(Name=N'Adventureworks2012_data', Filename=N'D: \ sql2012 \ SNP \ adventureworks2012_data.ndf')AsSnapshotOfAdventureworks2012;
 

Now enable extended Event sessions. For example, click Start session and then watch live data. This allows you to view the event content in real time when running DBCC checkdb on the adventureworks2012 database, next, we open a query window to run DBCC checkdb.

We can see that database_id and database_id (Action) are different in each record. If you query sys. databases does not have the database_id (Action) ID record. If we use db_name (7) to query the database name, when DBCC checkdb is running, it returns adventureworks2012. After DBCC checkdb is running, it returns NULL.