SQL 테이블의 대용량 데이터를 로그 없이 삭제하는 방법

SQL 테이블의 대용량 데이터를 로그 없이 삭제하는 방법

저는 큰 데이터 테이블을 가지고 있습니다.이 표에는 천만 개의 레코드가 있습니다.

이 쿼리에 대한 가장 좋은 방법은 무엇입니까?

   Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())

1. 해당 표의 모든 행을 삭제하는 경우 가장 간단한 옵션은 표를 잘라내는 것입니다.

    TRUNCATE TABLE LargeTable
    GO
   

테이블을 잘라내면 테이블이 비워질 뿐이며 WHERE 절을 사용하여 삭제할 행을 제한할 수 없으며 트리거가 실행되지 않습니다.

2. 한편 데이터의 80~90% 이상을 삭제할 경우 총 1100만 행이 있으며 1000만 행을 삭제할 경우에는 이 100만 행(보존할 레코드)을 다른 스테이징 테이블에 삽입하는 방법이 있습니다.이 큰 테이블을 잘라내고 이 100만 행을 다시 삽입합니다.

3. 또는 이 큰 테이블을 기본 테이블로 하는 사용 권한/뷰 또는 기타 객체가 이 테이블을 삭제해도 영향을 받지 않으면 비교적 적은 수의 행을 다른 테이블로 가져와서 이 테이블을 삭제한 후 동일한 스키마를 사용하여 다른 테이블을 만든 다음 이러한 행을 이 초대형 테이블로 다시 가져올 수 있습니다.

4. 할 수 의 "Database를 입니다.Recovery Mode to SIMPLE그런 다음 다음과 같은 while loop을 사용하여 작은 배치의 행을 삭제합니다.

    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
   

recovery 모드를 full로 되돌리는 것을 잊지 마십시오.이 모드를 완전하게 유효하게 하려면 , 백업을 실시할 필요가 있다고 생각합니다(변경 모드 또는 recovery 모드).

@m-ali의 답변은 맞지만 각 청크 후에 트랜잭션을 커밋하지 않고 체크포인트를 실행하지 않으면 로그가 크게 증가할 수 있습니다.퍼포먼스 테스트와 그래프를 사용하여 이 기사 http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes을 참조합니다.

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

또한 GO + 동일한 쿼리를 실행할 횟수를 사용할 수도 있습니다.

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

@프란시스코 골든스타인, 약간의 수정일 뿐입니다.변수를 설정한 후에는 COMMIT를 사용해야 합니다.그렇지 않으면 WHILE은 한 번만 실행됩니다.

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

이 M의 변형입니다.알리가 나한테 잘 먹혀.일부를 삭제하고 로그를 클리어하고 반복합니다.나는 통나무가 자라고, 떨어지고, 다시 시작하는 것을 보고 있다.

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

파티셔닝을 실장할 의향이 있는 경우(및 실장할 수 있는 경우), 런타임 오버헤드를 거의 사용하지 않고 대량의 데이터를 삭제할 수 있는 효과적인 기술입니다.일회성 운동으로는 비용 효율이 낮습니다.

2,100만 행의 테이블에서 1,900만 행을 몇 분 만에 삭제할 수 있었습니다.제 접근 방식은 이렇습니다.

이 테이블에 자동 증분 기본 키가 있는 경우 이 기본 키를 사용할 수 있습니다.

1. readTime < dateadd ( MONTH , 7 , GETDATE )에서 큰 테이블의 프라이머리 키의 최소값을 가져옵니다(readTime 에 인덱스를 추가합니다(아직 존재하지 않는 경우 이 인덱스는 순서 3의 테이블과 함께 삭제됩니다).변수 'min_primary'에 저장합니다.

2. 프라이머리 키가 min_primary인 행을 모두 스테이징 테이블에 삽입합니다(행 수가 크지 않은 경우 메모리테이블).

3. 큰 테이블을 떨어뜨려라.

4. 테이블을 다시 만듭니다.모든 행을 스테이징 테이블에서 메인 테이블로 복사합니다.

5. 스테이징 테이블을 드롭합니다.

구문 단축

select 1
WHILE (@@ROWCOUNT > 0)
BEGIN
  DELETE TOP (10000) LargeTable 
  WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

다음과 같은 while loop을 사용하여 작은 배치를 삭제할 수 있습니다.

DELETE TOP (10000)  LargeTable 
WHERE readTime < dateadd(MONTH,-7,GETDATE())
WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

SQL Server 2016 이상을 사용하고 있으며 테이블에서 삭제하려는 열(예: 타임스탬프 열)을 기준으로 파티션을 만들고 있는 경우 이 새 명령을 사용하여 파티션별 데이터를 삭제할 수 있습니다.

(파티션 ( { | } [ , ...n ] )으로 테이블을 잘라냅니다.

이렇게 하면 선택한 파티션의 데이터만 삭제되며 트랜잭션 로그는 생성되지 않으며 테이블에서 모든 데이터가 삭제되지 않고 일반 잘라내기 작업만큼 빠르게 수행되므로 테이블의 일부에서 데이터를 삭제하는 가장 효율적인 방법이 될 수 있습니다.

단점은 테이블이 파티션으로 설정되어 있지 않은 경우 오래된 방법으로 데이터를 삭제한 후 나중에 파티션을 사용하여 테이블을 다시 만들어야 한다는 것입니다.삽입 절차 자체에 파티션 생성 및 삭제를 추가했습니다.5억 행의 테이블이 있었기 때문에 삭제 시간을 단축할 수 있는 유일한 옵션이었습니다.

상세한 것에 대하여는, 다음의 링크를 참조해 주세요.https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-2017

SQL Server 2016 파티션으로 테이블 잘라내기

아래는 필요한 데이터가 포함된 파티션을 사용하여 테이블을 다시 작성하기 전에 먼저 데이터를 삭제하기 위해 수행한 작업입니다.이 쿼리는 데이터가 삭제될 때까지 지정된 기간 동안 실행됩니다.

:connect <<ServerName>>
use <<DatabaseName>>

SET NOCOUNT ON;
DECLARE @Deleted_Rows INT;
DECLARE @loopnum INT;
DECLARE @msg varchar(100);
DECLARE @FlagDate datetime;
SET @FlagDate =  getdate() - 31;
SET @Deleted_Rows = 1;
SET @loopnum = 1;

/*while (getdate() < convert(datetime,'2018-11-08 14:00:00.000',120))
BEGIN
    RAISERROR( 'WAIT for START' ,0,1) WITH NOWAIT   
    WAITFOR DELAY '00:10:00'
END*/
RAISERROR( 'STARTING PURGE' ,0,1) WITH NOWAIT   

WHILE (1=1)
BEGIN
    WHILE (@Deleted_Rows > 0 AND (datepart(hh, getdate() ) >= 12 AND datepart(hh, getdate() ) <= 20)) -- (getdate() < convert(datetime,'2018-11-08 19:00:00.000',120) )
      BEGIN
       -- Delete some small number of rows at a time
         DELETE TOP (500000)  dbo.<<table_name>>
         WHERE timestamp_column < convert(datetime, @FlagDate,102)
         SET @Deleted_Rows = @@ROWCOUNT;
         WAITFOR DELAY '00:00:01'
         select @msg = 'ROWCOUNT' + convert(varchar,@Deleted_Rows);
         set @loopnum = @loopnum + 1
         if @loopnum > 1000
             begin 
                 begin try
                        DBCC SHRINKFILE (N'<<databasename>>_log' , 0, TRUNCATEONLY)
                        RAISERROR( @msg ,0,1) WITH NOWAIT
                 end try
                 begin catch
                     RAISERROR( 'DBCC SHRINK' ,0,1) WITH NOWAIT  
                 end catch
                 set @loopnum = 1
             end
        END
WAITFOR DELAY '00:10:00'
END 
select getdate()

또 다른 용도:

SET ROWCOUNT 1000 -- Buffer

DECLARE @DATE AS DATETIME = dateadd(MONTH,-7,GETDATE())

DELETE LargeTable  WHERE readTime < @DATE
WHILE @@ROWCOUNT > 0
BEGIN
   DELETE LargeTable  WHERE readTime < @DATE
END
SET ROWCOUNT 0

옵션

트랜잭션 로그를 사용하도록 설정한 경우 트랜잭션 로그를 사용하지 않도록 설정합니다.

ALTER DATABASE dbname SET RECOVERY SIMPLE;

루프가 없는 상태라고 하면GOTOsql server. exa를 사용하여 대량의 레코드를 삭제하기 위한 문.

 IsRepeat:
    DELETE TOP (10000)
    FROM <TableName>
    IF @@ROWCOUNT > 0
         GOTO IsRepeat

이렇게 하면 삭제 크기가 작은 대량의 데이터를 삭제할 수 있습니다.

더 많은 정보가 필요하면 알려주세요.

이 질문은 좀 낡았지만, 도움을 청하다 우연히 발견했어요.행 전체를 삭제하는 가장 빠른 방법은 일부 행을 유지하면서

1. 임시 테이블을 만듭니다(테이블 변수를 사용함).

2. 임시 테이블에 유지할 행을 선택합니다.

3. 대상 테이블 잘라내기

4. 보관된 행을 대상 테이블에 다시 삽입합니다.

   트랜의 개시

항상 먼저 @tmpSaveTable 행을 선택하고 트랜잭션을 롤백하여 테스트합니다.몇 초 만에 1700만 줄을 달았어요

Begin tran
DECLARE @tmpSaveTable   table (
...your columns, types, etc. go here )
INSERT @tmpSaveTable (columns here)
SELECT (appropriate columns from target here)
WHERE (which rows to save)
-- appropriate place to test w/ select from @tmpSaveTable
TRUNCATE SourceTable
INSERT SourceTable (columns) 
SELECT (all values from @tmpSaveTable)
--Rollback Tran testing 
Commit Tran

다수의 레코드가 있는 테이블의 레코드를 삭제하고 일부 레코드를 보관하는 경우 필요한 레코드를 유사한 테이블에 저장한 후 메인테이블을 잘라낸 후 저장된 레코드를 메인테이블로 되돌릴 수 있습니다.

언급URL : https://stackoverflow.com/questions/24213299/how-to-delete-large-data-of-table-in-sql-without-log