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

مقدمه

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

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

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

تذکر : در شرایط نرمال ما نیازی به فایل دوم لاگ فایل نداریم و اگر هم اضافه کنیم بی فایده است. چون دسترسی به لاگ فایل به صورت ترتیبی یا 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 می بینید دیتابیس ما نیاز به لاگ بکاپ دارد.

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

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

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

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

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

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

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