خانه/ وبلاگ/ مدیریت رشد Transaction Log
مدیریت فایل‌ها

چرا حجم Transaction Log زیاد می‌شود و چگونه آن را مدیریت کنیم؟

مدیریت پایگاه‌داده ۱ شهریور ۱۴۰۴ ۱۰ دقیقه مطالعه

یکی از رایج‌ترین سوال‌های DBAهای تازه‌کار این است: «چرا فایل .ldf پایگاه‌داده‌ام چند برابر فایل .mdf شده؟» جواب کوتاه: SQL Server هر تراکنش را قبل از اعمال روی داده، ابتدا در Transaction Log ثبت می‌کند (به این مکانیزم Write-Ahead Logging می‌گویند). اگر این لاگ هیچ‌وقت «پاک‌سازی» (Truncate) نشود، همیشه در حال رشد خواهد بود.

مفهوم کلیدی: Truncate یعنی چه؟

Truncate شدن Log به این معنی نیست که فایل کوچک‌تر می‌شود؛ یعنی فضای داخل همان فایل که دیگر لازم نیست (چون تراکنش‌های مربوطه کامل شده‌اند) دوباره قابل استفاده می‌شود. اگر Log هیچ‌وقت Truncate نشود، فضای خالی داخلش تمام می‌شود و SQL Server مجبور است فایل را با Auto Growth بزرگ‌تر کند — این همان چیزی است که باعث رشد بی‌رویه می‌شود.

نقش Recovery Model

مهم‌ترین عامل در رفتار Transaction Log، تنظیم Recovery Model پایگاه‌داده است:

Recovery Modelرفتار Logچه زمانی مناسب است
SIMPLEبعد از هر Checkpoint، خودکار Truncate می‌شودپایگاه‌داده‌های تست/توسعه یا جایی که از‌دست‌رفتن چند دقیقه داده قابل قبول است
FULLفقط با گرفتن Log Backup Truncate می‌شودسیستم‌های Production که نیاز به بازیابی تا لحظه‌ی دقیق خرابی (Point-in-Time Recovery) دارند
BULK_LOGGEDمشابه FULL، با لاگ کمتر برای عملیات‌های حجیمImport های حجیم موقت
رایج‌ترین علت رشد بی‌رویه‌ی Log: پایگاه‌داده روی FULL Recovery Model است، اما هیچ‌وقت Log Backup گرفته نمی‌شود. در این حالت، Log تا ابد رشد می‌کند چون هیچ عاملی آن را Truncate نمی‌کند.

راه‌حل درست: زمان‌بندی Log Backup منظم

اگر پایگاه‌داده روی FULL Recovery Model است (که برای اکثر سیستم‌های Production توصیه می‌شود)، باید یک Job زمان‌بندی‌شده برای گرفتن Log Backup داشته باشی — مثلاً هر ۱۵ تا ۳۰ دقیقه:

sql
-- گرفتن یک Log Backup (باعث Truncate شدن بخش استفاده‌شده‌ی Log می‌شود)
BACKUP LOG ShopDB
TO DISK = N'D:\Backups\ShopDB_log.trn';

برای آشنایی با انواع Backup و تفاوت‌شان، راهنمای تفاوت Full، Differential و Transaction Log Backup را ببین.

بررسی دلیل عدم Truncate شدن

اگر با وجود گرفتن Log Backup، همچنان Log بزرگ می‌ماند، این کوئری دلیل را نشان می‌دهد:

sql
SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'ShopDB';
log_reuse_wait_descمعنی
NOTHINGمشکلی نیست، Log آزاد است
LOG_BACKUPمنتظر گرفتن Log Backup است
ACTIVE_TRANSACTIONیک تراکنش طولانی هنوز باز است (Commit/Rollback نشده)
REPLICATIONReplication هنوز این بخش از Log را نخوانده
AVAILABILITY_REPLICAیک Replica در Always On هنوز همگام نشده

کوچک‌کردن اضطراری با Shrink (فقط برای مواقع اضطراری)

بعد از رفع علت اصلی (مثلاً بعد از گرفتن یک Log Backup که Truncate را انجام می‌دهد)، می‌توانی فضای اضافی را با DBCC SHRINKFILE واقعاً از فایل حذف کنی:

sql
DBCC SHRINKFILE (ShopDB_log, 512);
Shrink کردن مکرر Log توصیه نمی‌شود — چون به‌محض تراکنش‌های بعدی، فایل دوباره رشد می‌کند و این رشدهای مکرر باعث Fragmentation و افت عملکرد می‌شوند. Shrink را فقط یک‌بار بعد از یک رویداد غیرعادی (مثل یک Import حجیم) استفاده کن، نه به‌عنوان یک روال دائمی.

جمع‌بندی

  • رشد Log معمولاً به این معنی است که هیچ عاملی آن را Truncate نمی‌کند.
  • روی FULL Recovery Model، این عامل «Log Backup منظم» است.
  • ستون log_reuse_wait_desc دقیقاً می‌گوید Log منتظر چیست.
  • Shrink فقط یک راه‌حل موقت و اضطراری است، نه یک روتین.

می‌خوای مفاهیم تراکنش و بازیابی را کامل یاد بگیری؟

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

ادامه‌ی یادگیری
© ۱۴۰۴ SQLFarsi — سوالات متداول · وبلاگ · ساخته شده توسط مصطفی موسوی