لاگ ترنزکشن SQL Server پر شد؛ علت و راه‌حل

ساعت دو بامداد، برنامه ناگهان هیچ رکوردی ثبت نمی‌کند و در لاگ خطای SQL Server این پیام دیده می‌شود: «The transaction log for database is full due to LOG_BACKUP» یا همان خطای 9002. دیتابیس هنوز بالاست و خواندن کار می‌کند، ولی هر INSERT و UPDATE شکست می‌خورد. اولین واکنش خیلی‌ها جست‌وجوی «shrink log» و اجرای اولین اسکریپتی است که پیدا می‌کنند.

این نوشته دربارهٔ کاری است که بهتر است به‌جای آن انجام دهید: فهمیدن اینکه لاگ چرا جا ندارد، رفع همان علت، و تنظیم دیتابیس طوری که دفعهٔ بعد پیش از پر شدن خبردار شوید.

لاگ ترنزکشن چطور کار می‌کند

فایل لاگ (.ldf) دنباله‌ای از رکوردهای تغییر است که SQL Server پیش از نوشتن داده در فایل اصلی، آنها را ثبت می‌کند. این فایل از داخل به تکه‌هایی به نام Virtual Log File یا VLF تقسیم شده است. لاگ به‌صورت حلقوی استفاده می‌شود: وقتی همهٔ رکوردهای یک VLF دیگر لازم نباشند، آن VLF «truncate» می‌شود، یعنی دوباره قابل استفاده می‌شود. truncate حجم فایل را کم نمی‌کند؛ فقط جا را برای نوشتن بعدی آزاد می‌کند.

اگر چیزی جلوی truncate را بگیرد، SQL Server ناچار است فایل را بزرگ کند. وقتی رشد به سقف MAXSIZE برسد یا دیسک پر شود، خطای 9002 می‌آید. پس سؤال اصلی همیشه یکی است: چه چیزی جلوی استفادهٔ دوباره از لاگ را گرفته است؟

قدم اول: log_reuse_wait_desc

SQL Server خودش علت را می‌گوید. این ستون در sys.databases نشان می‌دهد لاگ هر دیتابیس منتظر چیست:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
ORDER BY name;

-- Size and usage of the log for the current database
SELECT total_log_size_in_bytes / 1048576.0 AS log_size_mb,
       used_log_space_in_bytes / 1048576.0 AS used_mb,
       used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

مقدارهای رایج و معنایشان:

مقدار	معنا	کار اول
NOTHING	مانعی نیست	احتمالاً لاگ فقط برای حجم کار فعلی کوچک است
LOG_BACKUP	مدل FULL است و بکاپ لاگ گرفته نشده	بکاپ لاگ بگیرید و جابش را بررسی کنید
ACTIVE_TRANSACTION	یک ترنزکشن باز، شروع لاگ را نگه داشته	ترنزکشن طولانی را پیدا کنید
REPLICATION	Log Reader هنوز رکوردها را نخوانده	وضعیت Log Reader Agent
AVAILABILITY_REPLICA	یک ثانویهٔ AG هنوز لاگ را دریافت یا اعمال نکرده	وضعیت همگام‌سازی AG
CHECKPOINT	هنوز checkpoint انجام نشده	معمولاً گذراست
ACTIVE_BACKUP_OR_RESTORE	بکاپ یا ریستور در حال اجرا	صبر یا بررسی بکاپ گیرکرده

علت شمارهٔ یک: بکاپ لاگ عقب افتاده

در اغلب مواردی که دیده‌ایم، مقدار LOG_BACKUP است. دیتابیس روی مدل FULL ساخته شده (چون دیتابیس model پیش‌فرضش FULL است)، ولی کسی برایش بکاپ لاگ تعریف نکرده؛ یا جاب بکاپ لاگ وجود دارد ولی چند روز است شکست می‌خورد چون مقصد بکاپ پر شده یا مسیر شبکه‌ای در دسترس نیست. راه‌حل فوری گرفتن بکاپ لاگ است:

BACKUP LOG Sales
  TO DISK = N'E:BackupSales_LOG_emergency.trn'
  WITH COMPRESSION, CHECKSUM;

-- When did the last log backup finish?
SELECT TOP (5) backup_finish_date, backup_size / 1048576.0 AS size_mb
FROM msdb.dbo.backupset
WHERE database_name = N'Sales' AND type = 'L'
ORDER BY backup_finish_date DESC;

اگر دیسک بکاپ هم پر است، بکاپ را به مسیر دیگری بفرستید؛ نه به NUL. بکاپ لاگ به NUL لاگ را آزاد می‌کند ولی زنجیرهٔ بکاپ را می‌شکند و بازیابی نقطه‌ای را تا فول بعدی از دست می‌دهید. بعد از رفع فوری، بپرسید آیا این دیتابیس واقعاً به بازیابی نقطه‌ای نیاز دارد؟ اگر نه، مدل SIMPLE صادقانه‌تر است. اگر بله، بکاپ لاگ باید منظم و پایش‌شده باشد؛ جزئیاتش در آزمون بازیابی در SQL Server آمده است.

ترنزکشن طولانی: یک BEGIN TRAN فراموش‌شده

اگر علت ACTIVE_TRANSACTION باشد، یک ترنزکشن باز مانع truncate است. حتی در مدل SIMPLE، لاگ از ابتدای قدیمی‌ترین ترنزکشن فعال به بعد آزاد نمی‌شود. مقصرهای رایج: یک پنجرهٔ SSMS که کسی در آن BEGIN TRAN زده و رفته ناهار، بازسازی ایندکس روی یک جدول بزرگ، یا DELETE حجیم در یک ترنزکشن.

DBCC OPENTRAN (Sales);

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       t.transaction_begin_time,
       DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS minutes_open,
       dt.database_transaction_log_bytes_used / 1048576.0 AS log_mb
FROM sys.dm_tran_active_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
JOIN sys.dm_tran_database_transactions dt ON dt.transaction_id = t.transaction_id
WHERE dt.database_id = DB_ID(N'Sales')
ORDER BY t.transaction_begin_time;

پیش از KILL کردن، با صاحب نشست حرف بزنید. KILL یعنی rollback، و rollback یک ترنزکشن چندساعته ممکن است به همان اندازه طول بکشد و در این مدت لاگ همچنان پر بماند. برای کارهای حجیم، دسته‌بندی (batch) کردن DELETE و UPDATE به تکه‌های چندهزارتایی بهترین پیشگیری است.

رپلیکیشن و Availability Group

در رپلیکیشن ترنزکشنی، Log Reader Agent باید رکوردها را بخواند و به distributor بدهد. اگر agent متوقف شده باشد، لاگ همین‌طور رشد می‌کند. گاهی رپلیکیشن سال‌ها پیش حذف شده ولی دیتابیس هنوز علامت publish دارد؛ در این حالت با بررسی دقیق و حذف تنظیمات باقی‌مانده مشکل حل می‌شود، نه با shrink.

در Always On Availability Group، لاگ اولیه تا وقتی همهٔ ثانویه‌ها رکوردها را دریافت کرده‌اند نگه داشته می‌شود. یک ثانویهٔ قطع‌شده یا کند می‌تواند لاگ اولیه را پر کند:

SELECT ar.replica_server_name, drs.synchronization_state_desc,
       drs.log_send_queue_size AS send_queue_kb,
       drs.redo_queue_size AS redo_queue_kb,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON ar.replica_id = drs.replica_id
WHERE drs.database_id = DB_ID(N'Sales');

اگر ثانویه برای مدت طولانی برنمی‌گردد، تصمیم دربارهٔ حذف موقت آن از AG تصمیمی معماری است و باید با آگاهی از پیامدهایش گرفته شود.

VLFها و چرا shrink کورکورانه بد است

shrink کردن لاگ بعد از رفع علت، گاهی لازم است؛ مثلاً لاگی که یک بار به خاطر یک عملیات استثنایی به ۲۰۰ گیگابایت رسیده. ولی shrink به‌عنوان «راه‌حل» یا در یک جاب شبانه چند ضرر دارد:

• اگر علت سر جایش باشد، لاگ دوباره رشد می‌کند و چرخهٔ رشد و shrink تکرار می‌شود.
• رشد فایل لاگ برخلاف فایل داده از Instant File Initialization استفاده نمی‌کند (به‌جز رشدهای کوچک در نسخه‌های جدید)، پس هر رشد بزرگ نوشتن‌ها را برای لحظاتی متوقف می‌کند.
• رشدهای کوچک و مکرر تعداد VLFها را به هزاران می‌رساند، و تعداد زیاد VLF شروع دیتابیس، ریکاوری، بازیابی و رپلیکیشن را کند می‌کند.

-- Number of VLFs (SQL Server 2016 SP2+)
SELECT COUNT(*) AS vlf_count
FROM sys.dm_db_log_info(DB_ID(N'Sales'));

-- After fixing the cause: shrink once, then grow to a planned size
USE Sales;
DBCC SHRINKFILE (N'Sales_log', 1024);
ALTER DATABASE Sales
  MODIFY FILE (NAME = N'Sales_log', SIZE = 16GB, FILEGROWTH = 4GB);

اندازهٔ مناسب لاگ را از روی بزرگ‌ترین بازهٔ کار بین دو بکاپ لاگ (معمولاً شب بازسازی ایندکس) تعیین کنید، با رشد ثابت به مگابایت یا گیگابایت، نه درصدی.

پیش از پر شدن خبردار شوید

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

دی‌بی‌ماگ هر دقیقه SQL Server را می‌خواند و به‌جای نمودار، یک جمله پیامک می‌کند؛ مثلاً اینکه لاگ دیتابیس Sales ۹۲٪ پر است و علتش بکاپ لاگ چهار ساعت عقب‌افتاده است، همراه با فضای آزاد درایو. هر مشکل شناسهٔ یکتا دارد و تا حل نشود دوباره از صفر شمرده نمی‌شود، پس سیل پیامک راه نمی‌افتد؛ و وقتی لاگ به حالت عادی برگشت، پیام «حل شد» با مدت اختلال می‌آید. روند رشد فایل‌ها را هم دنبال می‌کند و روز پر شدن دیسک را تخمین می‌زند. حساب پایش sysadmin نیست و روی دیتابیس چیزی نصب نمی‌شود. توضیح کامل در dbmug.ir است. اگر سرورهای ویندوز و لینوکس را هم پایش می‌کنید، چک‌لیست پایش سرور را ببینید.

پرسش‌های پرتکرار

مدل را به SIMPLE ببرم و برگردانم به FULL؟

این کار لاگ را آزاد می‌کند ولی زنجیرهٔ بکاپ لاگ را می‌شکند. اگر در شرایط اضطراری مجبور شدید، بلافاصله پس از برگشت به FULL یک بکاپ فول (یا دیفرنشیال) بگیرید تا زنجیره دوباره شروع شود. و یادداشت کنید که در این بازه بازیابی نقطه‌ای ندارید.

چرا بعد از بکاپ لاگ، فایل لاگ کوچک نشد؟

چون بکاپ لاگ فایل را کوچک نمی‌کند؛ فقط فضای داخلش را قابل استفادهٔ دوباره می‌کند. used_log_space_in_percent را ببینید: اگر پایین آمده، مشکل حل است و حجم فایل اهمیتی ندارد، مگر اینکه به آن فضای دیسک واقعاً نیاز دارید.

چند فایل لاگ اضافه کنم تا مشکل حل شود؟

افزودن فایل لاگ دوم روی درایو دیگر می‌تواند در لحظهٔ اضطرار وقت بخرد، چون SQL Server لاگ را به‌صورت ترتیبی می‌نویسد و فایل دوم کارایی را بیشتر نمی‌کند. بعد از رفع علت، فایل اضافه را حذف کنید.

جمع‌بندی

لاگ پر علامت است، نه بیماری. با log_reuse_wait_desc علت را پیدا کنید، همان را رفع کنید (بکاپ لاگ، ترنزکشن باز، رپلیکیشن یا AG)، فقط در صورت لزوم یک بار shrink کنید و لاگ را به اندازهٔ برنامه‌ریزی‌شده برسانید. بعد پایشی بگذارید که درصد پر بودن و عقب افتادن بکاپ لاگ را پیش از رسیدن به خطای 9002 گزارش کند.