推薦：揭秘SQL Server數(shù)據(jù)庫系統(tǒng)的編譯
你知道SQL Server這么龐大的企業(yè)級(jí)數(shù)據(jù)庫服務(wù)器產(chǎn)品是如何build出來的嗎? 這有些相關(guān)的數(shù)據(jù)： 每個(gè)build 的大小在300GB左右。 每個(gè)完整的build需要幾十臺(tái)高端的服務(wù)器運(yùn)行2.5天。 每個(gè)完整的build由幾千個(gè)job、10000多個(gè)參數(shù)組成。 我們每天同時(shí)做20個(gè)左右的

介紹:SQL Server 2008變更數(shù)據(jù)捕獲

SQL Server 2008的CDC函數(shù)讀取激活了CDC的每個(gè)表所關(guān)聯(lián)的事務(wù)日志來記錄系統(tǒng)表中的變更。它將這些文件寫到同一個(gè)數(shù)據(jù)庫的系統(tǒng)表中，這些系統(tǒng)表是可以通過直接查詢或系統(tǒng)函數(shù)來訪問的。

讓我們看些示例代碼并看看每一步有什么改變。

使用示例代碼來跟蹤可變部分

首先，我們從一個(gè)全新的數(shù)據(jù)庫開始。要跟蹤這些變更，打開對(duì)象瀏覽器并展開這些文件夾：

· Databases

· SQLServer Agent | Jobs

· (數(shù)據(jù)庫創(chuàng)建之后)Security | Roles | Database Roles

· (數(shù)據(jù)庫創(chuàng)建之后)Programmability | Stored Procedures | System Stored Procedures

· (數(shù)據(jù)庫創(chuàng)建之后)Programmability | Functions | Table-valued Functions

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE TestCdc

這為我們創(chuàng)建了新數(shù)據(jù)庫，但是沒有新作業(yè)。展開Tables文件夾和System Tables文件夾。現(xiàn)在是空的。

在數(shù)據(jù)庫TestCdc上激活變更數(shù)據(jù)捕獲

USE TestCdc

-- Turn on Change Data Capture at database level

EXEC sys.sp_cdc_enable_db

這個(gè)步驟需要一些時(shí)間。現(xiàn)在刷新System Tables文件夾。現(xiàn)在它下面有6個(gè)系統(tǒng)表：

· cdc.captured_columns

· cdc.change_tables

· cdc.ddl_history

· cdc.index_columns

· cdc.lsn_time_mapping

· dbo.systranschemas

注意，其中的5個(gè)是“cdc” schema 部分的。

在sys.all_objects表中還有四個(gè)新的看起來很奇怪的“SQL inline table-valued function”登錄：

· fn_cdc_get_all_changes_◦...◦

· fn_cdc_get_all_changes_...

· fn_cdc_get_net_changes_◦...◦

· fn_cdc_get_net_changes_...

(我使用了非比例字體，用“◦”來標(biāo)記空格，使得很清楚地看到在這些名稱中有嵌入的空格。)

下面是會(huì)返回這些函數(shù)的查詢：

SELECT [name] FROM sys.all_objects WHERE [name] LIKE '%...%'

還有更多的新存儲(chǔ)過程

· sp_batchinsert_lsn_time_mapping

· sp_ins_dummy_lsn_time_mapping

· sp_ins_instance_enabling_lsn_time_mapping

· sp_ins_lsn_time_mapping

現(xiàn)在還沒有新的作業(yè)或角色。

創(chuàng)建新的Schema和用戶表

CREATE SCHEMA MySchema

CREATE TABLE MySchema.Person (

PersonID int IDENTITY PRIMARY KEY,

FirstName varchar(32) NOT NULL,

LastName varchar(32) NOT NULL,

UpdateCt int NOT NULL DEFAULT 0

)

上面的代碼創(chuàng)建了一個(gè)新的用戶表，但是這個(gè)表還沒有激活CDC。

這個(gè)步驟不影響我們所跟蹤的函數(shù)/存儲(chǔ)過程/角色列表。

為新的用戶表激活CDC

EXEC sys.sp_cdc_enable_table

@source_schema = N'MySchema',

@source_name = N'Person',

@role_name = N'ChangeDataAccessRole',

@supports_net_changes = 1

這會(huì)為新的用戶表激活CDC。刷新上面所列的文件夾。我們現(xiàn)在有了

· 一個(gè)新的系統(tǒng)表(cdc.MySchema_Person_CT)

· 兩個(gè)新的作業(yè)(cdc.TestCdc_capture和cdc.TestCdc_cleanup)

· 一個(gè)新的數(shù)據(jù)庫角色(ChangeDataAccessRole)

· 兩個(gè)新的表值型函數(shù)

o fn_cdc_get_all_changes_MySchema_Person

o fn_cdc_get_net_changes_MySchema_Person

· 三個(gè)新的存儲(chǔ)過程：

o sp_batchinsert_389576426

o sp_insdel_389576426

o sp_upd_389576426

你可以獲得這些存儲(chǔ)過程(sp_helptext 'cdc.sp_batchinsert_389576426' 【你的數(shù)字可能不一樣】)的文本。很明顯這個(gè)存儲(chǔ)過程被生成了，而且是專用于這個(gè)用戶表的。

如果你為第二個(gè)表激活了CDC，那么你將獲得另一組的sp_batchinsert_n、sp_insdel_n和sp_upd_n存儲(chǔ)過程。

系統(tǒng)表cdc.MySchema_Person_CT

這個(gè)表具有以下字段：

· __$start_lsn

· __$end_lsn

· __$seqval

· __$operation

· __$update_mask

· PersonID

· FirstName

· LastName

· UpdateCt

作業(yè)cdc.TestCdc_capture

當(dāng)你研究這個(gè)捕捉作業(yè)時(shí)，你會(huì)發(fā)現(xiàn)它是很有趣的。從sysjobs，我們可以看出這個(gè)作業(yè)是屬于sa的，是在“REPL-LogReader”分類中，并有一個(gè)“CDC Log Scan Job”的描述。

從sysjobsteps，我們看到它有兩個(gè)步驟：

讀過這些細(xì)節(jié)之后，接下來有兩個(gè)步驟，但是我們將保留第二個(gè)，直到我們插入了一些數(shù)據(jù)之后再執(zhí)行

sp_helptext N'sys.sp_MScdc_capture_job'

SELECT * FROM sys.dm_cdc_log_scan_sessions

存儲(chǔ)過程Psys.sp_MScdc_capture_job和sys.sp_cdc_scan

sys.sp_MScdc_capture_job是開始整個(gè)CDC過程的存儲(chǔ)過程。它首先進(jìn)行一些安全檢查，然后調(diào)用sys.sp_cdc_scan，這將進(jìn)行實(shí)質(zhì)的工作。sys.sp_cdc_scan執(zhí)行變更數(shù)據(jù)捕獲日志掃描操作。

默認(rèn)情況下，sys.sp_cdc_scan在每個(gè)循環(huán)間進(jìn)行五秒鐘的延遲。在每個(gè)常規(guī)的循環(huán)中，它調(diào)用sp_replcmds。(這是個(gè)復(fù)制存儲(chǔ)過程。使用默認(rèn)(1)調(diào)用這個(gè)存儲(chǔ)過程會(huì)返回等待分配的下一個(gè)事務(wù)。在這個(gè)存儲(chǔ)過程中，默認(rèn)@maxtrans是500。)

最后，它從sys.dm_cdc_log_scan_sessions讀取來準(zhǔn)備調(diào)用sp_sqlagent_log_jobhistory，從而記錄作業(yè)歷史信息。

數(shù)據(jù)庫角色ChangeDataAccessRole

如果你打開新的ChangeDataAccessRole角色的屬性窗口，那么你會(huì)看到這個(gè)角色屬于“cdc”，并且具有對(duì)上面所列的兩個(gè)表值型函數(shù)的SELECT權(quán)限，除此之外沒有其它權(quán)限。

總結(jié)

研究這些細(xì)節(jié)使我們了解了Server 2008的CDC是怎樣執(zhí)行的。

分享：怎樣清除SQL Server日志
方法一 一般情況下，SQL數(shù)據(jù)庫的收縮并不能很大程度上減小數(shù)據(jù)庫大小，其主要作用是收縮日志大小，應(yīng)當(dāng)定期進(jìn)行此操作以免數(shù)據(jù)庫日志過大 1、設(shè)置數(shù)據(jù)庫模式為簡單模式：打開SQL企業(yè)管理器，在控制臺(tái)根目錄中依次點(diǎn)開Microsoft SQL Server--