رشد بی رویه لاگ فایل دیتابیس
یکی از مشکلاتی که گاها مشاهده و در مورد آن سوال پرسیده می شود رشد زیاد لاگ فایل (فایل Ldf) یک دیتابیس است که خیلی از افراد برای رفع آن کار خطرناک Detach و Attach کردن دیتابیس را انجام می دهند. با من همراه بشوید که در این مقاله چهار موضوع زیر را بررسی کنیم:
-
لاگ فایل چیست
-
رشد لاگ فایل به چه دلیل است
-
چگونه می توان رشد لاگ فایل را کنترل کرد
-
چگونه می توان حجم لاگ فایل را کاهش داد
لاگ فایل چیست
لاگ فایل یکی از فایل های دیتابیس است که زمان ایجاد دیتابیس ساخته می شود و کار آن ثبت تمام تراکنش ها و تغییرات دیتابیس است. وجود لاگ فایل بسیار حیاتی است چرا که زمان وقوع مشکل، SQL Server از طریق آن می تواند وضعیت دیتابیس را به یک حالت پایدار برساند. به عنوان مثال اگر ما یک کوئری update بر روی یک جدولی که ۱۰۰ میلیون رکورد دارد اجرا کنیم اگر تا رکورد ۹۹ میلیون رفته باشد و سرور ریست شود زمانی که سرور بالا می آید SQL Server از طریق لاگ فایل، جدول مربوطه در دیتابیس را به حالت قبل از اجرای دستور Update بر می گرداند.
با دستور زیر می توان فایل های یک دیتابیس و وضعیت آنها را مشاهده کرد:
USE AdventureWorks2017 GO EXEC 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'
همانطور که در عکس بالا مشخص است سایز لاگ فایل دیتابیس حدودا ۴GB است در حالی که فقط ۴۰MB از آن استفاده شده است پس می توان حجم آن را با دستور زیر به سایز کوچکتری(در مثال ما ۶۴MB) تبدیل کرد:
DBCC SHRINKFILE('AdventureWorks2017_log',64)
سوال: آیا می توان حجم لاگ فایل دیتابیس هایی که در Always On Availability Group هستند را بدون اینکه زنجیره لاگ بکاپ ها از بین بروند کاهش داد؟
جواب این سوال مثبت است و می توان با انجام مراحل زیر این کار را انجام داد:
- Use DatabaseName
- گرفتن لاگ بکاپ از دیتابیس مورد نظر
- اجرای دستور DBCC SHRINKFILE(Database_LogFile,EMPTYFILE)
- اجرای دستور DBCC SHRINKFILE(‘Database_LogFile’,SizeMB)
- در صورت نرسیدن به سایز مورد نظر تکرار مراحل ۲ تا ۴
در اجرای مراحل بالا به جای DatabaseName نام دیتابیس، Database_LogFile نام لاگ فایل دیتابیس و SizeMB سایز مورد نظر برای لاگ فایل خود را بنویسید.
امیدوارم این آموزش برای خوانندگان گرامی مفید واقع شود. لطفا با ارائه نظرات ، سوالات و پیشنهادات خود به بالا بردن کیفیت مطالب آموزشی و همچنین ایجاد دلگرمی برای ادامه مسیر آموزش به ما کمک نمایید. همچنین می توانید با عضویت در کانال تلگرام IranSQL از جدیدترین مقالات و فیلم های آموزشی با خبر شوید.
دیدگاه ها
26 دیدگاه
بسیار مقاله خوب و پر کاربردی بود .
ممنون از حسن نظر شما
ممنون مفید بود مرسی . امکان داره به صورت تصویری وبا ریز نکات موارد رو برای مبتدی ها توضیح بدین ؟
ممنون وقت گذاشتین و مقاله را مطالعه کردین. سعی می شود در آموزش های بعدی از تصاویر بیشتر و تا حد امکان نکات ریزتر استفاده شود
ممنون مهندس
بسیار کاربردی و خلاصه
ممنون از حسن نظر شما
عالی بود ممنون از زحمت شما بابت اشتراک اطلاعات
خواهش می کنم. خوشحالم که مقاله برای شما مفید واقع شده است
بسیار سایت خوبی است امیدوارم که ادامه دار باشد
ممنون از کامنت مثبتتون
سلام مهندس جان ممنون بابت اموزش خوب و روان
mdf فایل من ۸۰ گیگ و ldf من ۲۸۰ هست و از این ۲۸۰ حدود ۲۳۰ گیگ استفاده شده هست ، ریکاوری مدل من فول هست ولی میخوام از یه زمانی بیام لاگ رو کلا ریست کنم و از نو لاگ زده بشه ولی مثل اینکه تو پرفورمنس تاثیر گذاره ، نظر شما چیه/؟
سلام. shrink کردن بیشتر بر روی mdf تاثیر پرفورمنسی میذاره اونم بدلیل بالا رفتن fragmentation جداول، ولی برای ldf تاثیر منفی نداره(البته به شرط اینکه تعداد vlfها زیاد نشه). میتونید ldf خودتون رو shrink کنید ولی حتما یک پلن مناسب برای بکاپ گیری از دیتابیس خودتون تعریف کنید که سایز ldf دیتابیس خیلی رشد نکند
سلام و عرض ادب
من مشکلی دارم روی یکی از فایل های .ndf که حجم این فایل بطور عجیبی افزایش پیدا کرده است. با اینکه اصلا حجم دیتایی در سیستم نیست.
سلام
چک کنید چقدر از فضای فایل مورد نظر Used و چقدر Unused است. اگر فضای Used قابل توجه بود، حجم جداول بر روی فایل گروپ مربوط به فایل مورد نظر را بررسی کنید که متوجه شید چه چیزی باعث رشد فایل شده است
سلام مهندس
یک دیتابیس دارم که حجم بک آپ آون بعد از compress کردن حدود ۲۰۰ مگ بود الان دستورات certificate را روی دیتابیس اجرا کردم تا امنیت اون را بالا ببرم
الان حجم backup ها چه با compress چه بدون اون به ۵ گیگ افزایش یافته که دقیقا حجم mdf هست.
ورژن sql هم ۲۰۱۴ هست
لطفا راهنمایی بفرمایید.
سلام. Sql Server رو آپگرید کنید به ورژن های بالاتر مشکل برطرف میشه.
لینک زیر میتونه کمکتون کنه:
https://www.mssqltips.com/sqlservertip/4512/backup-compression-for-sql-server-tde-enabled-databases/
سلام برشما
عالی
مختصر
و مفید
دستتون در نکنه که دانش و تجربه خودتون رو در اختیار همگان میگذارید
درود
سلام. ممنون بابت کامنت مثبتتون. خوشحالم مقاله برای شما مفید بوده
سلام. ممنون
وضعیت دیتابیس من این هست، چطوری میتونم حجمش رو کم کنم ؟
name filename Size_MB SpaceUsed_MB
tangesir_log C:\Program Files (x86)\Plesk\Databases\MSSQL\MSSQL14.MSSQLSERVER2017\MSSQL\DATA\tangesir_62d2c0eed4354d63950c28ac8372e6b4.ldf ۹۸۶۴ ۷۴۳۸
سلام. خواهش می کنم. مطابق مقاله عمل کنید و حجم فایلتون رو کم کنید
خیلی کمکم کرد. مممنون
خدا رو شکر. خواهش می کنم ?
سلام مهندس رومانی . ممنون از مقاله خوبتون
مهندس چرا مقالات جدید نمیزارید ؟
سلام. خواهش می کنم. به زودی مقاله جدید در سایت قرار داده میشه
عالی بود مهندس
خوشحالم که این مطلب براتون مفیده بوده
ارسال دیدگاه