搜尋本站文章

2010-10-13

設定「大型物件(LOB)」資料同步最大之上限,以使用「交易式複寫(Transactional Replication)」技術為例;Error: 7139

若是資料表中使用到資料類型:text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)等,一般統稱為「大型物件(Large Object,LOB)」。

雖然這類的資料類型,可以存放到 2 GB的資料量。
而且在使用「交易式複寫」上,也是可以使用到這些「大型物件(Large Object,LOB)」。

但是依據預設值,最大可以進行複寫資料是:65536 (位元組,Byte)。
換算下來,差不多是:65536/1024/1024 = 0.0625 (MB)。

若是資料量超過上限,將會遭遇到以下的錯誤訊息,請參考下圖所示:
Error: 7139, Severity: 16, State: 1
Length of LOB data (247504) to be replicated exceeds configured maximum 65536.
The statement has been terminated.
--
訊息 7139,層級 16,狀態 1,行 7
要複寫的 LOB 資料 (67504) 長度超過設定的最大值 65536。
陳述式已經結束。




調整 max text repl size 選項,可以使用 T-SQL 與 SSMS 管理工具來實踐。

(1)使用 T-SQL 陳述式來調整:
--01 查詢是否已經啟用 max text repl size (B)
-- 預設值為 65536,-1 表示沒有任何限制 (除了資料類型所加諸的限制以外)。
SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值', value_in_use N'這個選項目前有效的執行值',  
 description N'組態選項的描述'  ,minimum N'組態的最小值', maximum N'組態的最大值'
FROM sys.configurations  
WHERE name='max text repl size (B)'  
請參考下圖所示:

--02 設定啟用 max text repl size (B) :使用 sp_configure 來啟用 max text repl size (B)
-- 設定會立即生效,伺服器不必重新啟動。
USE master;  
GO  
EXEC sp_configure 'show advanced option', '1';  
GO  
RECONFIGURE  
GO  
--  
EXEC sp_configure'max text repl size (B)'  , '-1'  
RECONFIGURE WITH OVERRIDE  
GO  
  
--03 再度查詢是否已經啟用 max text repl size (B)
SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值', value_in_use N'這個選項目前有效的執行值',  
 description N'組態選項的描述'  ,minimum N'組態的最小值', maximum N'組態的最大值'
FROM sys.configurations  
WHERE name='max text repl size (B)'  
請參考下圖所示:



(2)使用 SSMS 管理工具來調整:
步驟01. 在SSMS管理工具,在「物件總管」,點選指定的伺服器,滑鼠右鍵,選擇「屬性」。

步驟02. 在「伺服器屬性」視窗,點選「進階」頁籤,在「其他」區域,設定「文字複寫大小上限」的屬性值。
請參考下圖所示:



關於 max text repl size 選項

max text repl size 選項可用來指定在單一 INSERT、UPDATE、WRITETEXT 或 UPDATETEXT 陳述式中,可以加入至複寫資料行或擷取資料行中的 text、ntext、varchar(max)、nvarchar(max)、varbinary(max)、xml 和 image 資料的大小上限 (以位元組為單位)。
預設值為 65536,-1 表示沒有任何限制 (除了資料類型所加諸的限制以外)。

這個選項適用於「交易式複寫」和「異動資料擷取」。當同時針對交易式複寫和異動資料擷取設定伺服器時,指定的值會套用到這兩個功能。
快照式複寫與合併式複寫會忽略這個選項。

設定會立即生效,伺服器不必重新啟動


交易式複寫在使用大型物件 (LOB) 資料類型的考量:

在使用「交易式複寫」上,若是資料表中使用到資料類型:text、ntext、image,建議請改用:varchar(max)、nvarchar(max)、varbinary(max)來取代。

如果您使用的是 text、ntext 或 image,請注意下列問題:

(1)WRITETEXT 和 UPDATETEXT 陳述式應在外顯交易中換行。

(2)記錄文字作業可透過使用已發行資料表上具有 WITH LOG 選項的 WRITETEXT 與 UPDATETEXT 來複寫。 WITH LOG 選項是必要的,因為交易式複寫會在交易記錄中追蹤變更。

(3)只有所有的「訂閱者」都執行 SQL Server,才能使用 UPDATETEXT 作業。 WRITETEXT 作業複寫為 UPDATE 陳述式,因此它們也可以與非 SQL Server 訂閱者一起使用。

(4)可設定的參數 max text repl size 會控制可複寫的 text、ntext 、varchar(max)、nvarchar(max) 和 image 資料的最大值 (以位元組為單位)。
這允許支援:ODBC 驅動程式和 OLE DB 提供者;無法處理這些資料類型大數值的 SQL Server Database Engine 執行個體;以及擁有系統資源 (虛擬記憶體) 條件約束的「散發者」。
當其中一個資料類型的資料行發行後,且執行超過設定限制的 INSERT、UPDATE、WRITETEXT 或 UPDATETEXT 作業時,作業就會失敗。

(5)發行 text、ntext 和 image 資料行時,應擷取與 UPDATETEXT 或 WRITETEXT 作業相同交易之內的文字指標 (並可重複讀取)。
例如,不要在某一個交易中擷取文字指標,然後在另一個交易中使用。 它可能已經移動過而且已經無效。


參考資料:
交易式複寫考量
http://technet.microsoft.com/zh-tw/library/ms151254.aspx

max text repl size 選項
http://msdn.microsoft.com/zh-tw/library/ms186225.aspx

sp_configure (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms188787.aspx