Sql-Server

SQL 事務日誌是否在循環結束時清除？

  • March 15, 2022

我正在執行 SQL Server 2019 企業版。每個月，我們都會執行一個儲存過程來載入數百萬條服務日期可以追溯到五年前的記錄。我為 4 個數據文件分配了 400GB 的空間，並為日誌文件分配了 100GB 的空間。作業經常失敗，因為日誌文件因活動事務而被填滿。數據庫處於簡單恢復模式。所以，我相信它應該在每筆交易結束時清除。開發人員更改了工作，使其一次循環並載入一年的記錄。

DROP TABLE IF EXISTS #UnpvtDx;

SELECT ClaimHeader_ID
   ,ClaimDetail_ID
   ,ClaimServiceLine
   ,Unpvt.CodeLine
   ,Unpvt.DxCode 
INTO #UnpvtDx
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT 
(
   DxCode FOR CodeLine
   IN
   (
       Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,Diagnosis4CD,Diagnosis5CD,
       Diagnosis6CD,Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
       Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,Diagnosis13CD
   )
) as Unpvt ---53 secs
WHERE YEAR(ServiceFromDT) = @year;

DROP TABLE IF EXISTS #UnpvtPointer;

SELECT ClaimHeader_ID
   ,ClaimDetail_ID
   ,ClaimServiceLine
   ,Unpvt.CodeLine
   ,Unpvt.Pointer 
INTO #UnpvtPointer
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT 
(
   Pointer FOR CodeLine 
   IN (DiagPointer1,DiagPointer2,DiagPointer3,DiagPointer4)
) as Unpvt ---40 secs
WHERE YEAR(ServiceFromDT) = @year;

INSERT INTO PROD.Claim.ClaimDiag
(
   ClaimHeader_ID,ClaimDetail_ID,SourceID,EDWLoadDTS,PartnerCD,
   PartnerNM,ClaimID,ClaimServiceLine,ClaimStatus,CCOMemberID,
   MemberID,PlaceOfServiceCD,ServiceFromDT,ServiceToDT,ClaimForm,
   TypeOfBillCD,DiagnosisCD,DiagnosisDESC,DiagPointer
)
SELECT DISTINCT
   det.ClaimHeader_ID,det.ClaimDetail_ID,det.SourceID,det.EDWLoadDTS,
   det.PartnerCD,det.PartnerNM,det.ClaimID,det.ClaimServiceLine,
   det.ClaimStatus,det.CCOMemberID,det.MemberID,det.PlaceOfServiceCD,
   det.ServiceFromDT,det.ServiceToDT,det.ClaimForm,det.TypeOfBillCD,
   DiagnosisCD = dx.DxCode,
   DiagnosisDESC = diag.DiagnosisDESC,
   DiagPointer = point.Pointer
FROM PROD.Claim.ClaimDetail det WITH (NOLOCK)
INNER JOIN PROD.Claim.ClaimHeader ch WITH (NOLOCK)
   ON ch.ClaimHeader_ID = det.ClaimHeader_ID
INNER JOIN #UnpvtDx dx
   ON dx.ClaimDetail_ID = det.ClaimDetail_ID
       AND dx.ClaimHeader_ID = det.ClaimHeader_ID
       AND dx.ClaimServiceLine = det.ClaimServiceLine
LEFT JOIN #UnpvtPointer point
   ON point.ClaimDetail_ID = det.ClaimDetail_ID
       AND point.ClaimHeader_ID = det.ClaimHeader_ID
       AND point.ClaimServiceLine = det.ClaimServiceLine
LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
   ON dx.DxCode = diag.DiagnosisCD
       AND diag.ICDVersion = 'ICD10CM'
       AND diag.ActiveFLG = 1
WHERE YEAR(det.ServiceFromDT) = @year;

使用以下命令從 SQL 代理作業執行儲存過程：

DECLARE @year INT
DECLARE cur CURSOR FOR 

SELECT yr = YEAR(hdr.MinServiceFromDT)
FROM PROD.Claim.ClaimHeader hdr WITH (NOLOCK)   
GROUP BY YEAR(hdr.MinServiceFromDT)
ORDER BY YEAR(hdr.MinServiceFromDT)

OPEN cur
FETCH NEXT FROM cur INTO @year
WHILE @@FETCH_STATUS = 0  
BEGIN  
   EXEC Claim.sp_UpdateClaimDiag @year

   FETCH NEXT FROM cur INTO @year      
END 

CLOSE cur
DEALLOCATE cur

循環的結束是否被認為是一個事務，因此在處理了每一年的記錄後日誌文件應該清空，或者日誌文件是否繼續填充，直到作業遍歷每個循環並載入所有年份的記錄？

我還將日誌文件增加到 150GB，但這會最大化可用空間（不會低於 10% 的緩衝區）。

發布整個儲存過程程式碼。

USE [Prod]
GO
   
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO
   
CREATE PROCEDURE [Claim].[sp_UpdateClaimDiag] @year INT
AS
   
BEGIN
   SET ANSI_DEFAULTS, ARITHABORT, NOCOUNT ON
   SET IMPLICIT_TRANSACTIONS OFF
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   
   -- variable declaration
   DECLARE
       @transactional BIT
       , @trancount INT
       , @err INT
       , @procname SYSNAME
       , @error INT
       , @message VARCHAR(4000)
       , @xstate INT
       , @RecordCount int;
   
   SELECT @procname = OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID())
       -- 0 =  no: will not execute batches inside a transaction; a partial success of procedure is possible
       -- 1 = yes: batches in procedure will be bound together by a transaction, partial success is impossible
       , @transactional = 0 
   
       -- optionally begin transaction and begin try block
       IF @transactional = 1 SET @trancount = @@TRANCOUNT

       BEGIN TRY
           IF @trancount = 0 and @transactional = 1
               BEGIN TRANSACTION

           ELSE IF @transactional = 1
               SAVE TRANSACTION p1
   
           ----------------------------------------------------------------------------------------
           ---Unpivot columns to Rows into Temp tables
           ----------------------------------------------------------------------------------------
               
           ---ICD9CM  & ICD10CM
           DROP TABLE IF EXISTS #UnpvtDx;

           SELECT
               ClaimHeader_ID
               ,ClaimDetail_ID
               ,ClaimServiceLine
               ,Unpvt.CodeLine
               ,Unpvt.DxCode 
           INTO #UnpvtDx
           FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
           UNPIVOT 
           (
               DxCode FOR CodeLine IN
               (
                   Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,
                   Diagnosis4CD,Diagnosis5CD,Diagnosis6CD,
                   Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
                   Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,
                   Diagnosis13CD
               )
           ) as Unpvt ---53 secs
           WHERE YEAR(ServiceFromDT) = @year;

           --Select top 100 * from #UnpvtDx where DxCode is null
   
           DROP TABLE IF EXISTS #UnpvtPointer;

           SELECT
               ClaimHeader_ID
               ,ClaimDetail_ID
               ,ClaimServiceLine
               ,Unpvt.CodeLine
               ,Unpvt.Pointer 
           INTO #UnpvtPointer
           FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
           UNPIVOT 
           (
               Pointer FOR CodeLine IN
               (
                   DiagPointer1, DiagPointer2,
                   DiagPointer3,DiagPointer4
               )
           ) as Unpvt ---40 secs
           WHERE YEAR(ServiceFromDT) = @year;
   
           --Select top 100 * from #UnpvtPointer
               
           ----------------------------------------------------------------------------------------
           --- INSERT INTO yearly records from the temp table
           ----------------------------------------------------------------------------------------
       
           INSERT INTO Prod.Claim.ClaimDiag (
               ClaimHeader_ID,
               ClaimDetail_ID,
               SourceID,
               EDWLoadDTS,
               PartnerCD,
               PartnerNM,
               ClaimID,
               ClaimServiceLine,
               ClaimStatus,
               CCOMemberID,
               MemberID,
               PlaceOfServiceCD,
               ServceFromDT,
               ServiceToDT,
               ClaimForm,
               TypeOfBillCD,
               DiagnosisCD,
               DiagnosisDESC,
               DiagPointer
           )
   
           SELECT DISTINCT
               det.ClaimHeader_ID,
               det.ClaimDetail_ID,
               det.SourceID,
               det.EDWLoadDTS,
               det.PartnerCD,
               det.PartnerNM,
               det.ClaimID,
               det.ClaimServiceLine,
               det.ClaimStatus,
               det.CCOMemberID,
               det.MemberID,
               det.PlaceOfServiceCD,
               det.ServiceFromDT,
               det.ServiceToDT,
               det.ClaimForm,
               det.TypeOfBillCD,
               DiagnosisCD = dx.DxCode,
               DiagnosisDESC = diag.DiagnosisDESC,
               DiagPointer = point.Pointer
           FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
           INNER JOIN Prod.Claim.ClaimHeader ch WITH (NOLOCK)
               ON ch.ClaimHeader_ID = det.ClaimHeader_ID
           INNER JOIN #UnpvtDx dx
               ON dx.ClaimDetail_ID = det.ClaimDetail_ID
                   AND dx.ClaimHeader_ID = det.ClaimHeader_ID
                   AND dx.ClaimServiceLine = det.ClaimServiceLine
           LEFT JOIN #UnpvtPointer point
               ON point.ClaimDetail_ID = det.ClaimDetail_ID
                   AND point.ClaimHeader_ID = det.ClaimHeader_ID
                   AND point.ClaimServiceLine = det.ClaimServiceLine
           LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
               ON dx.DxCode = diag.DiagnosisCD
                   AND diag.ICDVersion = 'ICD10CM'
                   AND diag.ActiveFLG = 1
           WHERE YEAR(det.ServiceFromDT) = @year
               --AND Year(det.ServiceFromDT) = 2021--for testing
               --and det.ClaimID ='21006E06455'--for testing
   
           ----------------------------------------------------------------------------------------
           --insert into updatelog table
           SET @RecordCount = @@ROWCOUNT;
   
           DECLARE @procName1 SYSNAME
           SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4))
           INSERT INTO Prod.dbo.UpdateLog(EventTimestamp,EventDescription,ProcName,TableName)
           SELECT GETDATE(),
               'Inserted ' + CAST(@RecordCount AS varchar(100)) + ' records',
               @procName1,
               'Claim.ClaimDiag'
   ----------------------------------------------------------------------------------------
           DROP TABLE IF EXISTS #UnpvtDx
           DROP TABLE IF EXISTS #UnpvtPointer
   ----------------------------------------------------------------------------------------
           SPEXIT:

           IF @transactional = 1 and @trancount = 0 COMMIT
       END TRY

       ----------------------------------------------------------------------------------------
       -- error handling with catch
       BEGIN CATCH
           SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE()
           IF @transactional = 1 and @xstate = -1 ROLLBACK
           IF @transactional = 1 and @xstate = 1 and @trancount = 0 ROLLBACK
           IF @transactional = 1 and @xstate = 1 and @trancount > 0 ROLLBACK TRANSACTION p1
       
           DROP TABLE IF EXISTS #claims
           SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4)) ---+ ', ' + CAST(@month AS varchar(4)) 
   
           RAISERROR ('%s, Error %d, %s', 16, 1, @procname1, @error, @message) 
           RETURN @error
       END CATCH
   
       RETURN 0  
   END
   GO

使用SIMPLE RECOVERY模型時，事務日誌在每個檢查點被截斷。不一定在您的交易結束時。檢查點每 60 秒發生一次。

查看程式碼的以下部分，您實際上根本沒有明確地開始事務。你最初set @transactional = 0，然後才開始一個事務如果@transactional = 1。此外，您只是@trancount根據IF語句在此處設置一個值。該塊仍然開始，因為您在語句之後BEGIN TRY沒有BEGIN/END塊。IF不過，這不是您的問題，它只是意味著您每次都在進行隱式事務，而不是創建顯式事務。

SELECT @procname = OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID())
   -- 0 =  no: will not execute batches inside a transaction; a partial success of procedure is possible
   -- 1 = yes: batches in procedure will be bound together by a transaction, partial success is impossible
   , @transactional = 0 
   
   -- optionally begin transaction and begin try block
   IF @transactional = 1 SET @trancount = @@TRANCOUNT

   BEGIN TRY
       IF @trancount = 0 and @transactional = 1
           BEGIN TRANSACTION

       ELSE IF @transactional = 1
           SAVE TRANSACTION p1

我懷疑即使一次處理數年的數據，您生成的事務日誌吞吐量也會超出驅動器的支持能力。這張表中一年的數據佔用了多少空間？

考慮將批量大小從一年減少到一個月，看看是否有幫助。當您使用它時，請停止使用 YEAR() 函式圍繞您的日期列。這使得這些列是非 SARGABLE，這意味著 SQL Server 在檢索數據時不能使用任何現有索引。這可能會導致您的事務執行時間超過所需時間，從而導致其他正在進行的事務在此事務執行時保留在日誌中。

考慮以下對儲存過程的更改。我將其簡化了一點，以突出要更改的重要部分。您需要將其與您擁有的內容合併並進行測試。這使您在呼叫過程時更容易控制批量大小。

另外，我刪除了NOLOCK提示。在此處閱讀更多內容以了解為什麼這是一個壞主意。NOLCOK由於非 SARGABLE where 子句，您可能認為您需要提示。

CREATE PROCEDURE [Claim].[sp_UpdateClaimDiag]
   @Interval int = 30
AS
BEGIN
   DECLARE
       @MinServiceFromDT DATETIME
       @MaxServiceFromDT DATETIME,
       @NextServiceFromDT DATETIME;

   SELECT
       @MinServiceFromDT = MIN(hdr.MinServiceFromDT),
       @MaxServiceFromDT = MAX(hdr.MinServiceFromDT)
   FROM PROD.Claim.ClaimHeader;

   WHILE @MinServiceFromDT <= @MaxServiceFromDT
   BEGIN
       BEGIN TRY
           @NextServiceFromDT = DATEADD(day,@Interval,@MinServiceFromDT);

           ---ICD9CM  & ICD10CM
           DROP TABLE IF EXISTS #UnpvtDx;

           DROP TABLE IF EXISTS #UnpvtPointer;
   
           --------------------------------------------------------------
           ---Unpivot columns to Rows into Temp tables
           --------------------------------------------------------------

           BEGIN TRANSACTION

           SELECT ClaimHeader_ID, ...
           INTO #UnpvtDx
           FROM Prod.Claim.ClaimDetail det
           UNPIVOT 
           (
               DxCode FOR CodeLine IN
               (
                   Diagnosis1CD,...
               )
           ) as Unpvt
           WHERE ServiceFromDT >= @MinServiceFromDT
               AND ServiceFromDT < @NextServiceFromDT;

           SELECT ClaimHeader_ID, ...
           INTO #UnpvtPointer
           FROM Prod.Claim.ClaimDetail det
           UNPIVOT 
           (
               Pointer FOR CodeLine IN
               (
                   DiagPointer1, DiagPointer2,
                   DiagPointer3,DiagPointer4
               )
           ) as Unpvt
           WHERE ServiceFromDT >= @MinServiceFromDT
               AND ServiceFromDT < @NextServiceFromDT;
           
           --------------------------------------------------------------
           --- INSERT INTO yearly records from the temp table
           --------------------------------------------------------------
       
           INSERT INTO Prod.Claim.ClaimDiag
               (ClaimHeader_ID, ....)
           SELECT DISTINCT det.ClaimHeader_ID, ....
           FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
           INNER JOIN Prod.Claim.ClaimHeader ch WITH (NOLOCK)
               ON ch.ClaimHeader_ID = det.ClaimHeader_ID
           INNER JOIN #UnpvtDx dx
               ON dx.ClaimDetail_ID = det.ClaimDetail_ID
                   AND dx.ClaimHeader_ID = det.ClaimHeader_ID
                   AND dx.ClaimServiceLine = det.ClaimServiceLine
           LEFT JOIN #UnpvtPointer point
               ON point.ClaimDetail_ID = det.ClaimDetail_ID
                   AND point.ClaimHeader_ID = det.ClaimHeader_ID
                   AND point.ClaimServiceLine = det.ClaimServiceLine
           LEFT OUTER JOIN Reference.Reference.Diagnosis diag
               ON dx.DxCode = diag.DiagnosisCD
                   AND diag.ICDVersion = 'ICD10CM'
                   AND diag.ActiveFLG = 1
           WHERE det.ServiceFromDT >= @MinServiceFromDT
               AND det.ServiceFromDT < @NextServiceFromDT;

           --------------------------------------------------------------
           --insert into updatelog table

           COMMIT TRANSACTION

           SET @ MinServiceFromDT = @NextServiceFromDT
       END TRY

       ------------------------------------------------------------------
       -- error handling with catch
       BEGIN CATCH
           <do error handling stuff>
       END CATCH
   END
END
GO

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

相關問答

Sql-Server

事務日誌：小事務與大事務以提高速度

  • January 19, 2022
檢視問答
Sql-Server

崩潰恢復過程如何在 SQL Server 中工作？

  • December 26, 2020
檢視問答
Sql-Server

為什麼 SQL Server 從 minLSN 而不是最後一個 CHECKPOINT 開始 REDO 階段？

  • April 8, 2020
檢視問答
Sql-Server

RAM 或物理文件中的事務日誌？

  • May 14, 2019
檢視問答
Sql-Server

sql server 中的事務日誌記錄

  • December 5, 2017
檢視問答
Sql-Server

如何辨識導致事務日誌增長的事務？

  • October 31, 2017
檢視問答