[MSSQL] MSDB 파일 사이즈 증가로 인한 백업 히스토리 기록 삭제방법(복원,백업 로딩속도 개선)

MSDB는 SQL Server 에이전트가 사용하는 데이터베이스로 예약된 작업등의 내용이 들어있습니다.

이번 시간에는 MSDB의 사이즈 증가로 인한 백업 관련 로그 삭제 방법과 DATA 및 LOG 축소 방법에 대해서 알아보겠습니다.

 

데이터베이스 백업 및 복원을 하기 위해서  백업 디바이스를 추가하는 화면 로딩을 진행할 경우 속도가 비정상적으로 엄청 오래 걸리는 경우가 있습니다.

로딩 속도가 느린 이유는 바로 이 로그가 많이 쌓여서 읽어오는데 시간이 필요하기 때문인데요

이 로그는 MSDB에 쌓이며 해당 히스토리 로그를 삭제하면 디비를 복원하는 화면의 활성화 되는 속도도 개선됩니다.

 

 

 

 

 

---

초기 데이터 확인

1. msdb 사이즈 확인

USE msdb ; 
GO 
sp_helpdb msdb

 

2. msdb 백업 히스토리 테이블 조회

USE msdb ; 
GO 

SELECT

    OBJECT_SCHEMA_NAME(a2.object_id) AS SchemaName,

    a2.name AS TableName,

    a1.rows as [RowCount],

    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,

    CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,

    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,

    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB

FROM

    (SELECT

        ps.object_id,

        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],

        SUM (ps.reserved_page_count) AS reserved,

        SUM (CASE

                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

            END

            ) AS data,

        SUM (ps.used_page_count) AS used

    FROM sys.dm_db_partition_stats ps

    GROUP BY ps.object_id) AS a1

LEFT OUTER JOIN

    (SELECT

        it.parent_id,

        SUM(ps.reserved_page_count) AS reserved,

        SUM(ps.used_page_count) AS used

     FROM sys.dm_db_partition_stats ps

     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

     WHERE it.internal_type IN (202,204)

     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

WHERE a2.type <> N'S' and a2.type <> N'IT'

and   a2.name  like '%back%' --조건추가1

or   a2.name='sysjobhistory' --조건추가2

ORDER BY ReservedSize_MB DESC

 

 

 

 

 

 

백업을 진행하여 데이터 증가

백업을 진행하여 msdb의 데이터를 증가시킵니다.

백업 명령어 후 msdb 데이터 증가가 없다면 여러 번 실행시켜 데이터를 증가시켜 주세요

 

BACKUP DATABASE [AdventureWorks2016] TO  DISK = N'D:\백업\AdventureWorks2016.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2016-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

BACKUP DATABASE [msdb] TO  DISK = N'D:\백업\msdb.bak' WITH NOFORMAT, NOINIT,  NAME = N'msdb-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

1. msdb 사이즈 확인

MSDB 의 DATA, LOG가 증가한 것을 확인

 

2. msdb 백업 히스토리 테이블 조회

백업 진행 후 RowCount 및 DataSize가 증가 한 것을 확인

 

msdb 백업 히스토리 삭제

백업을 진행하여 증가한 msdb의 백업 히스토리 삭제를 진행합니다.

msdb 용량이 큰 경우 삭제를 과거에서부터 일주일 or 한달씩 해서 쪼개서 삭제권장하며 운영시간대를 피해서 작업 권장합니다.

아래의 쿼리를 실행하여 2023년 01월 01일 이전의 백업 히스토리를 모두 삭제합니다.

USE msdb;
GO

EXEC sp_delete_backuphistory @oldest_date = '01/01/2023';  -- (월/일/년) 해당날짜 미만 히스토리 삭제

 

 

백업 히스토리 삭제 후 데이터 변화 확인

백업 히스토리 삭제 후 RowCount 및 DataSize_MB가 감소한 것을 볼 수 있습니다.

하지만 아직 MSDB의 사이즈는 그대로 입니다. 축소 작업을 진행해야 사이즈가 줄어듭니다.

 

MSDB의 data, log 파일 축소

DBCC SHRINKFILE 명령어를 사용하여 데이터 파일과 로그 파일 축소를 진행합니다.

USE [msdb]

DBCC SHRINKFILE (N'MSDBData' ,0) --MDF 파일 축소
GO

DBCC SHRINKFILE (N'MSDBLog' , 0) --LDF  파일 축소
GO

 

이후 용량 변화를 확인하면 아래와 같이 용량이 줄어든 것을 볼 수 있습니다.

DBCC SHRINKFILE 명령어 옵션의 자세한 설명은 아래의 링크를 확인하시기 바랍니다.

DBCC SHRINKFILE(Transact-SQL) - SQL Server

 

MSDB 로그삭제 GUI 설정방법(SQL Server 에이전트)

위의 방법은 SQL GUI 방법으로 설정이 가능합니다.

[SQL Server 에이전트]우 클릭 속성 - > [기록]에 들어갑니다.

 

에이전트 기록 제거를 체크하고 "다음보다 오래된 항목"의 일자를 선택하면 기록 삭제가 가능합니다.

또한 일자가 아니라 작업 기록 로그 크기 및 행수를 제한하여 로그를 순환 시킬 수도 있습니다.

이상으로 MSSQL의 MSDB 용량 증가의 원인과 해결방법에 대해서 알아보았습니다.

 

[쿼리 스크립트 참조]

msdb 백업히스토리 삭제(sp_delete_backuphistory).sql

0.00MB