[MSSQL] 데이터 파일 사용량이 풀일 때 해결방법

데이터 파일 사용량이 가득찰 때 발생하는 이슈

---

데이터파일의 사용량이 가득차게 되면, SQL Server 는 AutoGrow 옵션에 설정된 크기만큼 데이터파일 크기를 증가시킵니다.

이 때 자동증가가 완료될 때까지 다른 세션의 트랜잭션은 대기하게 됩니다.

디스크의 성능이 좋지 않아서 데이터파일의 크기를 증가시키는데 오랜 시간이 걸리게 되면,

파일 증가가 시간 내에 완료되지 못하고 파일 증가도 롤백되고, 에러로그에 아래와 같이 기록됩니다.

 - SQL Server has encountered x occurrences of I/O requests taking longer than 15 seconds to complete on file

 - Autogrow of file 'FILE_NAME' in database 'DATABASE' was cancelled by user or timed out after x milliseconds.

오류 로그를 해석하면 I/O 에 대한 병목 현상이 나타난 것을 확인할 수 있습니다.

 - 766개의 I/O 요청 파일에 반영되는데 15 초 이상이 소요되었습니다.

 - 데이터베이스 파일의 자동증가가 타임아웃에 의해 취소되었습니다.

해결방법

---

기존 데이터파일이 AutoGrow 에 의해 확장되지 않도록 신규 데이터파일을 추가합니다.

( 데이터파일이 한 개 밖에 없는 경우 적용 )

월별 데이터파일 크기 증가량 확인

아래 쿼리를 통해 월별로 데이터베이스의 크기를 출력한 후 증가량을 확인합니다.

결과를 확인해 본 결과 월 평균 600MB 씩 증가하고 있는 것을 확인할 수 있습니다.

SELECT
  database_name
  DATEPART(year, backup_start_date) AS "Year",
  DATEPART(month, backup_start_date) AS "Month",
  AVG(backup_size/1024/1024) AS "Backup Size MB"
FROM msdb.dbo.backupset
WHERE database_name = 'AdventureWorks'
  AND type = 'D'
GROUP BY database_name, DATEPART(yyyy, backup_start_date), DATEPART(mm, backup_start_date)
ORDER BY DATEPART(year, backup_start_date), DATEPART(month, backup_start_date); 

데이터파일의 여유공간 확인

현재 데이터파일의 여유공간을 확인합니다.

select
 f.type_desc     as Type
,f.name          as FileName
,fg.name         as FileGroup
,f.physical_name as Path
,f.size / 128.0  as CurrentSizeMB
,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 128.0 as FreeSpaceMb
from sys.database_files f with (nolock)
  left outer join sys.filegroups fg with (nolock)
  on f.data_space_id = fg.data_space_id

서비스 기간을 산정하여 데이터파일 크기 설정 및 추가

ALTER DATABASE AdventureWorks ADD DATAFILE ( NAME = 'AdventureWorks_DATA1_02'
                                              , FILENAME = 'F:\Data\AdventureWorks_Data1_02.ndf'
                                              , SIZE = 30GB
                                              , FILEGROWTH = 10MB ) TO FILEGROUP AdventureWorks_DATA1

* 오류내용과 관련된 자세한 내용은 아래 URL 을 참고 부탁드립니다.

https://blog.sqlauthority.com/2015/09/14/sql-server-error-autogrow-of-file-mydb_log-in-database-mydb-was-cancelled-by-user-or-timed-out-after-30121-milliseconds/