批量數據載入和事務日誌

我目前正在從事一個項目，該項目從平面文件 (csv) 批量導入數據，大約 18 個不同的文件，每個文件都通過一些儲存過程連結到一個特定的表。

我按照數據載入性能指南中建議的步驟進行操作。

數據庫處於BulkLogged恢復模式以最小化日誌記錄，當在包含 600000 行的文件上執行以下儲存過程時，出現錯誤

消息 9002，級別 17，狀態 4，過程 SP_Import__DeclarationClearanceHistory_FromCSV，第 34 行

數據庫的事務日誌已滿。要找出日誌中的空間不能被重用的原因，請參閱 sys.databases 中的 log_reuse_wait_desc 列

（出於測試目的，我在開始導入之前進行了完整備份）。

看著log_reuse_wait_desc我看到以下內容：

log_reuse_wait_desc 檢查點。所有其他導入都成功導入。

任何解決此問題的意見都將受到歡迎。

PROCEDURE [dbo].[SP_Import_DeclarationClearanceHistory_FromCSV]
   @FilePath [nvarchar](1000)
AS
BEGIN
   -- Creating a Temproary Table for importing the data from csv file.
   DBCC TRACEON(610)

   CREATE TABLE #DeclarationClearanceHistory
   (
         [ItemID] [int] IDENTITY(1, 1) NOT NULL ,
         [CMSDeclarationID] [bigint] NOT NULL ,
         [StatusCode] [nvarchar](10) NOT NULL ,
         [SubStatus] [nvarchar](10) NULL ,
         [DepartmentCode] [nvarchar](10) NULL ,
         [StartDate] [datetime] NULL ,
         [EndDate] [datetime] NULL ,
         PRIMARY KEY CLUSTERED ( [ItemID] ASC )
           WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                  ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
       )
   ON  [PRIMARY]

   -- Inserting all the from csv to temproary table using BULK INSERT
   EXEC ('BULK INSERT #DeclarationClearanceHistory
   FROM ''' + @FilePath + '''
   WITH ( FIELDTERMINATOR = ''<,>'', ROWTERMINATOR =''\n'', FIRSTROW = 2, KEEPIDENTITY, CODEPAGE = ''ACP'', ORDER = ''ITEMID ASC'' );') ;

   -- By using MERGE statement, inserting the record if not present and updating if exist.
   MERGE dbo.DeclarationClearanceHistory AS TargetTable                            -- Inserting or Updating the table.
       USING #DeclarationClearanceHistory AS SourceTable                           -- Records from the temproary table (records from csv file).
       ON ( TargetTable.ItemID = SourceTable.ItemID )      -- Defining condition to decide which records are alredy present
       WHEN NOT MATCHED BY TARGET 
           THEN INSERT (
                         ItemID ,
                         CMSDeclarationID ,
                         StatusCode ,
                         SubStatus ,
                         DepartmentCode ,
                         StartDate ,
                         EndDate
                       )
              VALUES   ( SourceTable.ItemID ,
                         SourceTable.CMSDeclarationID ,
                         SourceTable.StatusCode ,
                         SourceTable.SubStatus ,
                         SourceTable.DepartmentCode ,
                         SourceTable.StartDate ,
                         SourceTable.EndDate
                       )
       WHEN MATCHED -- If  matched then UPDATE
           THEN UPDATE
              SET      TargetTable.ItemID = SourceTable.ItemID ,
                       TargetTable.CMSDeclarationID = SourceTable.CMSDeclarationID ,
                       TargetTable.StatusCode = SourceTable.StatusCode ,
                       TargetTable.SubStatus = SourceTable.SubStatus ,
                       TargetTable.DepartmentCode = SourceTable.DepartmentCode ,
                       TargetTable.StartDate = SourceTable.StartDate ,
                       TargetTable.EndDate = SourceTable.EndDate ;
DBCC TRACEOFF(610)
END

我的第一條評論是您正在執行 ELT（提取、載入、轉換）而不是 ETL（提取、轉換、載入）。雖然 ELT 利用了基於集合的關係優勢並且速度非常快，但它們有時非常需要寫入（儲存困難）。具體來說，t-log。這是因為轉換是在磁碟上完成的（通常是更新或插入）。如果可能，我更喜歡 ETL，因為轉換是在緩衝區中完成的，並且如果正確完成，則需要最少的 t-log 寫入。緩衝很便宜。快速儲存不是。對於一些批量操作，t-log 是一個非增值瓶頸。

這是您正在做的一些事情，但我不推薦。

1. 批量載入到 tempdb。我建議對目標數據庫中的真實表進行批量載入。然後您可以相應地調整文件大小，而不必擔心影響 tempdb。
2. 將獨立的程序捆綁在一起。把這兩個過程分開。批量載入和合併是相互獨立的。將它們分成單獨的程序使它們更加模組化/可單元測試。

看起來您已經很好地涵蓋了最小的日誌記錄規則。您正在使用 tf 610（指定的排序鍵）在批量記錄模式下載入到沒有非集群的空 B 樹。在臨時表之外，這裡一切正常。只要文件實際上是按密鑰排序的，就應該很好。您是在 tempdb 還是使用者數據庫上彈出日誌？

在合併語句上：

UPDATES 將始終被完整記錄。你改變了你的桌子相當重要的一部分嗎？如果是這樣，您可能會考慮在記憶體中進行合併（SSIS 數據流任務或 .Net），然後將批量載入到新表中。這是更多的工作，但大部分工作都是在緩衝區中完成的，並且使用了最少的 t-log。如果更改部分很重要，則最少記錄的插入可能比完全記錄的更新更快。

由於您使用的是 tf 610，因此在使用製表符提示時插入可以最少地記錄。有關與 tabblock 合併的更多資訊，請參見此處：連結注意，如果您走這條路線，更新仍將被完全記錄。

引用自：https://dba.stackexchange.com/questions/30734