چگونه می توان رشد Log File را کنترل نمود؟

چگونه می توان رشد Log File را کنترل نمود؟

نوشته شده توسط: غلامحسین عبادی
۰۸ خرداد ۱۴۰۱
زمان مطالعه: 5 دقیقه
۲
(۵)

مقدمه

در این مقاله می خواهیم شما را با سه تا از چالش هایی که یک DBA ممکن است با آنها روبرو شود را آشنا کنیم. یکی از مهمترین آنها رشد زیاد لاگ فایل می باشد(فایل Ldf). وظیفه لاگ فایل که یکی از فایل های مهم دیتابیس میباشد، ثبت تراکنش ها و تغییرات داده ها می باشد. بعضا مشاهده می شود که حجم لاگ فایل به طور غیرمنتظره ایی بزرگ می شود. در این مواقع چگونه می توانیم این مشکل را برطرف کنیم.

چگونه می توان رشد Log File را کنترل نمود؟

  • اگر فایل لاگ شما در حال پر شدن بود و درایو دیسک شما جا نداشت شما به عنوان یک DBA چی کار می کنید؟

اگر فایل لاگ شما در اثر عملیات مختلف همچون Rebuild کردن ایندکس ها و یا عملیات Bulk ای در حال پر شدن بود و هر لحظه ممکن بود درایو شما پر شده ودیتابیس شما به مشکل برخورد کند یک DBA چه کاری باید انجام دهد؟ در این مواقع یک DBA در وحله اول باید خونسردی خود را حفظ کرده و سپس باید یک فایل لاگ در یک درایو دیگر بسازد، در این صورت به طور اتوماتیک اسکیوال سرور بقیه تراکنش ها را در فایل لاگ دوم ایجاد می کند. و نهایتا بعد از اینکه تا حدودی مشکل برطرف گردید و شرایط عادی شد شما باید فایل های لاگ را خالی کرده و فایل لاگ دوم را پاک کنید.

دوره Performance Tuning در SQL Server

تذکر : در شرایط نرمال ما نیازی به فایل دوم لاگ فایل نداریم و اگر هم اضافه کنیم بی فایده است. چون دسترسی به لاگ فایل به صورت ترتیبی یا Sequential انجام می گیرد.

کد مربوط به افزودن یک لاگ فایل اضافه

لازم به ذکر است که توصیه می شود که این کد را در جایی ذخیره کنیم تا در شرایط بحران بتوانیم به کمک آن مشکل را حل کنیم.

USE [master]
GO
ALTER DATABASE [AdventureWorks2017] 
ADD FILE (NAME = N'AdventureWorks2017_log2', 
FILENAME = N'D:\LogFile\AdventureWorks2017_log2.Ldf' ,
SIZE = 1GB , FILEGROWTH = 1GB ) 
GO

برای کاهش حجم لاگ فایل کافیست از کد زیر استفاده کنیم:

USE [AdventureWorks2017]
GO
DBCC SHRINKFILE (N'AdventureWorks2017_log' , 32)
GO

اگر بخواهید لاگ فایل دوم را پاک کنید شما باید ابتدا لاگ فایل دومی را که ایجاد کرده اید را خالی نمایید برای اینکار از کد زیر استفاده کنید:

USE [AdventureWorks2019]
GO
DBCC SHRINKFILE (N'AdventureWorks2017' , EMPTYFILE)
GO

بعد از خالی نمودن فایل لاگ دوم، می توانیم آن فایل لاگ دوم را پاک کنیم.

USE [AdventureWorks2017]
GO
ALTER DATABASE [AdventureWorks2019] REMOVE FILE [AdventureWorks2017_log2]
GO
  • از کجا می فهمید که الان دیتابیس شما نیاز به لاگ بکاپ دارد یا خیر؟

برای اینکه متوجه شویم یک دیتابیس نیاز به لاگ بکاپ دارد یا خیر باید از DMV سیستمی Sys.Databases استفاده کنیم. این DMV در نسخه های مختلف تعداد ستون های مختلفی دارد. در نسخه اسکیوال ۲۰۱۹ ، تعداد ۸۶ ستون دارد. شما می توانید برای دیدن خروجی بهتر، دو ستون Name و log_reuse_wait_desc را مطابق کوئری زیر استفاده کنید.

Select name,log_reuse_wait_desc from sys.Databases

آیتم های ستون Log_reuse_wait_desc می تواند یکی از سیزده حالت زیر باشد:

  1. NOTHING
  2. CHECKPOINT
  3. LOG_BACKUP
  4. ACTIVE_BACKUP_OR_RESTORE
  5. ACTIVE_TRANSACTION
  6. DATABASE_MIRRORING
  7. REPLICATION
  8. DATABASE_SNAPSHOT_CREATION
  9. LOG_SCAN
  10. AVAILABILITY_REPLICA
  11. OLDEST_PAGE
  12. XTP_CHECKPOINT
  13. SLOG_SCAN

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

BACKUP LOG [AdventureWorks2017] 
TO DISK = N'D:\Dump\AdventureWorks2017_LogBackup.trn' 
WITH Compression, STATS = 10
GO
  • نحوه کاهش حجم لاگ فایل در SQL Server چگونه است؟

ممکن است حجم لاگ فایل شما از دیتا فایل دیتابیس شما بیشتر شود و لاگ فایل، فضای زیادی گرفته باشد، چطور می توانیم حجم فایل لاگ را کاهش دهیم. خیلی از افراد جهت کاهش حجم فایل لاگ از روش Detach و Attach استفاده می کنند که کار بسیار خطرناکی می باشد. همچنین افرادی هم هستند که Recovery Model دیتابیس خود را به Simple تغییر داده تا لاگ خود را از بین برده و سپس آن را به Full تغییر داده و در نهایت فول بکاپ می گیرند که این هم کار توصیه نمی شود(مخصوصا اگر در حوضه بانکی فعالیت دارد و دیتابیس های شما حساس و مهم می باشد). برای کاهش حجم لاگ فایل ابتدا Recovery Model دیتابیس خود را چک کنید، که آیا Full است و یا اینکه Simple است.

الف ) اگر Recovery Model دیتابیس شما Full باشد:

اگر Recovery Model دیتابیس شما Full بود در این صورت شما برای کاهش حجم لاگ فایل باید ابتدا لاگ بکاپ بگیرید و سپس فایل لاگ را Shrink کنید. حال به کمک SP_HelpFile حجم فایل لاگ را نگاه می کنیم اگر کاهش نیافته بود برای بار دوم ابتدا لاگ بکاپ گرفته و سپس فایل لاگ را Shrink کنید. حال به کمک SP_HelpFile حجم فایل لاگ را نگاه می کنیم اگر حجم لاگ فایل کاهش نیافته بود شما باید برای بار سوم از لاگ فایل بکاپ گرفته و سپس فایل لاگ را Shrink کنید.

لازم به ذکر است که برای دیدن حجم لاگ فایل می توانید از اس پی سیستمی SP_HelpFile کمک بگیرید و برای دیدن اینکه آیا دیتابیس شما نیاز به لاگ بکاپ دارد یا خیر می توانید از کوئری زیر کمک بگیرید.

Select name,log_reuse_wait_desc from sys.Databases
Where name='AdventureWorks2019'

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

چگونه می توان رشد Log File را کنترل نمود؟

ب) زمانی که Recovery Model ما Simple باشد.

زمانی که Recovery Model ما Simple بود در این صورت باید برای کاهش حجم لاگ فایل ابتدا دستور Checkpoint را اجرا نموده و سپس لاگ بکاپ بگیریم.حال به کمک SP_HELPFILE حجم فایل لاگ را نگاه می کنیم اگر کاهش نیافته بود برای بار دوم دستور Checkpoint را اجرا نموده و سپس لاگ بکاپ می گیریم. باز هم به کمک SP_HelpFile حجم لاگ فایل خود را چک می کنیم اگر کاهش نیافته بود برای بار سوم دستور Checkpoint را اجرا و سپس لاگ بکاپ می گیریم.

تذکر: ممکن است با همان دفعه اول که دستور Checkpoint اجرا کرده و سپس لاگ بکاپ می گیرید حجم لاگ فایل شما کاهش یابد و ممکن است تا سه بار این کار تکرار شده تا حجم لاگ فایل کاهش یابد.

سوال: چگونه می توانیم حجم لاگ فایل را کنترل نماییم؟

برای کنترل حجم لاگ فایل زمانی که Recovery Model دیتابیس ما Full می باشد بهتر است از دیتابیس خود به صورت دوره ایی لاگ بکاپ گرفته شود. زمان گرفتن لاگ بکاپ بستگی به حساسیت دیتاهای ما دارد اگر داده های ما خیلی حساس و مهم باشد ممکن است مجبور شویم که هر ۱۵ دقیقه یکبار ، یک لاگ بکاپ بگیریم.

سوال: چگونه می توانیم حجم لاگ فایل را کنترل نماییم؟

Best Practice جهت گرفتن بکاپ

یکی از بهترین Solution ها (سناریوها) جهت گرفتن بکاپ این است که یک روز در هفته Full بکاپ بگیریم، هر روز یکبار Differential بکاپ بگیریم و هر دو ساعت یکبار ، یک لاگ بکاپ بگیریم. دقت کنید که با توجه به حساسیت داده ها و میزان فضایی که برای ذخیره بکاپ ها در اختیار ما قرار می دهند ، این سناریو ممکن است تغییر کند.

چه رتبه ای می‌دهید؟

میانگین ۲ / ۵. از مجموع ۵

اولین نفر باش

title sign
دانلود مقاله
چگونه می توان رشد Log File را کنترل نمود؟
فرمت PDF
5 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
غلامحسین عبادی
مقالات
13 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
غلامحسین عبادی

غلامحسین عبادی هستم، سوابق حرفه‌ای من به شرح ذیل است: ۱- ۸ سال سابقه کار به عنوان برنامه نویس در شرکت خودرو سازی سایپا، ۲- ۱۱ سال سابقه کاری در شرکت ایران خودرو به عنوان رئیس برنامه‌های کاربردی و ۴ سال آخر مسئول دیتابیس، ۳- ۴ سال سابقه DBA در شرکت داده ورزی سداد، ۴- دارای مدرک MCSD مایکروسافت از کشور امارات، ۵- دارای مقالات متعدد در ماهنامه تجارات الکترونیک ۶- بیش از یک سال سابقه DBA در شرکت خدمات رایانه‌ای امید، ۷- مدرسی SQL Server در برخی سازمان‌های دولتی ۸- ۱۶ سال سابقه تدریس در دانشگاه‌ها و سازمان‌های مختلف ۹- مولف کتاب‌های متعدد در حوزه برنامه نویسی و SQL Server

پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • سلام و عرض ادب
      اگر هارد دیسک ما فضای خالی داشته باشه و کمبود فضا نداشته باشیم باز نیاز به کم کردن حجم فایل لاگ هست؟

      من سرورم هر ۳ ساعت یکبار بکاپ فول میگیره ایا نیاز هست حجم فایل لاگ را کم کنم؟

    • سلام دوست عزیز :
      شما ممکن است در آن لحظه Transaction باز داشته باشید (در این صورت می توانید با دستور DBCC OPENTRAN آن را مشاهده کنید) که در این صورت اسکیوال سرور به شما اجازه کاهش حجم لاگ فایل را نخواهد داد. همچنین با گرفتن فول بکاپ احتمال زیاد می توانید باعث کاهش حجم لاگ فایل شوید.

    • با سلام و احترام
      راهکاری که در مقاله گفتین برای یکی از دیتابیس هامون خیلی عالی جواب داد و حجم لاگ فایل عالی کاهش داشت ولی برای یکی از دییتابیس ها که به صورت میرر هست تغییری اعمال نکرد
      در هر صورت باتشکر از شما

    • با سلام و خسته نباشید. ممنون از استاد عزیز و تیم نیک آموز

    • با سلام و خسته نباشید. سپاس از مقاله بسیار عالی شما و انتخاب موضوعات جذاب و کاربردی
      فقط جسارتا فرمودید: زمانی که Recovery Model ما Simple بود در این صورت باید برای کاهش حجم لاگ فایل ابتدا دستور Checkpoint را اجرا نموده و سپس لاگ بکاپ بگیریم.
      سوالی که برام اومده این هستش که لازمه گرفتن لاگ بک آپ تنظیم Recovery Model بر روی حالت FULL یا Bulk_Logged هستش. در حالت Simple فکر نکنم بتونیم لاگ بک آپ بگیریم…

      • سلام و درود مهندس جان،
        کاملا حق باشماست ، اشتباره تایپی بود. متشکرم از دقت شما.
        زمانی که Recovery Model ما بر روی Simple قرار دارد، باز هم ممکن است حجم لاگ فایل ما رشد کند برای کاهش حجم لاگ فایل ابتدا باید Checkpoint زده و سپس حجم لاگ فایل را به کمک دستور Shrinkfile کاهش دهیم(این دو دستور را بعضا ممکن است مجبور شویم تا سه مرتبه تکرار تکرار کنیم).

    • سلام. ممنون از مقاله بسیار عالی شما.
      در قسمت ب فرمودید: زمانی که Recovery Model ما Simple بود در این صورت باید برای کاهش حجم لاگ فایل ابتدا دستور Checkpoint را اجرا نموده و سپس لاگ بکاپ بگیریم.
      سوالی که برام پیش اومده این هستش که شرط گرفتن لاگ بک آپ داشتن Recovery Model به صورت FULL نبود؟ یعنی برای Recovey Model به صورت Simple امکان گرفتن لاگ بک آپ وجود ندارد

    • بسیار عالی بود

ثبت نام رایگان در همایش Tehran .NET Conf 2023 ، همین الان کلیک کنید
ثبت نام رایگان..
close-image