作為MS SQL Server數(shù)據(jù)庫(kù)恢復(fù)這個(gè)系列的第一篇文章，我們看一下如果一個(gè)數(shù)據(jù)庫(kù)存在快照數(shù)據(jù)庫(kù)，那么當(dāng)執(zhí)行DBCC CHECK命令時(shí)，是否會(huì)自動(dòng)使用已存在的快照數(shù)據(jù)庫(kù)呢？我一直認(rèn)為是不會(huì)的，并且也這樣告訴其它人。為了證明給我自己以及其它人，本篇將嘗試最終去證明DBCC CHECK命令將不會(huì)使用已存在的數(shù)據(jù)庫(kù)快照。

For my first post of this MS SQL Server Disaster Recovery, I want to take a look at the myth that the DBCC CHECK commands will automatically use an existing database snapshot if one exists for the database. This is a myth I believed myself at one time and told to others. This was done to prove it to myself as much as to anyone else. This is an attempt to prove definitively that the DBCC CHECK commands will not use an existing snapshot for a database.

執(zhí)行DBCC CHECK命令時(shí)是否會(huì)自動(dòng)使用已存在的快照數(shù)據(jù)庫(kù) ?

我做了大量DBCC CHECK命令的調(diào)查試圖找到辦法查看DBCC CHECK命令是否有隱示的創(chuàng)建和使用數(shù)據(jù)庫(kù)快照，最終發(fā)現(xiàn)快照沒(méi)有顯示在sys.databases, sys.master_file以及其它的系統(tǒng)目錄中，另外，數(shù)據(jù)庫(kù)快照的創(chuàng)建不會(huì)觸發(fā)服務(wù)器級(jí)別的事件也不會(huì)觸發(fā)庫(kù)級(jí)別的事件以及SQL跟蹤和擴(kuò)展事件。

I was doing some bigger investigations into DBCC CHECK commands (post to come) and was looking for a way to see that hidden snapshot the DBCC CHECK commands create and use. The snapshots are not visible in sys.databases, sys.master_files, nor any other system catalog that I could find. Additionally, the snapshot creation does not trigger server level events for a DDL trigger nor the database create or database start events for SQL Trace or Extended Events.

最終，我發(fā)現(xiàn)通過(guò)擴(kuò)展示件的databases_dbcc_logical_scan事件可以看到它，這個(gè)事件會(huì)返回當(dāng)前正針對(duì)某個(gè)數(shù)據(jù)庫(kù)運(yùn)行的數(shù)據(jù)庫(kù)的ID以及實(shí)際正在哪個(gè)數(shù)據(jù)庫(kù)上操作的數(shù)據(jù)庫(kù)ID，當(dāng)前實(shí)際操作的數(shù)據(jù)庫(kù)是一個(gè)隱藏的數(shù)據(jù)為快照，它的database_id不會(huì)顯示在sys.databases,而且DB_NAME()函數(shù)會(huì)返回它的源數(shù)據(jù)庫(kù)的名稱(chēng)，我設(shè)置了一個(gè)擴(kuò)展事件會(huì)話來(lái)收集databases_dbcc_logical_scan事件，它含有database_id和database_name列。我使用ring buffer target因?yàn)槲也⒉淮蛩惚Ａ羧魏螖?shù)據(jù)，執(zhí)行下面的腳本，它暫時(shí)還未激活。

I found it in the [i]databases_dbcc_logical_scan[/i] event via Extended Events. The event reports database_id of the database the command was run against as well as the database_id of the database where the action is actually occurring. If the action database is the hidden snapshot, the database_id will not show up in sys.databases, but the DB_NAME() function will return the name of the source database. I set up an Extended Events session to capture this event with the columns database_id and database_name. I use the ring buffer target because I don’t intend to retain any of this data. This session will not be active yet.

CREATE EVENT SESSION [TestSnap] ON SERVER 
ADD EVENT sqlserver.databases_dbcc_logical_scan(
    ACTION(sqlserver.database_id,
        sqlserver.database_name)) 
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB);

然后為AdventureWorks2012數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)快照庫(kù)AWSnap，這樣我們就有了一個(gè)已存在的數(shù)據(jù)庫(kù)和它的快照庫(kù)。

Then I create a snapshot of the AdventureWorksDW2012 database called AWSnap so there is an existing snapshot.

CREATE DATABASE AWSnap
    ON (NAME = N'AdventureWorks2012_Data',
        FILENAME = N'D:\SQL2012\SNP\AdventureWorks2012_Data.ndf')
        AS SNAPSHOT OF AdventureWorks2012;

現(xiàn)在啟用擴(kuò)展事件會(huì)話，如下圖點(diǎn)擊Start Session，然后再點(diǎn)擊Watch Live Data，這樣當(dāng)對(duì)AdventureWorks2012數(shù)據(jù)庫(kù)運(yùn)行DBCC CHECKDB時(shí)就能實(shí)時(shí)看到事件內(nèi)容，接下來(lái)我們打開(kāi)一個(gè)查詢(xún)窗口運(yùn)行DBCC CHECKDB。

Now, using Object Explore in SQL Server Management Studio (SSMS), I start the Extended Events session (expand Management -> expand Extended Events -> right-click on the session -> click Start Session). Then I right-click on the session and click on Watch Live Data. This allows me to see the events in real-time while DBCC CHECKDB is running against the AdventureWorksDW2012 database. Next I open a new query window and run DBCC CHECKDB.

從下圖中我們可以看到每一個(gè)記錄中，database_id和database_id(action)都是不同的，如果你查詢(xún)sys.databases那么沒(méi)有database_id(action)的ID記錄，如果我們用DB_NAME(7)查數(shù)據(jù)庫(kù)名稱(chēng)，當(dāng)DBCC CHECKDB正在運(yùn)行時(shí)，它返回AdventureWorks2012，當(dāng)DBCC CHECKDB運(yùn)行完后，它返回NULL.

We can see by looking at any one entry in the Extended Events session that the database_id and database_id (Action) are different database IDs. If you query sys.databases, you will see that database_id of 6 is the AdventureWorksDW2012 database and there is no entry for the database_id (Action). If we check the action database_id with the DB_NAME() function while the CHECKDB is running, it will return AdventureWorksDW2012, but after DBCC completes, it will return NULL.

接下來(lái)我們做另一個(gè)測(cè)試，先關(guān)掉剛才的Live Data 窗口并重新打開(kāi)一個(gè)新的，以保證全部清空記錄，再對(duì)AWSnap庫(kù)運(yùn)行DBCC CHECKDB，我們可以看到database_id和database_id(action)現(xiàn)在是相同的值，如果查詢(xún)sys.databases能夠看到它的值對(duì)應(yīng)到數(shù)據(jù)庫(kù)AWSnap.

For the next part of the test, I close out the Live Data window and reopen it so that it is all clear again. Then I run DBCC CHECKDB against the snapshot AWSnap. checking the Live Data window, we see that the database_id and database_id (Action) are now the same value. If you query sys.databases, you will see that the value for both maps to the snapshot AWSnap.

總結(jié)：
正如你所看到的那樣，如果你想讓DBCC CHECKDB使用已存在的數(shù)據(jù)庫(kù)快照，需要特別指定快照數(shù)據(jù)庫(kù)，否則DBCC CHECKDB不會(huì)自動(dòng)檢測(cè)并使用已存在的快照數(shù)據(jù)庫(kù)。

As I have shown here, if you want to control the snapshot that DBCC uses, you need to specifically run DBCC on the snapshot itself. It won’t detect and use the snapshot just because one exists. The reason why this is important to understand will become apparent in a later post. So be sure to check back and see how I build on this information.

本文系轉(zhuǎn)載，如有侵犯版權(quán)問(wèn)題，請(qǐng)通知。我們立即刪除。

轉(zhuǎn)載請(qǐng)注明：成都千喜數(shù)據(jù)恢復(fù)中心 » SQL Server 災(zāi)難恢復(fù)之一：DBCC CHECK命令會(huì)自動(dòng)使用已經(jīng)存在的數(shù)據(jù)庫(kù)快照嗎？