نظارت و مدیریت رشد ورود به سیستم SQL Server Server

در مقالات قبلی این سری در گزارش معاملات SQL Server ، ما با اطمینان از حفظ داده های معاملات تعهد و معامله ناموفق ، در مورد اهمیت ورود به سیستم معاملات سرور SQL و نقشی که در حفظ قوام بانک اطلاعاتی ایفا می کند ، بحث کردیم. به عقب چرخیده می شودهمچنین در صورت خرابی سیستم یا سخت افزار به بازیابی بانک اطلاعاتی به یک نقطه خاص از زمان کمک می کند. این امر با نوشتن یک رکورد ورود به سیستم ثبت نام SQL قبل از نوشتن صفحات داده به پرونده داده های فیزیکی ، با استفاده از فرآیند ورود به سیستم نوشتن ، حاصل می شود.

علاوه بر این ، ما عمیقاً در مورد سه مدل بازیابی کامل ، ساده و فله از پایگاه داده بحث کردیم ، که نحوه ورود و ذخیره این معاملات در پرونده ورود به سیستم معاملات SQL Server را مشخص می کند. در این مقاله خواهیم دید که چگونه نظارت بر رشد ورود به سیستم SQL Server Server و نحوه مدیریت آن رشد را کنترل کنیم.

رشد ورود به سیستم معاملات SQL

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

قبل از گذراندن مکانیسم هایی که می تواند برای نظارت و مدیریت رشد ورود به سیستم معاملات SQL Server استفاده شود ، اجازه دهید در مورد عملیات بیش از حد ورود به سیستم که ممکن است پرونده ورود به سیستم معاملات SQL را به سرعت رشد کند و عملیاتی که معامله را قفل می کنند و از استفاده مجدد آن جلوگیری می کنند ، بحث کنیم. توسط سایر معاملات

فهرست مجدد و سازماندهی مجدد

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

مورد در شاخص سازماندهی مجدد عملکرد یکسان نیست. این فهرست سازماندهی مجدد ، بدون در نظر گرفتن تنظیم مدل بازیابی پایگاه داده ، همیشه کاملاً وارد شده است. شما می توانید فضای ورود به سیستم معاملات SQL را که برای سازماندهی مجدد یک شاخص بزرگ که به شدت تکه تکه شده است ، تصور کنید. در همه موارد ، سازماندهی مجدد یک شاخص با اندازه خاص به فضای ورود به سیستم کمتری نسبت به بازسازی همان شاخص نیاز دارد.

تهیه نسخه پشتیبان از ورود به سیستم SQL

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

معاملات فعال

معامله SQL Server یک واحد واحد از کار است که شامل چندین کار مرتبط است که باید به عنوان یک واحد اتمی موفق یا موفق شوند. یک معامله طولانی مدت یا غیرقابل قبول ، مانند بایگانی و تصفیه معاملات ، کوتاه شدن VLF هایی را که حاوی سوابق ورود به سیستم مربوط به آن معامله فعال است ، حتی اگر مدل بازیابی بانک اطلاعاتی ساده باشد ، به دلیل این واقعیت که VLF خواهد بود ، تأخیر می کند. اگر حاوی رکورد ورود به سیستم فعال باشد ، کوتاه نشوید.

برای غلبه بر مسئله معامله غیرقابل قبول ، حتماً معاملات کاملی را که در صورت موفقیت یا عدم موفقیت انجام می شود ، بنویسید و بر معاملات توزیع شده یتیم که بدون انجام مرتکب شده است نظارت کنید. از طرف دیگر ، شما می توانید با تقسیم معامله طولانی در معاملات کوچکتر که در دسته ها کار می کنند ، مسئله معاملات طولانی مدت را غلبه کنید. به عنوان مثال ، می توانید عبارت Delete را درون یک حلقه قرار دهید ، که جستجوی داده های قدیمی را ادامه داده و آن را در دسته ها حذف می کند.

تکثیر سرور SQL و تغییر ضبط داده ها

SQL Server Replication یک سطح پایگاه داده است ، راه حل در دسترس بودن بالا از یک سرور ناشر تشکیل شده است که لیستی از جداول پایگاه داده را به یک یا چند سرور مشترک توزیع می کند ، که برای عملیات گزارشگری فقط خواندنی استفاده می شود. از طرف دیگر ، CHANGE DATA CAPTURE یک ویژگی SQL Server است که برای ردیابی و ضبط تغییرات DML که در جداول پایگاه داده SQL Server انجام می شود ، استفاده می شود.

آنچه بین این دو ویژگی رایج است این است که هر دو ویژگی به نماینده Log Reader بستگی دارد تا قسمت فعال گزارش معاملات SQL Server را که با تغییراتی که باید تکرار شود ، بخواند. این سوابق ورود به سیستم معاملات SQL در حالت تکثیر در انتظار خواهد بود تا زمانی که این تغییرات ردیابی شده به پایگاه داده مشترک ، در صورت تکثیر ، یا در مورد جداول ردیابی در مورد CDC تکرار شوند.

اگر پایگاه داده مشترکین قابل دسترسی نباشد ، شغل SQL Server Agent در حالت توقف قرار دارد ، مشاغل عامل مرتبط با فرآیند تکثیر کار نمی کند یا به سادگی فعالیت های خواننده عامل ورود به سیستم آهسته است ، تمام سوابق ورود به سیستم معاملات SQL Server که هنوز تکرار نشده اندبخشی از ورود به سیستم فعال در حالت تکثیر در انتظار خواهد ماند. باز هم ، داشتن این سوابق ورود به سیستم در یک حالت فعال برای مدت طولانی مانع از کوتاه شدن VLF هایی که حاوی این سوابق ورود به سیستم هستند ، باعث می شود پرونده ورود به سیستم معامله به طور مداوم رشد کند تا اینکه از فضای آزاد خارج شود.

می توانید با اطمینان از اینکه سرویس عامل SQL Server Agent در حال اجرا است ، این مسئله را عیب یابی کنید ، مشاغل عامل مرتبط با تکثیر و CDC در حال اجرا هستند و در آخر اینکه سرور مشترک قابل دسترسی است.

آینه سازی پایگاه داده و همیشه در گروه در دسترس بودن

آینه سازی پایگاه داده SQL Server یک راه حل در دسترس بودن سطح پایگاه داده است ، که از یک سرور اصلی تشکیل شده است که سوابق ورود به معاملات فعال را به پایگاه داده آینه در سرور Mirror ارسال می کند و سپس این تغییرات را در پایگاه داده آینه تغییر می دهد. از طرف دیگر ، سرور SQL همیشه در ویژگی گروه در دسترس بودن ، یک راه حل در دسترس بودن سطح پایگاه داده است ، که از یک سرور اصلی تشکیل شده است ، که سوابق ورود به سیستم معامله را از پایگاه داده های اصلی به پایگاه داده ثانویه ارسال می کند تا در پایگاه داده نوشته شودپرونده ورود به سیستم برای اولین بار برای ذخیره سازی ، و تغییرات بعداً در پایگاه داده منعکس می شود.

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

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

برای غلبه بر این مسئله ، اطمینان حاصل کنید که مشکل اتصال بین ماکت ها را عیب یابی کرده و رفع می کنید ، یا مسئله ای که باعث شده است که آینه کاری یا سایتهای گروه در دسترس باشد در اسرع وقت شکست بخورند ، تا تعداد زیادی از سوابق در انتظار نباشندسایت اصلی

پشتیبان گیری طولانی مدت

هنگامی که یک پایگاه داده کامل یا عملکرد پشتیبان گیری دیفرانسیل در حال اجرا است ، کوتاه شدن ورود به سیستم معاملات SQL Server به تأخیر می افتد ، تا بخش فعال ورود به سیستم در آن نسخه پشتیبان تهیه شود. اگر این عملیات پشتیبان گیری برای مدت طولانی در حال اجرا باشد ، کوتاه شدن ورود به سیستم معاملات SQL برای مدت زمان طولانی به تأخیر می افتد و پرونده ورود به سیستم معاملات SQL به دلیل عدم امکان استفاده مجدد از قسمت غیرفعال ورود به سیستم ، رشد می کند.

برای غلبه بر این رشد ورود به سیستم ، باید علت اصلی اجرای عملکرد ورود به سیستم را برای مدت طولانی مانند فرآیند مسدود کردن داخلی یا یک مشکل عملکرد در سیستم I/O زیربنایی عیب یابی کنید.

عیب یابی رشد ورود به سیستم

هنگامی که پرونده ورود به سیستم معاملات SQL Server از پایگاه داده از فضای آزاد خارج می شود ، ابتدا باید تنظیمات اندازه پرونده ورود به سیستم معامله را تأیید کنید و بررسی کنید که آیا امکان افزایش اندازه پرونده ورود به سیستم وجود دارد یا خیر. اگر قادر به گسترش اندازه پرونده ورود به سیستم نیستید و مدل بازیابی پایگاه داده پر است ، می توانید با تغییر آن به مدل بازیابی ساده ، ورود به سیستم را مجبور کنید. اگر مدل بازیابی پایگاه داده در حال حاضر ساده است یا آن را به مدل بازیابی ساده تغییر نمی دهد ، باید مشخص کنید که چه چیزی مانع از کوتاه شدن ورود به معامله SQL Server می شود.

از نمای کاتالوگ سیستم Sys. Databases می توان برای بازیابی اطلاعات مفید در مورد دلیل پیشگیری از کوتاه شدن پرونده ورود به معاملات SQL ، تحت ستون LOG_REUSE_WAIT_DESC ، مانند پرس و جو زیر استفاده کرد:

نتیجه برگشتی از پرس و جو قبلی مانند موارد زیر خواهد بود:

مقدار ستون log_reuse_wait_desc که نشان دهنده یک حالت سالم برای پرونده ورود به سیستم معاملات پایگاه داده است ، چیزی نیست ، به این معنی که ورود به سیستم معامله SQL کوتاه و قابل استفاده مجدد است. مقادیر دیگری که ممکن است از sys. databases در ستون log_reuse_wait_desc بازگردد ، که نشان می دهد چه چیزی مانع از کوتاه شدن گزارش معاملات SQL می شود ، شامل موارد زیر است:

• محل بازرسی
• log_backup
• Active_backup_or_restore
• active_transaction
• database_miroring
• تکرار
• database_snapshot_creation
• log_scan
• در دسترس بودن_پلیکا

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

نظارت بر پرونده ورود به سیستم تراکنش سرور SQL

همیشه بهتر است که یک مدیر پایگاه داده فعال باشید و به رشد پرونده ورود به سیستم معاملات SQL Server توجه داشته باشید تا از مشکلات فاجعه آمیز در هنگام اتمام پرونده ورود به سیستم برای مدت طولانی جلوگیری شود. به جای اینکه در کنار سرور بخوابید ، می توانید از یک ابزار نظارتی مانند ابزار System Center Operations Manager (SCOM) ، شمارنده های مانیتور عملکرد استفاده کنید ، یا به سادگی هشدار ایجاد کنید که از یکی از نماهای کاتالوگ سیستم بخواند و هنگامی که از طریق ایمیل به یک اپراتور اطلاع می دهدفضای آزاد پرونده ورود به سیستم معاملات SQL تحت آستانه از پیش تعریف شده قرار می گیرد.

SYS. DM_DB_LOG_SPACE_USAGE یک نمای مدیریت پویا است که در SQL Server 2012 معرفی شده است ، که برای بازگشت اطلاعات استفاده از فضا برای ورود به سیستم استفاده می شود. از پرس و جو زیر می توان برای بررسی درصد فضای آزاد در پرونده ورود به سیستم معاملات SQL از پایگاه داده فعلی استفاده کرد:

و نتیجه مانند:

اگر نتیجه بازگشت از پرس و جو قبلی قبل از اجرای پرونده ورود به سیستم معاملات SQL Server از فضای آزاد ، یک آستانه از پیش تعریف شده را کاهش دهد ، DBA از طریق یک ایمیل ، پیام کوتاه یا تماس بر اساس ابزار مانیتورینگ مورد استفاده در نهاد شما مطلع می شود.

خلاصه

در مقاله بعدی این سری ، ما در مورد عملیات مختلفی که می توان در گزارش معاملات SQL از جمله عملیات پشت ، کوتاه و کوچک کردن انجام داد ، بحث خواهیم کرد و شناسایی یکی از دیگر خواننده را برای خواننده آسان تر می کند. گوش به زنگ باشید!