[sql-server] 로그없이 SQL에서 테이블의 대용량 데이터를 삭제하는 방법은 무엇입니까?

1. 해당 테이블의 모든 행을 삭제하는 경우 가장 간단한 옵션은 테이블을 자르는 것입니다.

   TRUNCATE TABLE LargeTable
   GO

   Truncate table은 단순히 테이블을 비우고 WHERE 절을 사용하여 삭제되는 행을 제한 할 수 없으며 트리거가 실행되지 않습니다.

2. 반면에 데이터의 80-90 % 이상을 삭제하는 경우 총 1 천 1 백만 행이 있고 1 천만 행을 삭제하려는 경우 다른 방법은이 1 백만 행을 삽입하는 것입니다 (보관하려는 레코드 )을 다른 스테이징 테이블에 추가합니다. 이 대형 테이블을 자르고이 100 만 행을 다시 삽입하십시오.

3. 또는이 테이블을 기본 테이블로 사용하는 권한 /보기 또는 기타 개체가이 테이블을 삭제해도 영향을받지 않는 경우 상대적으로 적은 양의 행을 다른 테이블로 가져 와서이 테이블을 삭제하고 동일한 스키마를 가진 다른 테이블을 만들고 가져올 수 있습니다. 이 ex-Large 테이블에 행을 다시 넣습니다.

4. 내가 생각할 수있는 마지막 옵션은 데이터베이스를 변경 Recovery Mode to SIMPLE한 다음 이와 같은 while 루프를 사용하여 더 작은 일괄 처리로 행을 삭제하는 것입니다.

   DECLARE @Deleted_Rows INT;
   SET @Deleted_Rows = 1;
   
   
   WHILE (@Deleted_Rows > 0)
     BEGIN
      -- Delete some small number of rows at a time
        DELETE TOP (10000)  LargeTable
        WHERE readTime < dateadd(MONTH,-7,GETDATE())
   
     SET @Deleted_Rows = @@ROWCOUNT;
   END

그리고 복구 모드를 다시 전체로 변경하는 것을 잊지 마십시오. 완전히 적용되도록하려면 백업을 수행해야한다고 생각합니다 (변경 또는 복구 모드).

답변

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN

   BEGIN TRANSACTION

   -- Delete some small number of rows at a time
     DELETE TOP (10000)  LargeTable
     WHERE readTime < dateadd(MONTH,-7,GETDATE())

     SET @Deleted_Rows = @@ROWCOUNT;

   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model
END

DELETE TOP (10000)  [TARGETDATABASE].[SCHEMA].[TARGETTABLE]
WHERE readTime < dateadd(MONTH,-1,GETDATE());
-- how many times you want the query to repeat
GO 100

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
BEGIN
    BEGIN TRANSACTION

    -- Delete some small number of rows at a time
    DELETE TOP (10000)  LargeTable
    WHERE readTime < dateadd(MONTH,-7,GETDATE())

    SET @Deleted_Rows = @@ROWCOUNT;

    COMMIT TRANSACTION
    CHECKPOINT -- for simple recovery model

END

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time
    delete top (100000) from InstallLog where DateTime between '2014-12-01' and '2015-02-01'
    SET @Deleted_Rows = @@ROWCOUNT;
    dbcc shrinkfile (MobiControlDB_log,0,truncateonly);
END