مدیریت حجم Log File دیتابیس

چگونه رشد بی رویه لاگ فایل دیتابیس را مدیریت کنیم؟

با گذشت زمان، حجم Log File دیتابیس‌ها به طور بی‌رویه افزایش می‌یابد، مخصوصاً در صورتی که ماژول‌هایی برای حذف داده‌ها از دیتابیس وجود نداشته باشد و یا برای افزایش سرعت دسترسی به داده‌ها، ماژول‌های نوشته شده به صورت خودکار Log File را پر نکند.

در این موارد، Log File دیتابیس به طور پی در پی بزرگتر می‌شود و در نهایت ممکن است منجر به پر شدن فضای دیسک و کاهش سرعت دسترسی به دادها شود. برای مدیریت رشد بی‌رویه Log File دیتابیس، بهتر است به نکات زیر توجه کنید:

بررسی سیاست‌های نگهداری داده:

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

تنظیم حجم Log File:

می‌توان حجم Log File دیتابیس را تنظیم کرد تا حجم آن به صورت منظم کنترل شود. برای این کار، می‌توان از دستورات DBCC SHRINKFILE یا DBCC SQLPERF (LOGSPACE) در SQL Server استفاده کرد. با استفاده از این دستورات، می‌توان ابتدا حجم Log File را کاهش داده و سپس حجم آن را تا حداقل مجاز تنظیم کرد.

استفاده از روش‌های پشتیبانی و بازیابی دیتابیس:

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

تنظیمات دیتابیس:

تنظیمات دیتابیس نیز می‌تواند به کنترل رشد بی‌رویه Log File کمک کند. برای مثال، با تنظیماتی مانند Recovery Model و Backup و Restore Options، می‌توان از Log File بهینه استفاده کرد و در عین حال جلوی رشد بی‌رویه آن را گرفت.

مانیتورینگ دقیق و منظم:

برای کنترل رشد بی‌رویه Log File دیتابیس، باید به صورت دقیق و منظم مانیتورینگی انجام شود. برای مثال، با استفاده از SQL Server Management Studio، می‌توان مانیتورینگی مانند نظارت بر حجم Log File، نظارت بر میزان فضای استفاده شده در دیسک و اعلان‌هایی که به صورت خودکار برای رشد بی‌رویه Log File دیتابیس ایجاد می‌شوند، برای جلوگیری از رشد بی‌رویه Log File استفاده کرد.
در نهایت، با توجه به مهم بودن داده‌های موجود در دیتابیس و اهمیت حفاظت از آن‌ها، باید به دقت و با دانش کافی برای کنترل رشد بی‌رویه Log File دیتابیس اقدام شود و در صورت نیاز، با استفاده از راه حل‌هایی مانند تنظیمات دیتابیس، پشتیبانی منظم و مانیتورینگ دقیق، از داده‌های خود مراقبت کرد.

در ادامه با استفاده از کد TSQL زیر میتوانید نسبت به بهینه سازی Log File اقدام نمایید:

SELECT name, filename, size/128 AS Size_MB, FILEPROPERTY(name,'SpaceUsed')/128 AS SpaceUsed_MB, (size/128)/(FILEPROPERTY(name,'SpaceUsed')/128) AS ratio
INTO #logfiles
FROM sys.sysfiles
WHERE RIGHT(filename, 4) = '.ldf'

DECLARE @ratio FLOAT
SET @ratio = (SELECT ratio FROM #logfiles)

IF @ratio > 10
BEGIN
    DECLARE @shrink_size INT
    SET @shrink_size = (SELECT FILEPROPERTY(name, 'SpaceUsed')*10 FROM #logfiles)
    DBCC SHRINKFILE('DWH_log', @shrink_size)
END

DROP TABLE #logfiles

این کد به منظور مدیریت حجم فایل های لاگ دیتابیس استفاده می‌شود.

در این کد، در قسمت اول، با استفاده از دستور SELECT، اطلاعات مختلفی از فایل های لاگ با پسوند .ldf دیتابیس از جمله نام فایل، مسیر آن، اندازه آن به صورت مگابایت (Size_MB)، میزان فضای استفاده شده در فایل لاگ به صورت مگابایت (SpaceUsed_MB) و نسبت اندازه فایل لاگ به فضای استفاده شده (ratio) استخراج می شود. در اینجا، از جدول sysfiles در دیتابیس SQL Server برای گرفتن این اطلاعات استفاده می‌شود.

در قسمت دوم، با استفاده از دستور INTO، این اطلاعات به یک جدول موقت با نام #logfiles منتقل می‌شوند.

در قسمت سوم، با استفاده از دستور DECLARE و SET، متغیری به نام @ratio تعریف شده و مقدار نسبت اندازه فایل لاگ به فضای استفاده شده در آن در آن ذخیره می‌شود که قبلا در قسمت SELECT محاسبه شده بود.

در قسمت چهارم، با استفاده از دستور IF، ابتدا بررسی می‌شود که آیا مقدار @ratio بیشتر از ۱۰ است یا خیر. اگر بیشتر باشد، با استفاده از دستورات DECLARE و SET، متغیری به نام @shrink_size تعریف شده و مقدار آن برابر با ضرب میزان فضای استفاده شده در فایل لاگ در ده برابر می‌شود و در آن ذخیره می‌شود.

در نهایت، با استفاده از دستور DBCC SHRINKFILE، اندازه فایل لاگ با نام DWH_log به اندازه حجم محاسبه شده در متغیر @shrink_size کاهش داده می‌شود.

در قسمت پایانی، با استفاده از دستور DROP TABLE، جدول موقت #logfiles حذف می‌شود.

در کل، این کد برای کنترل حجم فایل های لاگ دیتابیس، محاسبه نسبت اندازه فایل لاگ به فضای استفاده شده در آن و در صورت لزوم، کاهش حجم فایل لاگ به صورت خودکار استفاده می‌شود.

این کد برای دیتابیس های SQL Server مناسب است و باید با دقت و با دانش کافی مدیریت شود. همچنین، باید توجه داشت که استفاده بیش از حد از دستورات کاهش حجم فایل های لاگ، ممکن است منجر به ایجاد فرایندهای اضافی در دیتابیس شود و باعث کاهش سرعت عملکرد آن شود.

بنابراین، قبل از استفاده از این دستورات، باید به دقت بررسی شود که آیا استفاده از آن‌ها مناسب است یا خیر.

بستیفای مرجع ارائه فایل‌های کاربردی در زمینه های مختلف کسب و کار است،  در صورتی که نیاز به سفارشی سازی یا طراحی فایل اختصاصی جدید می‌باشید، می‌توانید نسبت به ارائه درخواست با بستیفای در ارتباط باشید.

مطالعه بیشتر