تشخیص قابل shrink بودن لاگ فایل - مقدمه

 در آموزش های معرفی دو روش سریع shrink و مدیریت صحیح حجم لاگ فایل با نحوه کاهش حجم  و روش های کنترل حجم لاگ فایل آشنا شدیم. در این آموزش قصد داریم به نحوه تشخیص قابل shrink بودن لاگ فایل بپردازیم. آیا تا به حال برای شما پیش آمده است که علی رغم اجرای دستور shrinkfile هیچ کاهش حجمی در لاگ فایل دیتابیس ایجاد نشود؟ ممکن است چندین بار پیاپی نیز اقدام به اجرای دستور shrinkfile کرده باشید و همچنان حجم لاگ فایل بدون تغییر باقی بماند! در این آموزش قصد داریم با علت رخ دادن این مشکل و نحوه رفع آن آشنا شویم.

آموزش تشخیص قابل shrink بودن لاگ فایل شامل موارد زیر است:

     1. توضیحی اجمالی و در حد نیاز، پیراموان لاگ فایل، موارد استفاده و ساختار آن 

     2. تفاوت بین truncate با shrink در لاگ فایل 

     3. نحوه shrink شدن لاگ فایل با اجرای دستور shrinkfile

     4. تشخیص قابل shrink بودن لاگ فایل قبل از اجرای دستور shrinkfile

     5. شناسایی و رفع موانع shrink شدن لاگ فایل

---

تشخیص قابل shrink بودن لاگ فایل - آشنایی با Transaction Log File

 هر دیتابیس در sql server حداقل از یک فایل mdf جهت ذخیره اطلاعات و یک فایل ldf جهت نگهداری سابقه عملیات انجام شده بر روی این اطلاعات، تشکیل شده است. یکی از کاربردهای مهم لاگ فایل، ایجاد امکان بازگشت دیتابیس به نقطه زمانی مشخص، در صورت بروز مشکل و یا خرابی اطلاعات است. در واقع قبل از اینکه تغییرات اعمال شده روی اطلاعات(حذف، بروزرسانی و ...) بصورت واقعی روی رکورد/ها و در فایل mdf بر روی دیسک اعمال شود، در ابتدا بایستی سابقه این تغییرات در فایل ldf ثبت شود(با نام write ahead logging شناخته می شود). معمولاً انتقال تغییرات اعمال شده روی رکوردها از حافظه اصلی به دیسک(فایل mdf) با رخ دادن checkpoint انجام می شود(به این انتقال flush می گوییم):

 تصویر بالا، مراحل کلی انجام یک عملیات بروزرسانی(افزایش امتیاز 10 واحدی فردی با فامیل بندامیر) را نمایش می دهد. در ابتدا صفحات(page) حاوی رکوردهایی با فامیل 'بندامیر' از فایل mdf به حافظه اصلی منتقل می شوند(شماره 1: fetch). سپس تغییر مورد نظر ما(افزایش 10 واحدی فیلد score) در حافظه اعمال می شود. در ادامه سابقه این تغییرات در لاگ فایل(ldf) ذخیره می شود(شماره2: wal) پس از اتمام این عملیات و رخ دادن checkpoint، تغییرات موجود در حافظه به فایل mdf منتقل می گردد(شماره 3: flush).

نکته: فایل mdf از قسمت هایی به نام page تشکیل شده و فایل ldf از سگمنت هایی به نام virtual log file یا VLF تشکیل شده است.   

هر لاگ فایل از تعدادی VLF تشکیل شده و دسترسی به یک لاگ فایل همیشه بصورت ترتیبی(sequential) است. بنابراین عملیات نوشتن در لاگ فایل از اولین VLF شروع می شود و با پر شدن آن، عملیات نوشتن در VLF بعدی ادامه پیدا می کند. با پر شدن آخرین VLF، قبل از اینکه حجم لاگ فایل افزایش پیدا کند، از ابتدای آن جهت یافتن VLFی که مورد استفاده نباشد جستجو انجام می شود و در صورت پیدا کردن VLF آزاد، به جای افزایش حجم لاگ فایل، از آن استفاده می شود و در صورت موجود نبودن VLF آزاد، حجم لاگ فایل افزایش پیدا می کند(این افزایش حجم به اندازه Autogrowth تعیین شده در properties دیتابیس است).

به عنوان مثال فرض کنید یک لاگ فایل متشکل از 10 عدد VLF داشته باشیم با پر شدن VLF شماره 10، بررسی می کنیم و می بینیم که مثلاً VLFهای شماره 5،3و7 موزد استفاده نیستند، بنابراین به جای افزایش حجم لاگ فایل، از VLF شماره 3 استفاده می شود.

نکته: VLFهای در حال استفاده را Active/Used و VLFهای آزاد که قابل استفاده مجدد هستند را Inactive/Free می نامیم.

شکل زیر یک لاگ فایل با VLFهایی در وضعیت Active و Inactive را نمایش می دهد:

 همانطور که می دانید عملیاتی که در سطح sql server انجام می دهیم در قالب یک تراکنش انجام می شوند که این تراکنش می تواند به صورت ضمنی(implicit) و یا صریح باشد(explicit) در هر دو صورت، سابقه هر عملیات در قالب یک یا چند رکورد در لاگ فایل و VLF آن ثبت می شود. اگر یک VLF حاوی حداقل یک رکورد متعلق به یک تراکنش فعال(در حال اجرا) باشد، آن VLF در وضعیت Active/Used قرار دارد و نمی تواند مورد استفاده مجدد قرار گیرد.

به عنوان مثال اگر شما یک تراکنش را شروع کنید(begin transaction) تا زمانی که دستور commit و یا rollback اجرا نشود، VLF یا VLFهایی که حاوی رکوردهای متعلق به تراکنش هستند قابل استفاده مجدد نمی باشند و در وضعیت Active/Used قرار دارند. با commit و یا rollback شدن تراکنش، وضعیت VLFها به Free/Inactive تغییر پیدا می کند(در صورتی که در آن VLF، رکورد متعلق به تراکنش فعال دیگری وجود نداشته باشد). در ادامه به معرفی عملیاتی که منجر به باقی ماندن VLF در وضعیت Active می شوند پرداخته می شود.

نکته: تغییر وضعیت VLF از Used/Active به Free/Inactive و قابل استفاده مجدد شدن آن را Truncate می نامیم.

 در عملیات Shrink لاگ فایل هدف اصلی کاهش حجم فضای اختصاص داده شده به لاگ فایل است. در حالیکه Truncation آزادسازی VLFهای یک لاگ فایل و فراهم کردن امکان استفاده مجدد از VLFها، جهت جلوگیری از رشد لاگ فایل در صورت پر شدن آن است و هیچ کاهش حجمی بواسطه آن در لاگ فایل رخ نمی دهد.

با توضیحات ارائه شده تا اینجا، زمان پاسخ دهی به سؤال "دلیل عدم کاهش حجم لاگ فایل پس از اجرای دستور Shrinkfile چیست؟" رسیده است.

برای پاسخ دهی به این سؤال لازم است ابتدا با نحوه Shrink شدن لاگ فایل آشنا شویم. نکته مهم این است که کاهش حجم لاگ فایل از انتهای آن تا رسیدن به اولین VLF با وضعیت Active/Used انجام می شود. بنابراین اگر آخرین VLF متعلق به یک لاگ فایل در حال استفاده باشد، ما هر چند بار هم که دستور shrinkfile را اجرا کنیم، هیچ تغییری در حجم لاگ فایل ایجاد نخواهد شد!

---

تشخیص قابل shrink بودن لاگ فایل - شناسایی و رفع موانع shrink شدن

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

DBCC LOGINFO

 در تصویر بالا، دیتابیس Store از شش VLF تشکیل شده است که فیلد Status وضعیت هر یک از این VLFها را نمایش می دهد. مقدار 0 در فیلد Status به معنای Inactive/free بودن و مقدار 2 به معنای Active/Used بودن آن VLF است(مجموع حجم VLFها به علاوه 8KB فضای Header، برابر با حجم لاگ فایل دیتابیس است). پس در دیتابیس Store در صورت پر شدن لاگ فایل، امکان استفاده مجدد از دو عدد VLF با وضعیت 0 وجود دارد و لاگ فایل رشد نخواهد کرد. اما در صورت اجرای دستور Shrinkfile هیچ کاهش حجمی در لاگ فایل رخ نخواهد داد! زیرا:

نکته: کاهش حجم لاگ فایل از انتهای آن تا رسیدن به اولین VLF با وضعیت Active/Used (دارای مقدار 0 در status)انجام می شود.

و در لاگ فایل دیتابیس Store وضعیت آخرین VLF برابر با 2 است که به  معنای Used و یا در حال استفاده بودن این VLF می باشد. در ادامه لازم است تا دلیل در حال استفاده بودن VLFها را بررسی کنیم. برای این منظور از اسکریپت زیر استفاده می کنینم:

SELECT name, log_reuse_wait_desc FROM sys.databases

 

مقدار فیلد log_reuse-wait_desc دلیل در حال استفاده بودن VLF را توضیح می دهد. همانطور که در شکل بالا مشاهده می کنید برای دیتابیس Store این مقدار برابر با LOG_BACKUP است. در ادامه لیست مهمترین مقادیر موجود در فیلد log_reuse-wait_desc به همراه توضیح مختصری از آن ها ارائه شده است(جهت مشاهده لیست کامل به این لینک مراجعه کنید):

     NOTHING: عدم وجود مشکل در استفاده مجدد از VLF

     LOG_BACKUP: قرار داشتن دیتابیس در حالت full recovery model و عدم تهیه بک آپ از لاگ فایل

     ACTIVE_TRANSACTION: وجود تراکنش فعال (long-running transaction)

     DATABASE_MIRRORING: استفاده از mirroring و انتظار برای درج شدن لاگ فایل در سرور secondary

 بعد از مشخص شدن دلیل مشغول بودن یک VLF، بایستی قبل از تلاش جهت shrink کردن لاگ فایل، وضعیت VLF را از 2 به 0 تغییر دهیم. برای اینکار متناسب با مقدار موجود در فیلد log_reuse-wait_desc اقدامات لازم را انجام می دهیم. به عنوان مثال برای مقدار ACTIVE_TRANSACTION می توانیم تراکنش مربوطه را commit و یا rollback کنیم و یا با استفاده از دستور DBCC OPENTRAN لیست تراکنش های فعال را استخراج و با استفاده از SID مربوط به تراکنش آن را Kill کنیم. در مورد دیتابیس Store با مشاهده مقدار LOG_BACKUP، اقدام به تهیه بک آپ از لاگ فایل این دیتابیس می کنیم که منجر به تغییر مقدار فیلد log_reuse-wait_desc از LOG_BACKUP به NOTHING می شود:

در ادامه با اجرای مجدد دستور DBCC LOGINFO میزان کاهش حجم لاگ فایل در صورت اجرای دستور shrinkfile قابل تشخیص است:

 

---

تشخیص قابل shrink بودن لاگ فایل - جمع بندی

در این آموزش علاوه بر معرفی اجمالی لاگ فایل و ساختار آن، با تفاوت دو مفهوم Truncate و shrink در لاگ فایل آشنا شدیم. پیشنهاد می شود جهت shrink لاگ فایل مطابق مراحل ذکر شده در آموزش تشخیص قابل shrink بودن لاگ فایل پیش بروید. در ابتدا وضعیت لاگ فایل و VLFهای آن را بررسی کنید و در صورت مشغول بودن VLFها، اقدام به رفع مشکل نمایید. دقت کنید تا حد امکان لاگ فایل را به حداقل اندازه مورد نیاز shrink نمایید. به عبارتی دیگر با توجه به شناختی که از حجم تراکنش های دیتابیس و میزان داده های ذخیره شده در آن دارید، به جای استفاده از گزینه TRUNCATEONLY در دستور shrinkfile، از یک میزان حجم مشخص برای لاگ فایل استفاده کنید که می دانید در آینده نیازی به رشد آن نمی باشد.

همانگونه که در آموزش های گذشته نیز اشاره شد در صورتیکه بازیابی اطلاعات برای شما مهم نیست و یا دلیل خاصی مانند استفاده از تکنولوژی های HA، جهت قرار دادن recovery model دیتابیس در حالت full ندارید، recovery model دیتابیس خود را در حالت simple قرار دهید و دیگر نگران افزایش حجم لاگ فایل و مشکلات مرتبط با آن نباشید. 

  

• قبلی
• بعدی