sys.fn_cdc_get_min_lsn (Transact-SQL)sys.fn_cdc_get_min_lsn (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

傳回從指定的擷取執行個體的 start_lsn column 值cdc.change_tables系統資料表。Returns the start_lsn column value for the specified capture instance from the cdc.change_tables system table. 這個值代表擷取執行個體的有效性間隔低端點。This value represents the low endpoint of the validity interval for the capture instance.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax


sys.fn_cdc_get_min_lsn ( 'capture_instance_name' )  

引數Arguments

' capture_instance_name '' capture_instance_name '
這是擷取執行個體的名稱。Is the name of the capture instance. capture_instance_namesysnamecapture_instance_name is sysname.

傳回類型Return Types

binary(10)binary(10)

備註Remarks

當擷取執行個體不存在,或者呼叫端未經授權,無法存取與擷取執行個體相關聯的變更資料時,便傳回 0x00000000000000000000。Returns 0x00000000000000000000 when the capture instance does not exist or when the caller is not authorized to access the change data associated with the capture instance.

這個函數通常是用於識別與擷取執行個體相關聯之異動資料擷取時間表的低端點。This function is typically used to identify the low endpoint of the change data capture timeline associated with a capture instance. 您也可以在要求變更資料之前,使用這個函數來驗證查詢範圍的端點是否都位於擷取執行個體時間表之內。You can also use this function to validate that the endpoints of a query range fall within the capture instance timeline before requesting change data. 請您務必執行這類檢查,因為在變更資料表上執行清除時,擷取執行個體的低端點就會變更。It is important to perform such checks because the low endpoint of a capture instance changes when cleanup is performed on the change tables. 如果變更資料要求之間的時間很長,即使是上一次變更資料要求時設定為高端點的低端點也可能位於目前時間表以外。If the time between requests for change data is significant, even a low endpoint that is set to the high endpoint of the previous change data request might lie outside the current timeline.

PermissionsPermissions

需要系統管理員 (sysadmin) 固定伺服器角色或 db_owner 固定資料庫角色中的成員資格。Requires membership in the sysadmin fixed server role or db_owner fixed database role. 若為所有其他使用者,則需要來源資料表中所有擷取資料行的 SELECT 權限,而且如果定義了擷取執行個體的控制角色,便需要該資料庫角色的成員資格。For all other users, requires SELECT permission on all captured columns in the source table and, if a gating role for the capture instance was defined, membership in that database role.

範例Examples

A.A. 針對指定的擷取執行個體傳回最小 LSN 值Returning the minimum LSN value for a specified capture instance

下列範例會針對 AdventureWorks2012AdventureWorks2012 資料庫中的擷取執行個體 HumanResources_Employee 傳回最小 LSN 值。The following example returns the minimum LSN value for the capture instance HumanResources_Employee in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2-12;  
GO  
SELECT sys.fn_cdc_get_min_lsn ('HumanResources_Employee')AS min_lsn;  

B.B. 驗證查詢範圍的低端點Verifying the low endpoint of a query range

下列範例會使用 sys.fn_cdc_get_min_lsn 所傳回的最小 LSN 值來驗證變更資料查詢的建議低端點是否適用於 HumanResources_Employee 擷取執行個體的目前時間表。The following example uses the minimum LSN value returned by sys.fn_cdc_get_min_lsn to verify that the proposed low endpoint for a change data query is valid for the current timeline for the capture instance HumanResources_Employee. 這則範例會假設擷取執行個體的上一個高端點 LSN 已儲存而且可設定 @save_to_lsn 變數。This example assumes that the previous high endpoint LSN for the capture instance was saved and is available to set the @save_to_lsn variable. 為了執行此範例,@save_to_lsn 會設定為 0x000000000000000000,以便強制執行錯誤處理區段。For the purposes of this example, @save_to_lsn is set to 0x000000000000000000 to force the error-handling section to run.

USE AdventureWorks2012;  
GO  
DECLARE @min_lsn binary(10), @from_lsn binary(10),@save_to_lsn binary(10), @to_lsn binary(10);  
-- Sets @save_to_lsn to the previous high endpoint saved from the last change data request.  
SET @save_to_lsn = 0x000000000000000000;  
-- Sets the upper endpoint for the query range to the current maximum LSN.  
SET @to_lsn = sys.fn_cdc_get_max_lsn();  
-- Sets the @min_lsn parameter to the current minimum LSN for the capture instance.  
SET @min_lsn = sys.fn_cdc_get_min_lsn ('HumanResources_Employee');  
-- Sets the low endpoint for the query range to the LSN that follows the previous high endpoint.  
SET @from_lsn = sys.fn_cdc_increment_lsn(@save_to_lsn);  
-- Tests to verify the low endpoint is valid for the current capture instance.  
IF (@from_lsn < @min_lsn)  
    BEGIN  
        RAISERROR('Low endpoint of the request interval is invalid.', 16, -1);  
    END  
ELSE  
-- Return the changes occurring within the query range.  
    SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Employee(@from_lsn, @to_lsn, 'all');  
GO  

另請參閱See Also

sys.fn_cdc_get_max_lsn (Transact-SQL) sys.fn_cdc_get_max_lsn (Transact-SQL)
交易記錄 (SQL Server)The Transaction Log (SQL Server)