برای مشاهده یافته ها از کلید Enter و برای خروج از کلید Esc استفاده کنید.

رشد بی رویه لاگ فایل دیتابیس

یکی از مشکلاتی که گاها مشاهده و در مورد آن سوال پرسیده می شود رشد زیاد لاگ فایل (فایل Ldf) یک دیتابیس است که خیلی از افراد برای رفع آن کار خطرناک Detach و Attach کردن دیتابیس را انجام می دهند. با من همراه بشوید که در این مقاله چهار موضوع زیر را بررسی کنیم:

  • لاگ فایل چیست

  • رشد لاگ فایل به چه دلیل است

  • چگونه می توان رشد لاگ فایل را کنترل کرد

  • چگونه می توان حجم لاگ فایل را کاهش داد

 

لاگ فایل چیست

لاگ فایل یکی از فایل های دیتابیس است که زمان ایجاد دیتابیس ساخته می شود و کار آن ثبت تمام تراکنش ها و تغییرات دیتابیس است. وجود لاگ فایل بسیار حیاتی است چرا که زمان وقوع مشکل، SQL Server از طریق آن می تواند وضعیت دیتابیس را به یک حالت پایدار برساند. به عنوان مثال اگر ما یک کوئری update بر روی یک جدولی که ۱۰۰ میلیون رکورد دارد اجرا کنیم اگر تا رکورد ۹۹ میلیون رفته باشد و سرور ریست شود زمانی که سرور بالا می آید SQL Server از طریق لاگ فایل، جدول مربوطه در دیتابیس را به حالت قبل از اجرای دستور Update بر می گرداند.

با دستور زیر می توان فایل های یک دیتابیس و وضعیت آنها را مشاهده کرد:

USE AdventureWorks2017
GO	
EXEC sp_helpfile

sp_helpfile

پس تا اینجا متوجه شدیم که وجود لاگ فایل برای دیتابیس حیاتی است و در آن تمام تغییرات مربوط به دیتابیس به صورت تراکنشی ثبت می شود.

رشد لاگ فایل به چه دلیل است

اگر تغییرات یک دیتابیس زیاد باشد به طبع رکوردها در لاگ فایل بیشتر و حجم لاگ فایل هم بزرگتر می شود به عنوان مثال در یک دیتابیس OLTP با workload بالا حجم لاگ فایل بسیار سریع رشد می کند.

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

به دو روش می توان رشد لاگ فایل را کنترل نمود:

۱- تغییر ریکاوری مدل به حالت Simple

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

با دستور زیر می توانید وضعیت ریکاوری مدل دیتابیس خود را مشاهده نمایید:

SELECT name , recovery_model_desc 
FROM sys.databases 
WHERE name = 'AdventureWorks2017'

و با دستور زیر می توان ریکاوری مدل خود را به حالت Simple تغییر داد:

ALTER DATABASE AdventureWorks2017 SET RECOVERY SIMPLE

۲- گرفتن Transaction Log Backup به صورت دوره ایی

گرفتن این نوع بکاپ از دیتابیس باعث می شود فضای مورد استفاده تراکنش هایی که تعیین تکلیف شده اند مجددا مورد استفاده قرار بگیرد. کاربرد این نوع بکاپ ها در دیتابیس هایی با حساسیت داده ایی بالا و نیاز به بازگردانی دیتابیس به یک لحظه زمانی خاص در صورت بروز مشکل است. برای گرفتن این نوع بکاپ می بایست حتما ریکاوری مدل دیتابیس Full و یک بار هم فول بکاپ از دیتابیس گرفته شده باشد.

 برای گرفتن این نوع بکاپ می توان از دستور زیر استفاده کرد:

BACKUP LOG AdventureWorks2017 
   TO DISK = 'C:\Backup\AdventureWorks2017.TRN'

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

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

در جواب این سوال باید بگم در شرایطی که حجم تغییرات در یک تراکنش بسیار زیاد باشد ناچارا این فایل رشد می کند به عنوان مثال زمان کانورت داده که حجم زیادی از داده ها تغییر می کنند این فایل به شدت رشد می کند.

چگونه می توان حجم لاگ فایل را کاهش داد

برای کاهش حجم لاگ فایل می توان از دستور dbcc shrinkfile(LogicalFileName,Size_MB) استفاده نمود ولی قبل از انجام این کار با دستور زیر حجم فایل و همچنین میزان فضای استفاده شده در آن را مشاهده نمایید و در صورتی که اختلاف فضای استفاده شده با سایز فایل قابل توجه بود این کار را انجام دهید

USE AdventureWorks2017
GO	
SELECT name , filename , size / 128 Size_MB , FILEPROPERTY(name,'SpaceUsed') / 128 SpaceUsed_MB 
FROM sys.sysfiles 
WHERE right(filename,4) = '.ldf'

File Space Used

همانطور که در عکس بالا مشخص است سایز لاگ فایل دیتابیس حدودا ۴GB است در حالی که فقط ۴۰MB از آن استفاده شده است پس می توان حجم آن را با دستور زیر به سایز کوچکتری(در مثال ما ۶۴MB) تبدیل کرد:

DBCC SHRINKFILE('AdventureWorks2017_log',64)

سوال: آیا می توان حجم لاگ فایل دیتابیس هایی که در Always On Availability Group هستند را بدون اینکه زنجیره لاگ بکاپ ها از بین بروند کاهش داد؟

جواب این سوال مثبت است و می توان با انجام مراحل زیر این کار را انجام داد:

  1. Use DatabaseName
  2. گرفتن لاگ بکاپ از دیتابیس مورد نظر
  3. اجرای دستور DBCC SHRINKFILE(Database_LogFile,EMPTYFILE)
  4. اجرای دستور DBCC SHRINKFILE(‘Database_LogFile’,SizeMB)
  5. در صورت نرسیدن به سایز مورد نظر تکرار مراحل ۲ تا ۴

در اجرای مراحل بالا به جای DatabaseName نام دیتابیس، Database_LogFile نام لاگ فایل دیتابیس و SizeMB سایز مورد نظر برای لاگ فایل خود را بنویسید.


امیدوارم این آموزش برای خوانندگان گرامی مفید واقع شود. لطفا با ارائه نظرات ، سوالات و پیشنهادات خود به بالا بردن کیفیت مطالب آموزشی و همچنین ایجاد دلگرمی برای ادامه مسیر آموزش به ما کمک نمایید. همچنین می توانید با عضویت در کانال تلگرام IranSQL از جدیدترین مقالات و فیلم های آموزشی با خبر شوید.

اشتراک گذاری با دیگران
  •  
  •  
  •  
  •  
  •