دلایل شناسایی و رفع مشکل پر شدن لاگ تراکنش SQL Server

چرا لاگ تراکنش SQL Server پر می‌شود؟ دلایل، شناسایی و راه‌حل‌ها

لاگ تراکنش در SQL Server یکی از اجزای حیاتی است که تمام تراکنش‌ها و تغییرات پایگاه داده را ثبت می‌کند. پر شدن این لاگ می‌تواند عملیات پایگاه داده را متوقف کند. درک دلایل پر شدن آن و نحوه مدیریت آن برای هر مدیر پایگاه داده (DBA) بسیار مهم است.

لاگ تراکنش یک رکورد ترتیبی از تمام تغییراتی است که در پایگاه داده رخ می‌دهد. وظایف اصلی آن عبارتند از:
بازیابی (Recovery): بازیابی پایگاه داده به حالت سازگار در صورت خرابی.
برگرداندن (Rollback): خنثی کردن تراکنش‌هایی که commit نشده‌اند.
Replication/Mirroring/AlwaysOn: پشتیبانی از راهکارهای High Availability و Disaster Recovery.

اندازه لاگ تراکنش بر اساس مدل بازیابی پایگاه داده و پشتیبان‌گیری از لاگ مدیریت می‌شود.

چرا لاگ تراکنش پر می‌شود؟

عوامل متعددی می‌توانند باعث پر شدن لاگ تراکنش شوند و از نوشتن داده‌های جدید در پایگاه داده جلوگیری کنند. ستون `log_reuse_wait_desc` در `sys.databases` کلید شناسایی علت این مشکل است. دلایل رایج عبارتند از:

عدم پشتیبان‌گیری از لاگ (در مدل بازیابی FULL یا BULK_LOGGED): در این مدل‌ها، فایل لاگ تنها پس از پشتیبان‌گیری از لاگ قابل truncate شدن است. اگر پشتیبان‌گیری انجام نشود، لاگ به طور نامحدود رشد می‌کند تا دیسک پر شود.
تراکنش‌های طولانی‌مدت (Long-Running Transactions): یک تراکنش باز که برای مدت طولانی اجرا می‌شود، از truncate شدن لاگ جلوگیری می‌کند، زیرا تمام رکوردهای لاگ تا شروع قدیمی‌ترین تراکنش فعال باید نگهداری شوند.
فضای دیسک ناکافی (Insufficient Disk Space): ساده‌ترین دلیل؛ درایوی که فایل لاگ در آن قرار دارد، فضای کافی ندارد.
Database Mirroring / AlwaysOn Availability Groups: اگر session مربوط به mirroring یا replica گروه در دسترس‌پذیری (Availability Group) از کار عقب بماند، لاگ نمی‌تواند truncate شود تا زمانی که تغییرات به secondary ارسال و hardening شوند.
Replication: اگر تراکنش‌ها به subscribers replicate نشوند، لاگ نمی‌تواند truncate شود.
Database Snapshots: گاهی اوقات یک database snapshot داخلی می‌تواند رکوردهای لاگ را نگهداری کند.

شناسایی علت پر شدن لاگ تراکنش

برای تشخیص مشکل پر شدن لاگ تراکنش، باید میزان استفاده فعلی از آن و مقدار `log_reuse_wait_desc` را بررسی کنید.

1. بررسی میزان استفاده از فضای لاگ:
می‌توانید از `DBCC SQLPERF(LOGSPACE)` برای مشاهده درصد فضای لاگ استفاده شده برای هر پایگاه داده استفاده کنید.

DBCC SQLPERF(LOGSPACE);

این دستور `Database Name`، `Log Size (MB)` و `Log Space Used (%)` را برمی‌گرداند.

2. شناسایی دلیل انتظار برای استفاده مجدد از لاگ:
ستون `log_reuse_wait_desc` در `sys.databases` بسیار حیاتی است.

SELECT name, log_reuse_wait_desc FROM sys.databases;

مقادیر رایج برای `log_reuse_wait_desc` عبارتند از:
`NOTHING`: لاگ برای استفاده مجدد در دسترس است.
`LOG_BACKUP`: نیاز به پشتیبان‌گیری از لاگ است (رایج‌ترین برای FULL/BULK_LOGGED).
`ACTIVE_TRANSACTION`: یک تراکنش فعال از truncate شدن لاگ جلوگیری می‌کند.
`REPLICATION`: مشکلات Replication از truncate شدن جلوگیری می‌کند.
`AVAILABILITY_REPLICA`: replica مربوط به AlwaysOn Availability Group عقب افتاده است.
`MIRRORING`: Database mirroring از truncate شدن جلوگیری می‌کند.

نحوه رفع مشکل پر شدن لاگ تراکنش

راه حل بستگی به مقدار `log_reuse_wait_desc` دارد.

1. اگر `log_reuse_wait_desc` برابر `LOG_BACKUP` باشد (رایج‌ترین):
از لاگ تراکنش پشتیبان‌گیری کنید. این کار به لاگ اجازه می‌دهد تا truncate شود.

BACKUP LOG YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName_log.trn';

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

DBCC SHRINKFILE (LogFileLogicalName, TargetSizeInMB);

مثال:

USE YourDatabaseName;
GO
SELECT name, size FROM sys.database_files WHERE type = 1; -- Find logical name of log file
GO
DBCC SHRINKFILE (N'YourDatabaseName_log', 1024); -- Shrink to 1GB

2. اگر `log_reuse_wait_desc` برابر `ACTIVE_TRANSACTION` باشد:
تراکنش طولانی‌مدت را شناسایی و خاتمه دهید.

DBCC OPENTRAN;

این دستور می‌تواند به شناسایی قدیمی‌ترین تراکنش فعال کمک کند. ممکن است لازم باشد SPID مربوط به آن را kill کنید، اما این کار باید با احتیاط انجام شود.

3. اگر `log_reuse_wait_desc` برابر `REPLICATION`، `AVAILABILITY_REPLICA` یا `MIRRORING` باشد:
مشکل اصلی در replication، AlwaysOn یا mirroring را برطرف کنید تا جریان داده بازیابی شود. پس از حل مشکل، truncate لاگ از سر گرفته خواهد شد.

پیشگیری از پر شدن آتی لاگ تراکنش

پشتیبان‌گیری منظم از لاگ: یک استراتژی قوی برای پشتیبان‌گیری از لاگ در پایگاه داده‌هایی با مدل بازیابی FULL یا BULK_LOGGED پیاده‌سازی کنید. فرکانس آن به RPO (Recovery Point Objective) شما بستگی دارد.
نظارت بر فضای دیسک: به طور منظم فضای دیسک برای فایل‌های لاگ را بررسی کنید.
نظارت بر `log_reuse_wait_desc`: این ستون را به طور فعال نظارت کنید تا مشکلات احتمالی را زودتر تشخیص دهید.
مدل بازیابی مناسب: اطمینان حاصل کنید که پایگاه داده از مدل بازیابی مناسب استفاده می‌کند. اگر بازیابی نقطه‌ای (point-in-time recovery) نیاز نیست، مدل بازیابی SIMPLE را در نظر بگیرید.

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;

توجه: تغییر به مدل بازیابی SIMPLE زنجیره پشتیبان‌گیری از لاگ را قطع می‌کند. بلافاصله پس از تغییر مدل بازیابی، یک پشتیبان‌گیری کامل (full backup) بگیرید.

تنظیمات Auto-Growth: تنظیمات auto-growth مناسب برای فایل‌های لاگ خود را پیکربندی کنید (به عنوان مثال، به مگابایت و نه درصد، و با افزایش منطقی) تا از رشد‌های کوچک و مکرر جلوگیری شود. این کار به بهبود عملکرد کمک می‌کند اما از رشد نامحدود در صورت عدم مدیریت جلوگیری نمی‌کند.

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