[MSSQL] 데이터베이스 offline 처리 후 drop하면 mdf, ldf 파일은 지워질까 안지워질까?

신규 데이터베이스로 마이그레이션 이후 기존 데이터베이스 미사용처리를 위해 offline처리를 진행했다.

이후 정상화를 확인하고 drop 하면 mdf, ldf 파일은 삭제되지않고 남아있는 것을 확인했다.

 

사실 처음알았다. drop하면 무조건 같이 지워지는 줄 알았는데 🙄 

 

현상 재현부터 해보자

-- 그냥 기본 형식으로 만든다
create database test2

-- 오프라인 처리를 하자
alter database set offline

-- db정보를 확인해보자
exec sp_helpdb test2

-- 그리고 drop한다
drop database test2

오프라인 처리가 잘 되었고 sp_helpdb를 하면 데이터가 안나온다(메타데이터에서 제거를 하는 것 같다는 의심이 든다)

 

offline 처리 이후

sp_helpdb

drop 후 mdf, ldf파일은 지워지지 않음

 

 

다시 mdf, ldf 제거하고 처음부터 생성한 뒤에 

sp_helpdb는 왜 안나오는지 sp_helpdb sp 내부를 살펴보자

-- sp내용 확인
exec sp_helptext sp_helpdb

 

 

어느 부분에서 나오지 않는지 살펴보기위해 인풋 파라미터인 @dbname에 declare로 값을 대입하여 살펴보자.

 

원본

수정본

select * from #spdbdesc

 

이부분에서는 잘 나온다

 

다음블럭을 실행해보자

 

결과값

 

왜그런가 하니 이 부분에서 걸려서 #spdbdesc 테이블에서 값이 제거됨을 알 수 있다.

 

일단 이건 정확한 정보는 아닌 것 같아서 다른 sys테이블을 조회해봤다. 잘 나온다.

 

파일 정보에 대한 메타데이터에 대한 값을 제거하지는 않는 것 같고 뭔가 상태값만 수정해서 맵핑을 끊어내는? 것 같다 online처리시에 되돌려야하니까

 

관련해서 챗 지피티한테 물어봤더니 다음과 같이 설명해주었다. 정보가 달라지는 것은 제거하고 반복적으로 같은 값만 반환하는 것만 추렸다.

SQL Server에서 데이터베이스를 오프라인 상태로 설정한 후 DROP DATABASE 명령어를 실행할 때, 
데이터베이스 파일(.mdf, .ldf)이 삭제되지 않는 이유

1. 데이터베이스 오프라인 상태:
데이터베이스가 오프라인 상태일 때, SQL Server는 데이터베이스 파일에 접근하지 않습니다. 
따라서 데이터베이스가 오프라인인 경우 DROP DATABASE 명령어를 실행하면 SQL Server는 데이터베이스의 
실제 파일 시스템에서 파일을 제거하지 않고 데이터베이스의 상태를 'OFFLINE'으로 업데이트하여 
데이터베이스의 접근을 제한합니다.

2. 파일 시스템 관리:
SQL Server는 데이터베이스 파일을 직접 삭제하는 대신, 
데이터베이스가 오프라인으로 설정된 상태에서 데이터베이스를 삭제할 경우 
파일 시스템에서 해당 파일들을 수동으로 삭제하도록 설계되었습니다. 
이는 데이터베이스 파일 삭제 시 실수로 중요한 데이터를 잃지 않도록 하기 위한 안전 장치입니다.

3.보안 및 권한 문제:
SQL Server 인스턴스가 파일 시스템에서 파일을 삭제하는 것은 보안상 위험할 수 있습니다. 
데이터베이스 파일 삭제 권한은 운영체제 수준에서 다루어지며,
SQL Server는 이러한 작업을 피하고 관리자에게 파일 삭제를 맡깁니다.

drop시에 데이터베이스는 SQL Server의 시스템 카탈로그에서 제거되지만, 
실제 데이터 파일(.mdf, .ldf)은 파일 시스템에 남아 있습니다.

오프라인 상태에서는 데이터베이스 파일이 SQL Server에 의해 잠겨 있지 않으며, 
파일 시스템에 그대로 남아 있게 됩니다. 
이는 데이터베이스 파일을 수동으로 복구하거나 다른 방식으로 사용할 필요가 있는 경우를 대비한 것입니다. 
또한, 오프라인 상태에서는 SQL Server가 해당 파일에 대해 충분한 제어를 가지고 있지 않을 수 있으므로, 
파일 시스템에서 파일을 자동으로 삭제하지 않습니다.

 

 

 MSDN에서도 drop database시 오프라인 상태인 경우 디스크 파일은 삭제되지 않는다고 기재되어있다.

시퀄에서 의도된 바로써, 오프라인 데이터베이스의 파일에 액세스하지 않는 것을 의미한다. 

 

SQL server에서 이렇게 제작한지에 대해 100% 의도를 알 수 없지만 오프라인 상태로 돌리는 경우 해당 데이터베이스 메타정보에 접근할 수 없어 정보를 알 수 없기 때문에 지울 수 없는 것으로 보인다.

온라인 데이터베이스에 대한 정보만 관리하고 오프라인 데이터베이스는 더이상 신경쓰지 않겠다는 뜻...?

 

추가로 공부하다가 확인한 점 

- 데이터베이스가 오프라인으로 전환되면, SQL Server는 해당 데이터베이스의 데이터 페이지, 인덱스 페이지, 실행 계획 등과 같은 메모리 캐시를 비움(버퍼 캐시, 플랜 캐시, 테이블 메타데이터 캐시 등 포함)

- 다시 온라인 상태로 전환하면 SQL Server는 데이터베이스의 메타데이터를 다시 로드하고, 필요한 데이터 페이지를 메모리에 다시 로드함

 

캐시에서 내려서 다시 로드할 때까지는 확인이 불가한가보당 

 

오프라인 데이터베이스에 대한 drop을 할 때에는 물리적 파일이 지워지지 않으니 꼭 mdf, ldf가 존재하는 경로로 가서 파일을 지워줘야 디스크 공간이 확보될 수 있음을 유의하자.

---

 

참고 url : https://www.brentozar.com/archive/2015/02/dropping-offline-database-sql-server/

Dropping an Offline Database in SQL Server - Brent Ozar Unlimited®

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms178613(v=sql.100)?redirectedfrom=MSDN

DROP DATABASE (Transact-SQL)

https://bobpusateri.com/archive/2018/10/dropping-offline-databases-be-careful/