[SQL SERVER] DROP 과 TRUNCATE 는 정말 로그가 안 남을까

DROP과 TRUNCATE는 실제 명령이 로그를 생성하지 않는다고 오해한다

하지만 일부 LOG가 생성이되고 (메타 데이터와 페이지, 익스텐트와의 연결을 끊는 상황)

백그라운드 프로세스의 지연된 삭제에 의해 트랜잭션 로그에는 익스텐트 삭제가 기록으로 남는다

일반적으로 테이블의 0.3~0.4%의 크기가 로그 파일에 기록이되고 기록된 로그는 AG, 미러링, 로그 복제의 경우 전달이 된다.

다만 백그라운드 프로세스에서 해당 삭제 처리는 하나의 트랜잭션이 아닌 여러개의 트랜잭션으로 나누어져서 처리가되어 큰 이슈는 없다고 한다....

 

 

TEST

DROP TABLE ~~~

 

SELECT
[Current LSN],
[Operation],
[Context],
[Log Record Length],
[Description]
FROM fn_dblog (null, null);

 

--메타 데이터 할당 제거

 

--잠시 후 실제 익스텐트 할당 삭제

 

MDF 크기 7748.000000 ->  2801.375000

MDF 크기 5000MB 감소 

LDF  크기 42.74 -> 60.96

LDF 18MB 증가 -> 변경된 MDF의 약 0.35%

 

 

참고

데이터를 delete하면 LOP_DELETE_ROWS로 남는다

 

약 20MB 테이블 DELETE

LDF 173MB -> 412MB

 

DROP

LDF 34 -> 34.6

 

결론 -> drop 과 truncate 도 로그를 쓰는 작업이며 delete에 비하여 가벼운 작업으로 생각해야한다.

 

 

 

※

MySQL의 경우 Truncate는 DROP AND CREATE TABLE 로 작동하여 롤백이 불가능하다.

 

---

 

아래는 참고한 내용들

 

이는 모두 하나의 트랜잭션으로 생성되지 않으며(지연 삭제의 전체 요점은 많은 작은 트랜잭션에서 백그라운드에서 작업을 수행하는 것입니다) 즉시 생성되지 않습니다(지연 삭제 백그라운드 작업이 단일 스레드이기 때문에). 데이터베이스에서 진행되는 다른 모든 작업과 혼합되어 다른 트랜잭션과 마찬가지로 동기 AG 복제본으로 전송됩니다. 따라서 추가 로그가 많이 생성되고 있지만 큰 문제가 발생할 것으로 예상하지는 않습니다.

 

 

 

TRUNCATE TABLE 및 DROP TABLE은 모두 메타데이터 전용 작업으로. 특정 개체에 할당되는 범위를 포함하여 시스템 카탈로그를 변경합니다. 이러한 모든 변경 사항이 기록된다. 그러나 테이블의 크기에 관계없이 비용은 매우 적습니다.

각 행에 대한 로그 레코드를 생성하는 유일한 방법은 DELETE입니다.

할당을 해제하기 위해 익스텐트를 읽을 필요는 없습니다. 

어떤 범위가 어떤 객체에 속하는지에 대한 목록이 있으며 해당 목록만 업데이트하면 됩니다. 

... 즉, 데이터 자체는 "제거"되지 않습니다. 이는 여전히 페이지에 있습니다(DBCC PAGE에서 읽을 수 있음). 하지만 할당이 해제되어 재사용이 가능합니다. 

 

대한 공간 회수가 연기된다는 점을 추가할 수 있습니다. 삭제/잘림이 메타데이터 변경 사항을 동기식으로 커밋한 후에는 할당 취소로 표시된 익스텐트가 일괄적으로 비동기 백그라운드 스레드에 의해 회수되므로 삭제/자르기 작업이 기다릴 필요가 없습니다.

 

 

 

참조

 

https://www.sqlskills.com/blogs/paul/the-curious-case-of-log-generated-during-a-drop-table/

https://sqlperformance.com/2013/05/sql-performance/drop-truncate-log-myth