مدیریت صحیح حجم لاگ فایل در SQL SERVER - مقدمه

در آموزش shrink لاگ فایل و نجات دیتابیس به ارائه دو راه حل جهت کاهش حجم سریع لاگ فایل در شرایط اضطراری پرداختیم. در آموزش مدیریت صحیح حجم لاگ فایل هدف این است که یاد بگیریم بعد از کنترل شرایط بحرانی و برگرداندن وضعیت دیتابیس به حالت عملیاتی، یک برنامه ریزی مناسب جهت مدیریت صحیح حجم لاگ فایل داشته باشیم. البته بهتر است قبل از اینکه رخ دادن یک وضعیت بحرانی ما را مجبور به یک چنین برنامه ریزی و مدیریت بکند از همان ابتدای عملیاتی شدن یک دیتابیس مدیریت صحیح حجم لاگ فایل را اعمال کنیم(اگر چه معمولاً اینگونه نیست!).

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

---

مدیریت صحیح حجم لاگ فایل در SQL SERVER - انواع روش ها

  8 روشی که معمولاً توسط افراد مشغول در حوزه دیتابیس برای مدیریت حجم لاگ فایل استفاده می شود عبارتند از:

     1. تهیه بک آپ از لاگ فایل با یک دوره زمانی منظم

     2. تهیه بک آپ از لاگ فایل همراه با گزینه NO_LOG با یک دوره زمانی منظم

     3. تهیه بک آپ از لاگ فایل همراه با گزینه NO_LOG تنها در زمان پر شدن آن

     4. تنظیم Recovery model دیتابیس به simple برای همیشه

     5. در صورت پرشدن لاگ فایل، تغییر recovery model دیتابیس به simple و بازگشت آن به full

     6. در صورت پرشدن لاگ فایل، تغییر recovery model دیتابیس به simple و shrink کردن لاگ و بازگشت آن به full

     7. دان کردن سرور دیتابیس، حذف لاگ فایل و مجدداً استارت سرور

     8. ایجاد لاگ فایل های بیشتر برای دیتابیس

در ادامه توضیح مختصری در مورد هر یک از روشهای اشاره شده در بالا ارائه می کنیم:

1. مدیریت صحیح حجم لاگ فایل - تهیه بک آپ از لاگ فایل با یک دوره زمانی منظم

 زمانی که recovery model یک دیتابیس در حالت full تنظیم شده باشد با تهیه اولین بک آپ(full - differential) از دیتابیس، sql server اینگونه تصور می کند که شما به منظور مدیریت حجم لاگ فایل به طور منظم از لاگ فایل دیتابیس بک آپ تهیه می کنید. اگر در سازمان شما بازیابی اطلاعات از اهمیت خاصی برخوردار است و یا از تکنولوژهایی مانند alwayson یا database mirroring جهت دسترسی پذیری بالا(HA) و یا بازیابی خرابی در رویدادهای خاص(DR) استفاده می کنید، ملزم به تنظیم recovery model در حالت full هستید و در این حالت تنها عملیاتی که منجر به ایجاد امکان استفاده مجدد از لاگ فایل می شود تهیه بک آپ منظم از لاگ فایل است در غیر اینصورت بعد از مدتی، با افزایش حجم لاگ، کاهش کارایی و مشکلات مربوط به آن مواجه خواهید شد.

نکته: با ایجاد یک دیتابیس در sql server، بطور پیش فرض recovery model آن به حالت full تنظیم می شود.

2. مدیریت صحیح حجم لاگ فایل - تهیه بک آپ از لاگ فایل همراه با گزینه NO_LOG با یک دوره زمانی منظم

همانطور که در بالا اشاره شد تهیه بک آپ از لاگ فایل، باعث ایجاد امکان استفاده مجدد از فضای آن می شود(truncation). ممکن است به دلایلی مانند نداشتن فضای کافی جهت نگهداری بک آپِ لاگ فایل و ... در جستجوی راه حلی باشید که بدون تهیه بک آپ از لاگ فایل، امکان استفاده مجدد از فضای آن را فراهم کنید. این مورد با استفاده از گزینه NO_LOG در دستور بک آپ قابل انجام است:

BACKUP LOG [نام دیتابیس] WITH NO_LOG

نکته: از نسخه sql server 2008 گزینه NO_LOG از دستور Backup حذف شده است.

در ادامه لازم است به این نکته اشاره کنم که اگر شما recovery model دیتابیس را در حالت full تنظیم کرده اید به این معنا است که قابلیت بازیابی اطلاعات و نداشتن Data Lost برای شما و یا سازمانتان از اهمیت برخوردار است در نتیجه می بایست بصورت منظم از لاگ فایل، بک آپ تهیه کنید. اگر با مشکل کمبود فضای ذخیره سازی مواجه هستید باید از مدیریت سازمان تقاضای افزایش دیسک کنید و یا اگر مرتباً فضای ذخیره سازی، پر می شود باید با فاصله زمانی کمتری اقدام به تهیه بک آپ از لاگ فایل کنید. در غیر اینصورت از simple recovery model استفاده کنید.

نکته: جایگزین NO_LOG از نسخه sql server 2008 و بعد از آن، سوئیچ به حالت simple recovery model و اجرای دستور checkpoint است.

3. مدیریت صحیح حجم لاگ فایل - تهیه بک آپ از لاگ فایل همراه با گزینه NO_LOG تنها در زمان پر شدن آن

مانند مورد بالا است. با این تفاوت که به جای تهیه منظم بک آپ با گزینه NO_LOG از لاگ فایل، تنها در زمان مواجه شدن با کمبود فضای دیسک این کار ا انجام دهیم. دقت کنید که انجام چنین کاری دارای دو ریسک است: 1. ممکن است در این زمان ، لاگ فایل شما با مشکل مواجه شده باشد و امکان تهیه بک آپ را نداشته باشید. 2. با توجه به اینکه لاگ فایل سابقه تغییرات از آخرین full backup تهیه شده را نگهداری می کند، با زیاد شدن حجم آن در چینین شرایطی، زمان بیشتری جهت تهیه بک آپ نیاز است.

4. مدیریت صحیح حجم لاگ فایل - تنظیم Recovery model دیتابیس به simple برای همیشه

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

5. مدیریت صحیح حجم لاگ فایل - در صورت پرشدن لاگ فایل، تغییر recovery model دیتابیس به simple و بازگشت آن به full

با توجه به اینکه روش های اشاره شده در موردهای 2 و 3، در نسخه 2008 از sql server و بعد از آن اجرایی نیست، تغییر recovery model دیتابیس به simple و بازگشت آن به full جایگزینی برای آن محسوب می شود.

6. مدیریت صحیح حجم لاگ فایل - تغییر recovery model دیتابیس به simple و shrink کردن لاگ و بازگشت آن به full در صورت پرشدن لاگ فایل

 این مورد با جزئیات کامل در آموزش سریعترین روش shrink لاگ فایل توضیح داده شده است. یک نکته مهم در این حالت آنست که در زمان shrink کردن لاگ فایل به جای کاهش حجم آن به نزدیکی صفر(استفاده از گزینه TRUNCATEONLY)، بهتر است حجم لاگ فایل را به حداقل اندازه مورد نیاز دیتابیس جهت نگهداری سابقه تغییرات کاهش دهیم. دقت داشته باشید اگر حجم دیتابیس را به صفر، کاهش دهید در مدت زمان کوتاهی مجدداً با افزایش حجم لاگ فایل مواجه می شوید. 

7. مدیریت صحیح حجم لاگ فایل - دان کردن سرور دیتابیس، حذف لاگ فایل و مجدداً استارت سرور

 این روش یکی از بدترین راه حل های ممکن جهت مدیریت حجم لاگ فایل است که بعضاً توسط تعداد محدودی از ادمین های دیتابیس استفاده می شود! اولاً دان کردن سرور منجر به قطع سرویس دهی برنامه های کاربردی می شود که در همه سازمان ها و یا برای همه سیستم ها این امکان وجود ندارد. ثانیاً اگر سرور به درستی دان نشود و دیتابیس در اواسط انجام یک تراکنش باشد، بعد از استارت کردن سرور، دیتابیس مربوطه با حالت suspect اجرا می شود و تنها با emergency mode و احتمال از دست دادن دیتا می توان آن را نجات داد ثالثاً یک نکته ظریف در مورد لاگ فایل این است که امکان instant file initialization بر روی آن بدون تأثیر است و مدت زمانی جهت ایجاد آن لازم است. علاوه بر موارد اشاره شده، با دان کردن سرور و حذف لاگ فایل، شما عملاً لاگ فایل و قابلیت بازگشت اطلاعات به زمان خاص را نیز از دست خواهید داد. پس هرگز از این روش استفاده نکنید.

8. مدیریت صحیح حجم لاگ فایل - ایجاد لاگ فایل های بیشتر برای دیتابیس

اولین نکته در مورد این روش آنست که داشتن تعداد فایل های بیشتر برای لاگ فایل هیچگونه تأثیری بر کارایی ندارد و این تصور که sql server بصورت موازی بر روی این فایل ها لاگ می کند و این کار منجر به افزایش کارایی خواهد شد تنها یک اشتباه است. دومین نکته این است که از نقطه نظر مدیریت حجم لاگ فایل نیز این روش یک راه حل مناسب نمی باشد اما در شرایطی که به دلیل انجام عملیات غیر روتین(انجام عملیات سنگین، rebuild آنلاین ایندکس ها و ... ) حجم لاگ فایل زیاد شده است، اضافه کردن فایل جدید برای لاگ فایل می تواند یک راه حل جهت ادامه کار دیتابیس باشد. اگر در چنین شرایطی، فایل جدیدی به لاگ فایل اضافه کردید، حتماً بعد از اصلاح حجم لاگ فایل، اقدام به حذف فایل های اضافی کنید.

---

مدیریت صحیح حجم لاگ فایل در SQL SERVER - جمع بندی

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

1. ایجاد امکان بازگشت به یک نقطه زمانی خاص از اطلاعات در دیتابیس(point in time recovery)

2. بهبود بخشیدن استراتژی disaster recovery plan یک سازمان. به عبارتی دیگر استفاده از بک آپ لاگ فایل به عنوان یک ابزار کمکی ثانویه در کنار تکنولوژی هایی مانند database mirroring و یا alwayson و ... در تأمین disaster recovery یا DR

اگر شما هیچ یک از موارد 1 و 2 را لازم ندارید بنابراین recovery model دیتابیس خود را در حالت simple قرار دهید و با این کار دیگر نگرانی مواجه شدن با مشکلاتی مانند کاهش کارایی، خطر دان شدن سرو، کاهش حجم دیسک و ...که به واسطه افزایش حجم لاگ فایل ایجاد می شوند را نخواهید داشت. در غیر اینصورت، متناسب با حجم دیتابیس، میزان تراکنش ها(transaction per second)، حساسیت اطلاعات و ... یک پلن مناسب جهت تهیه بک آپ از لاگ فایل داشته باشید.

  

• قبلی
• بعدی