لاگ ترنزکشن پر شد: علت واقعی را پیدا کنید، نه اینکه کوچکش کنید

خطای The transaction log for database 'X' is full یکی از آن خطاهایی است که همیشه در بدترین لحظه می‌آید و همیشه با همان راه‌حل غلط جواب می‌گیرد: کسی لاگ را کوچک می‌کند، سیستم برمی‌گردد، و دو هفته بعد دوباره همان اتفاق می‌افتد.

کوچک کردن، علامت را پاک می‌کند. این مقاله دربارهٔ پیدا کردن علت است، که همیشه در یک ستون نوشته شده.

ستونی که جواب را دارد

SELECT name,
       recovery_model_desc,
       log_reuse_wait_desc
FROM   sys.databases
WHERE  log_reuse_wait_desc <> N'NOTHING';

SQL Server فضای لاگ را وقتی آزاد می‌کند که دیگر به آن بخش نیازی نداشته باشد. اگر آزاد نمی‌کند، دلیلش را دقیقاً در log_reuse_wait_desc می‌نویسد. این ستون، کل تشخیص است.

LOG_BACKUP

شایع‌ترین، به فاصلهٔ زیاد. دیتابیس در مدل FULL است و بکاپ لاگ گرفته نمی‌شود. SQL Server هر تغییر را نگه می‌دارد تا کسی بیاید و بکاپ بگیرد؛ کسی نمی‌آید؛ لاگ تا پر شدن دیسک رشد می‌کند.

دو راه دارید و باید آگاهانه یکی را انتخاب کنید: یا بکاپ لاگ منظم بگیرید (و بازیابی نقطه‌ای داشته باشید)، یا مدل را به SIMPLE ببرید (و بپذیرید که فقط تا آخرین فول برمی‌گردید). حالت سومی که خیلی‌ها در آن گیرند — مدل FULL بی بکاپ لاگ — بدترین هر دو دنیاست: هزینه‌اش را می‌دهید و مزیتش را ندارید.

ACTIVE_TRANSACTION

تراکنشی باز مانده. تا بسته نشود، لاگ از آن نقطه به بعد آزاد نمی‌شود — حتی اگر بکاپ لاگ بگیرید.

SELECT  s.session_id, s.login_name, s.host_name, s.program_name,
        t.database_transaction_begin_time,
        DATEDIFF(MINUTE, t.database_transaction_begin_time, SYSDATETIME()) AS open_minutes,
        t.database_transaction_log_bytes_used / 1024 / 1024 AS log_mb
FROM    sys.dm_tran_database_transactions t
JOIN    sys.dm_tran_session_transactions st ON st.transaction_id = t.transaction_id
JOIN    sys.dm_exec_sessions s ON s.session_id = st.session_id
ORDER   BY t.database_transaction_begin_time;

اگر تراکنشی ساعت‌هاست باز است و نشستش sleeping است، مشکل در اپلیکیشن است: کدی تراکنش را باز کرده و نه commit کرده و نه rollback. کشتن نشست، لاگ را آزاد می‌کند ولی rollback هم می‌تواند طولانی باشد — و باگ اصلی سر جایش می‌ماند.

REPLICATION یا AVAILABILITY_REPLICA

لاگ منتظر است تا تغییرات به مقصد همانندسازی برسند. یعنی مقصد عقب مانده یا قطع است. تا آن مشکل حل نشود، هیچ بکاپ لاگی فضا آزاد نمی‌کند.

CHECKPOINT

معمولاً گذراست و خودش رفع می‌شود؛ در مدل SIMPLE یعنی حجم عملیات از سرعت checkpoint جلو زده است.

حالا که پر شده، چه کنیم؟

به ترتیب، و بی عجله:

1. ستون بالا را بخوانید. بی این، هر کاری حدس است.
2. اگر LOG_BACKUP است، همین حالا یک بکاپ لاگ بگیرید. معمولاً فضا بلافاصله آزاد می‌شود.

   BACKUP LOG [Sales] TO DISK = N'E:\backup\Sales_emergency.trn' WITH COMPRESSION;

3. اگر دیسک کاملاً پر است و جا برای نوشتن بکاپ ندارید، فضای موقت روی درایو دیگری پیدا کنید و بکاپ را آنجا بنویسید.
4. اگر تراکنش باز است، صاحبش را پیدا و با تیم برنامه‌نویسی حلش کنید.
5. بعد از رفع علت، اگر فایل واقعاً بی‌دلیل بزرگ شده، یک بار کوچکش کنید — یک بار، نه به‌صورت جاب.

و کاری که در اضطرار وسوسه‌انگیز است ولی نباید بکنید: NO_LOG یا TRUNCATE_ONLY. این‌ها در نسخه‌های جدید حذف شده‌اند و دلیلش این بود که زنجیرهٔ لاگ را می‌شکستند — یعنی از آن لحظه تا فول بعدی، بازیابی نقطه‌ای ندارید و کسی هم به شما نمی‌گوید. همین‌طور حذف فایل لاگ و ساختن دوباره‌اش: دیتابیس ممکن است بالا بیاید، ولی در وضعیتی که به آن اعتماد نمی‌شود کرد.

چرا اندازهٔ لاگ به‌تنهایی معیار خوبی نیست

این درسی است که خودم با ۱۵۰ پیامک در ۷۲ ساعت یاد گرفتم. اولین نسخهٔ قاعدهٔ هشدار را روی درصد پر بودن لاگ گذاشته بودم. نتیجه این شد که tempdb — که لاگش طبیعتاً مثل ارّه بالا و پایین می‌رود و بعد از هر checkpoint آزاد می‌شود — شب و روز هشدار می‌داد. هیچ‌کدام واقعی نبودند.

معیار درست ترکیبی است: درصد پر بودن به‌علاوهٔ اینکه چیزی جلوی بازیافت را گرفته باشد. لاگی که ۹۰٪ پر است ولی NOTHING دارد، سالم است. لاگی که ۶۰٪ پر است ولی روی LOG_BACKUP ایستاده، در مسیر خرابی است.

پیشگیری

• برای هر دیتابیس FULL، بکاپ لاگ با فاصلهٔ متناسب RPO.
• فایل لاگ را از ابتدا به اندازهٔ معقول بسازید و رشدش را مقدار ثابت بگذارید، نه درصد.
• تعداد VLFها را کنترل کنید: هزاران VLF کوچک (حاصل رشدهای مکرر ریز) بالا آمدن دیتابیس و بازیابی را کند می‌کند.
• log_reuse_wait_desc را پایش کنید، نه فقط درصد را — و tempdb را از این قاعده مستثنا کنید.

بند آخر دقیقاً همان چیزی است که در DBMug اصلاح شد: قاعدهٔ لاگ فقط وقتی هشدار می‌دهد که علت بازیافت‌نشدن وجود داشته باشد، و tempdb از آن بیرون است. یک تغییر کوچک که تفاوت بین «پایش داریم» و «پیامک‌ها را خاموش کردیم» را می‌سازد.