خانه SQL Server چگونه می توان رشد Log File را کنترل نمود؟ SQL Server افزایش سرعت SQL Server نوشته شده توسط: غلامحسین عبادی تاریخ انتشار: ۰۸ خرداد ۱۴۰۱ آخرین بروزرسانی: ۲۵ آبان ۱۴۰۲ زمان مطالعه: 5 دقیقه ۲.۵ (۶) مقدمه در این مقاله می خواهیم شما را با سه تا از چالش هایی که یک 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 می تواند یکی از سیزده حالت زیر باشد: NOTHING CHECKPOINT LOG_BACKUP ACTIVE_BACKUP_OR_RESTORE ACTIVE_TRANSACTION DATABASE_MIRRORING REPLICATION DATABASE_SNAPSHOT_CREATION LOG_SCAN AVAILABILITY_REPLICA OLDEST_PAGE XTP_CHECKPOINT 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 بکاپ بگیریم و هر دو ساعت یکبار ، یک لاگ بکاپ بگیریم. دقت کنید که با توجه به حساسیت داده ها و میزان فضایی که برای ذخیره بکاپ ها در اختیار ما قرار می دهند ، این سناریو ممکن است تغییر کند. چه رتبه ای میدهید؟ میانگین ۲.۵ / ۵. از مجموع ۶ اولین نفر باش دانلود مقاله چگونه می توان رشد Log File را کنترل نمود؟ فرمت PDF 5 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 13 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده غلامحسین عبادی غلامحسین عبادی هستم، سوابق حرفهای من به شرح ذیل است: ۱- ۸ سال سابقه کار به عنوان برنامه نویس در شرکت خودرو سازی سایپا، ۲- ۱۱ سال سابقه کاری در شرکت ایران خودرو به عنوان رئیس برنامههای کاربردی و ۴ سال آخر مسئول دیتابیس، ۳- ۴ سال سابقه DBA در شرکت داده ورزی سداد، ۴- دارای مدرک MCSD مایکروسافت از کشور امارات، ۵- دارای مقالات متعدد در ماهنامه تجارات الکترونیک ۶- بیش از یک سال سابقه DBA در شرکت خدمات رایانهای امید، ۷- مدرسی SQL Server در برخی سازمانهای دولتی ۸- ۱۶ سال سابقه تدریس در دانشگاهها و سازمانهای مختلف ۹- مولف کتابهای متعدد در حوزه برنامه نویسی و SQL Server معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ مهدی جمالی نژاد ۲۸ / ۰۳ / ۰۳ - ۱۱:۱۰ متن زیر قسمتی از مقاله است: ” در وحله اول باید خونسردی خود را حفظ کرده و سپس باید یک فایل لاگ در یک درایو دیگر بسازد، در این صورت به طور اتوماتیک اسکیوال سرور بقیه تراکنش ها را در فایل لاگ دوم ایجاد می کند. و نهایتا بعد از اینکه تا حدودی مشکل برطرف گردید و شرایط عادی شد شما باید فایل های لاگ را خالی کرده و فایل لاگ دوم را پاک کنید. ” در این متن یک جمله آمده است: “و شرایط عادی شد شما باید فایل های لاگ را خالی کرده و فایل لاگ دوم را پاک کنید” در جمله فوق، جملهی “… شما باید فایل های لاگ را خالی کرده …” یعنی چی؟ میشه توضیح بدید لطفاً، خیلی واسم حیاتیه، مرسی پاسخ به دیدگاه فری ۲۰ / ۱۰ / ۰۲ - ۰۶:۳۸ سلام و عرض ادب اگر هارد دیسک ما فضای خالی داشته باشه و کمبود فضا نداشته باشیم باز نیاز به کم کردن حجم فایل لاگ هست؟ من سرورم هر ۳ ساعت یکبار بکاپ فول میگیره ایا نیاز هست حجم فایل لاگ را کم کنم؟ پاسخ به دیدگاه Hosein ۳۰ / ۱۱ / ۰۱ - ۰۹:۰۴ با سلام و خسته نباشید مقاله خوبی است اما به نظر کمی مشکل دارد در حال Recovery Model Simple امکان Backup Log اصلا وجود ندارد لطفا اصلاح بفرمایید. پاسخ به دیدگاه غلامحسین عبادی ۰۴ / ۰۴ / ۰۱ - ۰۱:۵۰ سلام دوست عزیز : شما ممکن است در آن لحظه 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 امکان گرفتن لاگ بک آپ وجود ندارد پاسخ به دیدگاه سید جلال علمی ۰۸ / ۰۳ / ۰۱ - ۰۱:۲۵ بسیار عالی بود پاسخ به دیدگاه