چرا حجم 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 های حجیم موقت |
FULL Recovery Model است، اما هیچ‌وقت Log Backup گرفته نمیشود. در این حالت، Log تا ابد رشد میکند چون هیچ عاملی آن را Truncate نمی‌کند.راهحل درست: زمان‌بندی Log Backup منظم
اگر پایگاه‌داده روی FULL Recovery Model است (که برای اکثر سیستم‌های Production توصیه میشود)، باید یک Job زمان‌بندی‌شده برای گرفتن Log Backup داشته باشی — مثلاً هر ۱۵ تا ۳۰ دقیقه:
-- گرفتن یک 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 بزرگ می‌ماند، این کوئری دلیل را نشان می‌دهد:
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 نشده) |
| REPLICATION | Replication هنوز این بخش از Log را نخوانده |
| AVAILABILITY_REPLICA | یک Replica در Always On هنوز همگام نشده |
کوچک‌کردن اضطراری با Shrink (فقط برای مواقع اضطراری)
بعد از رفع علت اصلی (مثلاً بعد از گرفتن یک Log Backup که Truncate را انجام می‌دهد)، می‌توانی فضای اضافی را با DBCC SHRINKFILE واقعاً از فایل حذف کنی:
DBCC SHRINKFILE (ShopDB_log, 512);
جمع‌بندی
- رشد Log معمولاً به این معنی است که هیچ عاملی آن را Truncate نمی‌کند.
- روی FULL Recovery Model، این عامل «Log Backup منظم» است.
- ستون
log_reuse_wait_descدقیقاً می‌گوید Log منتظر چیست. - Shrink فقط یک راهحل موقت و اضطراری است، نه یک روتین.
سوالات متداول درباره‌ی این موضوع
چرا Transaction Log مدام بزرگ میشود؟
در Recovery Model برابر Full، تا وقتی Log Backup نگیری فضای Log آزاد (Truncate) نمیشود و فایل مدام رشد میکند. تراکنشهای بازمانده هم مانع آزادسازی میشوند.
آیا Shrink کردن Log امن است؟
فقط بهصورت اضطراری و موقت. Shrink مکرر باعث Fragmentation و رشد دوبارهی Log میشود؛ راهحل اصلی Log Backup منظم است.
چه زمانی Simple Recovery Model مناسب است؟
وقتی بازیابی تا یک لحظهی دلخواه (Point-in-Time) لازم نیست و Backupهای Full/Differential کافیاند، مثل پایگاهدادههای آزمایشی.
می‌خوای مفاهیم تراکنش و بازیابی را کامل یاد بگیری؟
دوره‌ی رایگان SQLFarsi را با درس تراکنش‌ها ادامه بده.
ادامه‌ی یادگیری