시스템 관리_36 SQL Server 트랜잭션 로그 풀 LOG_BACKUP 원인 분석 및 긴급 축소 해결 가이드

# 1. 애플리케이션 또는 SQL Server 에러 로그에서 Error 9002 확인
$ sudo tail -n 30 /var/opt/mssql/log/errorlog | grep -E "9002|transaction log.*full"
# [출력 예시]
# Error: 9002, Severity: 17, State: 2.
# The transaction log for database 'SampleDB' is full due to 'LOG_BACKUP'.

# 2. OS 레벨에서 트랜잭션 로그 파일(.ldf)이 위치한 마운트 포인트 여유 공간 확인
$ df -h /var/opt/mssql/data
# Filesystem      Size  Used Avail Use% Mounted on
# /dev/sdb1       200G  198G  500M  100% /var/opt/mssql/data
# -> 로그 파일 자동 증가(Auto-growth)를 위한 디스크 여유 공간이 완전히 고갈되었는지 확인

# 3. 데이터베이스별 복구 모델(Recovery Model) 및 로그 재사용 대기 원인 확인
$ sqlcmd -S localhost -U sa -P 'MyStrongPassword123!' -Q "
SELECT 
    name, 
    recovery_model_desc, 
    log_reuse_wait, 
    log_reuse_wait_desc 
FROM sys.databases 
WHERE name = 'SampleDB';
"
# [출력 예시]
# name       recovery_model_desc log_reuse_wait log_reuse_wait_desc
# ---------- ------------------- -------------- -------------------
# SampleDB   FULL                             2 LOG_BACKUP
# -> FULL 복구 모델 상태에서 로그 백업이 수행되지 않아 로그 재사용이 영구 정체 중임을 확인

# 4. 전체 데이터베이스의 트랜잭션 로그 공간 점유율(%) 및 크기 확인
$ sqlcmd -S localhost -U sa -P 'MyStrongPassword123!' -Q "
DBCC SQLPERF(LOGSPACE);
"
# [출력 예시]
# Database Name  Log Size (MB)  Log Space Used (%)  Status
# -------------  -------------  ------------------  ------
# master         3.99           35.21               0
# SampleDB       154200.50      99.85               0
# -> SampleDB의 트랜잭션 로그가 154GB에 도달하고 99.85%를 소모한 상태 확인

# 5. 대상 데이터베이스의 실제 로그 파일 논리명 확인 (DBCC SHRINKFILE에 필요)
$ sqlcmd -S localhost -U sa -P 'MyStrongPassword123!' -Q "
SELECT file_id, name, physical_name, size * 8 / 1024 AS size_mb 
FROM sys.master_files 
WHERE database_id = DB_ID('SampleDB') AND type = 1;
"

#!/bin/bash
# check_mssql_log_full.sh - Diagnose SQL Server Transaction Log usage and LOG_BACKUP state

SQL_HOST=${1:-"localhost"}
SQL_USER=${2:-"sa"}
SQL_PASS=${3:-"MyStrongPassword123!"}
TARGET_DB=${4:-""}

echo "=========================================="
echo " [SQL Server 트랜잭션 로그 고갈 상태 진단]"
echo " 대상 호스트: ${SQL_HOST}"
echo "=========================================="

if ! command -v sqlcmd &>/dev/null; then
    echo "  [오류] sqlcmd 명령어를 찾을 수 없습니다. mssql-tools 설치 여부를 확인하세요."
    exit 1
fi

# 1. sys.databases의 로그 재사용 대기 사유 점검
echo -e "\n[1] 데이터베이스별 복구 모델 및 로그 재사용 대기 원인:"
QUERY_DB_STATUS="
SET NOCOUNT ON;
SELECT 
    name, 
    recovery_model_desc, 
    log_reuse_wait_desc 
FROM sys.databases 
WHERE name NOT IN ('master', 'tempdb', 'model')
"
[ -n "$TARGET_DB" ] && QUERY_DB_STATUS="${QUERY_DB_STATUS} AND name = '${TARGET_DB}'"

DB_LIST=$(sqlcmd -S "$SQL_HOST" -U "$SQL_USER" -P "$SQL_PASS" -W -Q "$QUERY_DB_STATUS" 2>/dev/null)
echo "$DB_LIST" | sed 's/^/  /'

# 2. LOGSPACE 통계 확인
echo -e "\n[2] DBCC SQLPERF(LOGSPACE) 로그 사용률 통계:"
LOG_STATS=$(sqlcmd -S "$SQL_HOST" -U "$SQL_USER" -P "$SQL_PASS" -W -Q "
SET NOCOUNT ON;
CREATE TABLE #LogStats (
    DatabaseName sysname,
    LogSizeMB float,
    LogSpaceUsedPercent float,
    Status int
);
INSERT INTO #LogStats EXEC('DBCC SQLPERF(LOGSPACE)');
SELECT DatabaseName, LogSizeMB, LogSpaceUsedPercent FROM #LogStats WHERE LogSpaceUsedPercent > 80.0;
DROP TABLE #LogStats;
" 2>/dev/null)

if echo "$LOG_STATS" | grep -q "[0-9]"; then
    echo "  [경고] 로그 점유율 80% 초과 데이터베이스 감지:"
    echo "$LOG_STATS" | sed 's/^/  /'
else
    echo "  [정상] 80%를 초과하여 임계치에 도달한 데이터베이스가 없습니다."
fi

# 3. LOG_BACKUP 대기 상태에 대한 긴급 조치 가이드 생성
echo -e "\n[3] LOG_BACKUP 병목 감지 시 권장 조치 가이드:"
BLOCKED_DBS=$(sqlcmd -S "$SQL_HOST" -U "$SQL_USER" -P "$SQL_PASS" -h -1 -W -Q "
SET NOCOUNT ON;
SELECT name FROM sys.databases WHERE log_reuse_wait_desc = 'LOG_BACKUP';
" 2>/dev/null | tr -d '[:space:]')

if [ -n "$BLOCKED_DBS" ]; then
    echo "  [!] 다음 데이터베이스들이 LOG_BACKUP으로 인해 로그가 비워지지 못하고 있습니다:"
    for DB in $BLOCKED_DBS; do
        LOG_LOGICAL_NAME=$(sqlcmd -S "$SQL_HOST" -U "$SQL_USER" -P "$SQL_PASS" -h -1 -W -Q "
        SET NOCOUNT ON;
        SELECT name FROM sys.master_files WHERE database_id = DB_ID('$DB') AND type = 1;
        " 2>/dev/null | tr -d '[:space:]')
        
        echo "    - DB명: ${DB} (로그 논리 파일명: ${LOG_LOGICAL_NAME})"
        echo "      [긴급 조치 쿼리 예시]:"
        echo "      1) 트랜잭션 로그 백업 (권장):"
        echo "         BACKUP LOG [${DB}] TO DISK = '/var/opt/mssql/data/${DB}_LogBackup.trn' WITH COMPRESSION;"
        echo "      2) 로그 파일 물리적 축소:"
        echo "         DBCC SHRINKFILE (N'${LOG_LOGICAL_NAME}', 1024);"
    done
else
    echo "  [정상] 현재 LOG_BACKUP으로 정체된 데이터베이스가 없습니다."
fi

echo -e "\n=========================================="
echo " [진단 완료]"
echo "=========================================="