کاهش حجم فایل Log در SQL Server

کاهش حجم فایل Log در SQL Server (Shrink کردن LDF)

کاهش حجم فایل لاگ (LDF) در SQL Server که با دستور SHRINKFILE انجام می‌شود، عملاً باعث حذف فضای خالی انتهای فایل و کوچک‌سازی فیزیکی آن می‌گردد.
با این حال، انجام این کار بدون مدیریت صحیح وضعیت لاگ (مانند عدم تعویض Simple Recovery Model یا عدم بک‌گیری منظم Transaction Log) بی‌فایده یا موقتی است، زیرا لاگ بلافاصله دوباره رشد می‌کند.
نکته حیاتی این است که Shrink کردن لاگ را هرگز نباید به صورت معمول و روزمره انجام داد، زیرا باعث ایجاد fragmentation شدید در فایل و کاهش کارایی می‌شود.
بلکه تنها راهکار اصولی، برنامه‌ریزی منظم برای بکاپ لاگ (در مدل Full) یا تغییر مدل ریکاوری به Simple و سپس کوچک‌سازی یک‌باره برای بازیابی فضای اضافی است.

فایل لاگ (LDF) چیست و چرا رشد می‌کند؟

قبل از هر اقدامی، باید درک صحیحی از ماهیت فایل لاگ داشته باشیم.
فایل تراکنش لاگ در SQL Server مسئول ثبت تمام تغییرات اعمال شده روی دیتابیس است.
این فایل برای قابلیت‌های حیاتی زیر استفاده می‌شود:

• بازیابی تراکنش‌ها در صورت خرابی (Rollback)
• بازیابی دیتابیس تا یک نقطه زمانی مشخص (Point-in-Time Recovery)
• تکثیر دیتابیس به سرورهای دیگر (Replication و Always On)

علت اصلی رشد بی‌رویه فایل لاگ، تنظیمات Recovery Model دیتابیس است. SQL Server سه نوع Recovery Model دارد:

• Simple Recovery Model

• Full Recovery Model

• Bulk-Logged Recovery Model

تشخیص مشکل: آیا فایل لاگ واقعاً بزرگ است؟

پیش از هر اقدامی، باید بررسی کنید که آیا واقعاً فایل لاگ مشکل دارد یا خیر:

-- مشاهده حجم و درصد استفاده از فضای لاگ برای همه دیتابیس‌ها
DBCC SQLPERF(logspace);

این دستور نشان می‌دهد هر دیتابیس چه مقدار از فضای لاگ خود را استفاده کرده است.
اگر عدد Log Size بسیار بزرگ باشد اما Log Space Used کم باشد، یعنی فضای خالی زیادی داخل فایل وجود دارد.
در این حالت Shrink کردن مؤثر خواهد بود.

برای مشاهده جزئیات فایل‌های یک دیتابیس خاص:

USE [YourDatabaseName];
GO

SELECT 
    name AS [نام فایل],
    type_desc AS [نوع],
    size / 128.0 AS [حجم فعلی (مگابایت)],
    physical_name AS [مسیر فیزیکی]
FROM sys.database_files;

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

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

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

روش‌های کاهش حجم فایل لاگ در SQL

روش اول: Shrink کردن بدون قطع زنجیره Backup (روش اصولی)

اگر دیتابیس شما در Full Recovery Mode است و نمی‌خواهید زنجیره Backup لاگ قطع شود، ابتدا یک Backup از لاگ بگیرید، سپس Shrink کنید:

USE [YourDatabaseName];
GO

-- مرحله 1: Backup از Transaction Log
BACKUP LOG [YourDatabaseName] 
TO DISK = 'C:\DBBACKUP\YourDatabaseName_LogBackup.trn'
WITH COMPRESSION;
GO

-- مرحله 2: بررسی نام واقعی فایل لاگ
SELECT name FROM sys.database_files WHERE type_desc = 'LOG';
GO

-- مرحله 3: Shrink کردن فایل لاگ (نام فایل را جایگزین کنید)
DBCC SHRINKFILE (N'YourDatabaseName_log', 512); -- کاهش به 512 مگابایت
GO

روش دوم: Shrink سریع با تغییر Recovery Model (روش اضطرار)

زمانی که دیسک کاملاً پر شده و حتی امکان Backup لاگ وجود ندارد، از این روش استفاده کنید:

USE [YourDatabaseName];
GO

-- گام 1: تغییر به Simple Recovery Model
ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;
GO

-- گام 2: کوچک کردن فایل لاگ
DBCC SHRINKFILE (N'YourDatabaseName_log', 0); -- 0 یعنی حداقل ممکن
GO

-- گام 3: بازگشت به Full Recovery Model (اختیاری)
ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;
GO

هشدار مهم: این روش باعث قطع شدن زنجیره Backup لاگ می‌شود. پس از اتمام کار، حتماً یک Full Backup کامل بگیرید.

روش سوم: استفاده از SSMS (رابط گرافیکی)

برای کاربرانی که با خط فرمان راحت نیستند:

• در SQL Server Management Studio، روی دیتابیس مورد نظر راست کلیک کنید
• Tasks → Shrink → Files را انتخاب کنید
• File Type را روی Log قرار دهید
• گزینه "Release unused space" را انتخاب کنید
• مقدار مورد نظر را وارد کرده و OK کنید

چرا Shrink کردن همیشه نتیجه نمی‌دهد؟

گاهی بعد از اجرای دستور Shrink، حجم فایل لاگ تغییری نمی‌کند. علت آن وجود Virtual Log File (VLF)های فعال در انتهای فایل است.
VLFها واحدهای داخلی فایل لاگ هستند. اگر یک VLF فعال در انتهای فایل قرار داشته باشد، SQL Server نمی‌تواند آن قسمت از فایل را آزاد کند.

راه حل در این شرایط:

-- اجرای مکرر دستور Shrink تا 5 بار
DBCC SHRINKFILE (N'YourDatabaseName_log', 100);
-- بررسی نتیجه و تکرار در صورت نیاز

یا در شرایط بحرانی، می‌توانید یک Checkpoint دستی انجام دهید:

CHECKPOINT;
GO
DBCC SHRINKFILE (N'YourDatabaseName_log', 0);
GO

تنظیمات Auto Growth: پیشگیری از رشد مجدد

بعد از کوچک کردن فایل لاگ، باید تنظیمات رشد خودکار را بهینه کنید:

-- مشاهده تنظیمات فعلی رشد
SELECT 
    name,
    growth,
    is_percent_growth,
    CASE 
        WHEN is_percent_growth = 1 THEN 'درصدی'
        ELSE 'مقدار ثابت به مگابایت'
    END AS [نوع رشد]
FROM sys.database_files
WHERE type_desc = 'LOG';

-- تغییر به رشد ثابت 512 مگابایتی (به جای درصد)
ALTER DATABASE [YourDatabaseName]
MODIFY FILE (
    NAME = N'YourDatabaseName_log',
    FILEGROWTH = 512MB  -- رشد 512 مگابایت در هر بار
);

چرا رشد درصدی بد است؟ اگر رشد را 10 درصد تنظیم کنید، زمانی که فایل لاگ به 100 گیگابایت برسد، هر بار رشد 10 گیگابایت اتفاق می‌افتد که می‌تواند دیسک را خیلی سریع پر کند.

استراتژی Backup لاگ در Full Recovery Model

اگر دیتابیس شما در Full Recovery Mode است، باید Backup لاگ را به صورت منظم برنامه‌ریزی کنید:

• نوع دیتابیس    فرکانس پیشنهادی Backup لاگ
• دیتابیس‌های حیاتی (بانکی، بورس)    هر 5-15 دقیقه
• دیتابیس‌های عملیاتی معمولی    هر 30-60 دقیقه
• دیتابیس‌های گزارش‌گیری    هر 4-8 ساعت
• دیتابیس‌های تست و توسعه    Simple Recovery کافی است

مثال ایجاد یک Agent Job برای Backup خودکار لاگ

-- ایجاد یک Job جدید در SQL Server Agent
USE msdb;
GO

EXEC dbo.sp_add_job
    @job_name = N'Regular Log Backup - YourDatabase' ;
GO

-- (برای ادامه تنظیمات Job، از SSMS استفاده کنید)

چه زمانی نباید Shrink کرد؟

• Shrink کردن فایل لاگ یک عملیات سنگین است و معایبی دارد:
• Fragmentation: باعث خرد شدن فایل‌های دیتابیس و کاهش performance می‌شود
• مصرف منابع: در حین Shrink، CPU و I/O مصرف می‌شود
• رشد مجدد: اگر علت اصلی را برطرف نکنید، لاگ دوباره رشد می‌کند و مجبور به Shrink مکرر می‌شوید
• قاعده طلایی: Shrink کردن یک راهکار موقتی است، نه راهحل دائمی. راهحل اصلی، مدیریت صحیح Backup لاگ و Recovery Model است.

سناریوهای واقعی و راهکارها کاهش حجم فایل Log در SQL Server

سناریو 1: دیسک کاملاً پر شده و دیتابیس از کار افتاده است

-- راهکار: تغییر به Simple و Shrink فوری
ALTER DATABASE [ProblemDB] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
DBCC SHRINKFILE (N'ProblemDB_log', 0);
GO
-- سپس فضای خالی روی دیسک بررسی کنید

سناریو 2: دیتابیس Always On دارد و نمی‌توان Recovery Model را تغییر داد

در این حالت نمی‌توانید به Simple تغییر دهید. راهکار:

-- Backup از لاگ روی دیسک دیگر (اگر فضای کافی وجود دارد)
BACKUP LOG [ProblemDB] TO DISK = 'D:\Backup\Log_Backup.trn';
GO
DBCC SHRINKFILE (N'ProblemDB_log', 1024); -- کاهش به 1 گیگ
GO

سناریو 3: لاگ خیلی بزرگ است اما فضای داخلی خالی کمی دارد

اگر DBCC SQLPERF(logspace) نشان می‌دهد که Log Space Used بالاست، یعنی واقعاً نیاز به فضای لاگ دارید.
در این حالت Shrink کردن کمکی نمی‌کند. باید:

• برنامه‌ای که تراکنش‌های طولانی اجرا می‌کند را پیدا کنید
• آن تراکنش را Commit یا Rollback کنید
• در صورت نیاز، فضای دیسک را افزایش دهید

جمع‌بندی و توصیه‌های نهایی

مشکل	راهکار دائمی
رشد بی‌رویه LDF در Full Recovery	تنظیم Backup خودکار لاگ (هر 15-60 دقیقه)
رشد بی‌رویه LDF در Simple Recovery	بررسی تراکنش‌های طولانی و باز بودن connection‌ها
دیسک پر شده و اضطرار	تغییر موقت به Simple Recovery + Shrink
Shrink جواب نمی‌دهد	اجرای CHECKPOINT و تکرار Shrink

 

نکات کلیدی برای حفظ سلامت دیتابیس:

• هرگز Shrink کردن را به عنوان یک Job منظم schedule نکنید
• در Full Recovery Model، حتماً Backup لاگ منظم داشته باشید
• Auto Growth را به مقدار ثابت (مثلاً 512MB) تنظیم کنید، نه درصدی
• ماهیانه یکبار وضعیت فضای لاگ را بررسی کنید
• اگر نیازی به Point-in-Time Recovery ندارید، دیتابیس را در Simple Recovery Model نگه دارید

با رعایت این اصول، دیگر با مشکل پر شدن دیسک به دلیل رشد فایل لاگ مواجه نخواهید شد و دیتابیس شما همواره در دسترس و پایدار خواهد ماند.