70세 코딩

정기적으로 로그 백업을 하고 있다면 로그 파일 크기 내에서 재활용은 하겠지만, 대량의 트랜잭션이 발생하여 로그파일의 크기가 늘어나더라도 로그 백업을 통해 이미 늘어난 파일 크기가 줄어들지는 않습니다. 이로 인해서 불필요하게 디스크 공간을 차지하게 되므로 가끔씩 로그파일 크기를 줄여줄 필요가 있습니다.

현재 로그파일 크기 확인

sp_helpfile

sp_helpfile로 확인해 보면 현재 로그파일 크기가 9,024,448 KB로 나타납니다 (운영 중인 DB라 가렸습니다).

이는 탐색기에서 확인되는 사이즈와 동일합니다.

VLF 구성 확인

DBCC loginfo

DBCC loginfo로 로그파일의 VLF(가상 로그 파일)를 확인해 보면 81개로 구성되어 있음을 알 수 있습니다.

로그파일 크기 줄이기

sp_helpfile 조회 결과의 'name' 컬럼에 해당하는 이름이 로그파일명 입니다.

DBCC SHRINKFILE (로그파일명)

DBCC SHRINKFILE 명령이 정상적으로 수행되면 다음과 같이 결과를 보여 줍니다.

위에서 CurrentSize는 줄어든 후 로그파일의 페이지 수를 나타냅니다. SQL 서버에서 한 페이지는 8KB 이므로 KB로 변경하면 1,025,504 * 8 = 8,204,032 가 됩니다.

sp_helpfile로 다시 확인해보면 다음과 같습니다.

작업 직전에 로그 백업을 받고 진행하지 않아서 많이 줄어들지는 않았지만 그래도 크기가 줄어들었습니다.

DBCC loginfo로 재확인 해보면 VLF 개수가 80개로 줄었습니다.

참고로 쿼리로 로그 테이블의 초기 사이즈를 확인하기 위해서는 다음과 같이 하면 됩니다.

SELECT name, size * 8 AS init_size_KB, physical_name
FROM master.sys.master_files where database_id = DB_ID('데이터베이스명')

DBCC SHRINKFILE를 통해 줄어들 수 있는 최대치는 이 초기 사이즈이기 때문에 이하로는 줄어들지 않습니다.

[참조]

DBCC SHRINKFILE(Transact-SQL)

2013년에 포스팅한 내용이 너무 간단한 것 같아서 좀 더 자세한 내용으로 다시 올립니다.

hackhyun.tistory.com/231

SQL서버에서 기존의 PK로 사용된 컬럼을 다른 컬럼으로 변경하려면 다음과 같이 합니다.

1. 기존 PK 명(인덱스 명) 확인

sp_helpindex 테이블명

• primary key로 지정된 인덱스 명을 확인 합니다.

2. 기존 PK 삭제

ALTER TABLE 테이블명
DROP CONSTRAINT 기존_PK명

• 1에서 확인된 인덱스 명을 이용합니다.

3. 새로운 PK 정의

ALTER TABLE 테이블명
ADD CONSTRAINT 새_PK명 
PRIMARY KEY [NONCLUSTERED](컬럼1, 컬럼2, ...)

• 새 PK에 포함될 컬럼은 NULL이 허용되지 않는 컬럼이어야 합니다.
• 별도로 지정하지 않으면 PK 컬럼이 클러스터 인덱스로 생성됩니다.
• PK가 아닌 다른 컬럼을 클러스터 인덱스로 사용할 계획이라면 NONCLUSTERED 옵션을 지정합니다.

4. 잘 변경되었는지 확인

이따금 SQL서버에서
"메시지 3998, 커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다."
라는 오류가 발생하는 경우가 있습니다.

기술적 내용을 알고나면 지극히 당연한 오류 메시지인데, 그 전에는 도대체 무슨 뜻인지 이해하기가 쉽지 않습니다.

SQL서버에서 명시적으로 트랜잭션을 시작하는 경우, 런타임 중에 오류가 발생하면 해당 트랜잭션에 대하여 직접 완료(커밋 또는 롤백)처리를 해야 하는 모드와 SQL서버가 자동으로 롤백 처리해 주는 모드가 있습니다.

다음 명령을 통해서 모드 변경이 가능 합니다.

SET XACT_ABORT { ON | OFF }

T-SQL 디폴트는 OFF 상태 이며, 이 때는 직접 완료처리를 해야 합니다.

USE AdventureWorks2014
GO

SET XACT_ABORT OFF;
GO

BEGIN TRANSACTION; --- ①

SELECT 10 /0       --- ②
SELECT TOP 1 * FROM Sales.Store    --- ③

SELECT @@TRANCOUNT as trnCount

① 에서 명시적 트랜잭션이 시작 되었습니다.
② 에서 0으로 나누기 오류가 발생 됩니다. 그러나 다음 행의 쿼리가 계속 수행 됩니다.
③ SELECT 문이 정상적으로 수행 됩니다.

결과는 다음과 같습니다.

이 시점에서 @@TRANCOUNT는 보시는 바와 같이 1 입니다.
따라서 개발자가 롤백을 하든지 커밋을 하든지 직접 해야 합니다.
물론 위에서는 UPDATE, DELETE 가 없으므로 커밋을 하든 롤백을 하든 결과가 달라질 것은 없습니다.
XACT_ABORT가 OFF 상태일 때는 런타임 오류가 발생하여도 오류가 발생된 이후의 쿼리가 계속 수행되고
트랜잭션 상태가 유지 됩니다.

이제 XACT_ABORT가 ON인 경우를 보겠습니다.

USE AdventureWorks2014
GO

SET XACT_ABORT ON;   --- ①
GO

BEGIN TRANSACTION;

SELECT 10 /0   --- ②
SELECT TOP 1 * FROM Sales.Store

SELECT @@TRANCOUNT as trnCount

① 에서 XACT_ABORT가 ON으로 변경 되었고, 나머지 쿼리는 전과 동일 합니다.

결과는 다음과 같습니다.

② 에서 0 나누기 오류가 발생하자 그 이후의 쿼리는 수행되지 않고 트랜잭션이 종료되고 자동으로 롤백이 됩니다.
따라서 이때 개발자가 직접 롤백처리 하고자 하면 오류가 발생 합니다.

그럼 "커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다." 오류는 언제 발생하는 걸까요?

다음 쿼리의 실행 결과를 보겠습니다.

USE AdventureWorks2014
GO

SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;
BEGIN TRY   --- ①
    SELECT * FROM person.Person WHERE BusinessEntityID = 1

    INSERT INTO person.personPhone (BusinessEntityID, PhoneNumber, PhoneNumberTypeID)
    VALUES (1, '697-555-0142', 1)    --- ②

    COMMIT
END TRY
BEGIN CATCH  --- ③
    SELECT XACT_STATE() AS XACTSTATE,  ERROR_NUMBER() AS ERR_NUMBER,   --- ④
           ERROR_MESSAGE() AS ERR_MESSAGE 
END CATCH
GO

SELECT @@TRANCOUNT as trnCount

명시적 트랜잭션 실행 후,
① 에서 TRY/CATCH를 처리하고 있습니다.
② 에서 PRIMARY KEY 제약 조건 위반(중복 키 삽입) 오류가 발생 됩니다.
③ TRY 내에서 발생된 오류(예외)로 인해서 CATCH로 제어가 넘어 오게 됩니다.

드디어 "커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다." 오류가 발생 하였습니다. 

XACT_ABORT가 ON 상태에서 명시적으로 트랜잭션이 시작되고, TRY 내에서 예외 발생이 되었으나 CATCH에서 트랜잭션에 대하여 롤백 처리를 하지 않고 일괄처리가 끝나면 위와 같은 오류가 발생하게 됩니다.

이제는 오류 메시지가 무슨 의미인지 이해가 될 것입니다.

참고로 ④에서 XACT_STATE()를 사용하고 있는데 반환값이 -1 이면 활성 사용자 트랜잭션이 있지만 오류가 발생하여 트랜잭션이 커밋할 수 없는 상태라는 의미 입니다.
만약 이 상태에서 CATCH 내에서 ROLLBACK이 아닌 COMMIT을 시도하게 되면 다음과 같은 오류가 발생 합니다.

이 글이 도움이 되셨으면 추천과 댓글을 부탁 드려요!

SSMS 18.3 버전 부터 쿼리를 실행 하고나면 항상 완료시간이 표시 됩니다.

특별히 도움 되지는 않고 귀찮기만 한 경우에는 다음 옵션에서 체크를 해제 하면 됩니다.

도구 > 옵션 > 쿼리 실행 > SQL Server > 고급