نحوه مدیریت‌ کردن لاگ‌ها در SQL Server

نحوه مدیریت‌کردن لاگ‌ها در SQL Server

گزارش‌ها بهترین منبع برای عیب‌یابی هرگونه مشکل توسط مدیر پایگاه‌داده هستند. این مشکلات ممکن است مربوط به پیکربندی سرور، راه‌اندازی، بازیابی، عملکرد، پرچم‌های ردیابی، بن‌بست‌ها، IO یا تاخیر در Checkpoint باشد. به عنوان مثال، فرض کنید SQL Server شما به دلایل نامعلومی راه اندازی مجدد شده است و پس از راه‌اندازی، سرویس‌های SQL فعال می‌شوند. با این حال، برنامه شما نمی تواند به پایگاه داده دسترسی پیدا کند. بنابراین، برای بررسی مسائل، می‌توانید به آخرین گزارش‌های SQL Server نگاهی بیندازید و روند بازیابی پایگاه داده و زمان تخمینی تکمیل را نظارت کنید.

مدیر پایگاه ‌داده می‌تواند SQL Server را برای ورود بیشتر به گزارش‌های خطا پیکربندی کند. به ‌عنوان‌ مثال، ما می‌توانیم یک پرچم ردیابی را برای گرفتن اطلاعات بن‌بست‌ها فعال کنیم. مدیر پایگاه‌داده (DBA) باید این گزارش‌ها را به طور فعال بررسی کند، تا مشکلات احتمالی را پیدا کند. با بررسی لاگ‌ها می‌توانید اطلاعاتی مانند خرابی پشتیبان‌گیری، خرابی ورود، خطاهای IO را شناسایی کنید. این گزارش‌های خطا برای جستجوی مشکلاتی که وجود دارند یا ممکن است به وجود بیایند، عالی هستند.

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

مکان گزارش‌های SQL Server

پس از وارد شدن به SQL Server در SSMS، به Management رفته و سپس به SQL Server Logs بروید. همانطور که در زیر نشان داده شده است، این بخش دارای گزارش فعلی و شش گزارش بایگانی است (بایگانی شماره ۱ تا بایگانی شماره ۶)

روش ۱: با استفاده از Procedure توسعه ‌یافته xp_readerrorlog

گزارش‌های فعلی آخرین فایل گزارش خطا هستند و می‌توانید از آنها برای مشاهده فعالیت‌های اخیر از زمان شروع SQL Server یا استفاده مجدد از فایل لاگ استفاده کنید. گزارش خطای SQL Server یک فایل متنی است که در فهرست ورود به سیستم SQL Server ذخیره می شود. می‌توانید از procedure توسعه یافته xp_readerrorlog برای پیدا کردن مکان فعلی گزارش خطا استفاده کنید.

USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages', NULL, NULL,NULL
GO

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

1. فایل گزارش خطا: مقدار ۰ برای فعلی، ۱ برای بایگانی شماره ۱، ۲ برای بایگانی شماره ۲.
2. نوع فایل لاگ: مقدار ۰ برای گزارش خطای SQL Server، ۱ برای گزارش SQL Server Agent.
3. جستجوی رشته ۱
4. جستجوی رشته ۲
5. از زمان
6. زمان پایان
7. مرتب‌سازی نتایج – صعودی (N’ASC) یا نزولی (N’Desc).

محل ذخیره‌سازی فایل لاگ خطا (Log File) برای نمونه آزمایشی من،  C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG است.

روش ۲: استفاده از تابع SERVERPROPERTY

می‌توانیم تابع SERVERPROPERTY را برای شناسایی مکان SQL Server ERRORLOG استفاده کنیم.

SELECT SERVERPROPERTY('ErrorLogFileName')  AS 'Error log location'

روش ۳: با استفاده از SQL Server Configuration Manager

SQL Server Configuration Manager را باز کنید و پارامترهای راه‌اندازی شده را مشاهده می‌کنید. محل فایل log با استفاده از سوئیچ -e مشخص می‌شود.
شما می‌توانید دایرکتوری گزارش را مرور کنید و فایل‌های گزارش خطای آرشیو فعلی را مشاهده کنید. این گزارش‌های خطا را می‌توان در یک ویرایشگر متن مانند Notepad یا Visual Studio Code باز کرد.

پیکربندی تعداد فایل‌های SQL Server Log و اندازه آنها

به طور پیش‌فرض، SQL Server یک فایل گزارش فعلی و شش فایل بایگانی شده را نگهداری می‌کند. برای تعیین مقدار، بر روی پوشه SQL Server Logs در SSMS کلیک راست کرده و  Configure را انتخاب کنید.SQL Server تمام اطلاعات موجود در فایل لاگ فعلی را بدون توجه به‌اندازه فایل لاگ ثبت می‌کند. در یک سیستم شلوغ یا نمونه‌ای با خطاهای زیاد، ممکن است مشاهده فایل گزارش در SSMS برای شما دشوار باشد. در حالت‌های زیر SQL Server یک فایل log جدید ایجاد می‌کند و فایل فعلی را در حالت زیر بایگانی می‌کند.

• در حین راه‌اندازی مجدد سرویس SQL
• ثبت خطای دستی

با این ‌حال، اگر به دلایل ناشناخته مرتباً سرورها را راه‌اندازی مجدد می‌کنید، ممکن است تمام داده‌های گزارش بایگانی تاریخی را از دست بدهید، زیرا تنها شش گزارش بایگانی را نگهداری می‌کند. ازآنجایی‌که خطا حاوی اطلاعات ارزشمندی است که می‌تواند به شما در عیب‌یابی کمک کند، ممکن است نخواهیم این داده‌های مهم را از دست دهیم. در عوض، ممکن است بخواهیم فایل‌های گزارش را در یک سیستم مهم برای یک هفته یا حتی یک ماه حفظ کنیم.
SQL Server اجازه می دهد  از ۶ تا ۹۹ فایل ثبت خطا را پیکربندی کنید. ما نمی‌توانیم مقدار کمتر از شش را تعیین کنیم، زیرا در هر صورت، شش گزارش خطای بایگانی را نگهداری می‌کند.
برای تغییر تعداد پیش‌فرض فایل‌های گزارش خطا، روی کادر با عنوان “Limit the number of error log files before they are recycled” کلیک کنید. به‌عنوان‌مثال، تصویر زیر حداکثر ۳۰ فایل ثبت خطا را نشان می‌دهداسکریپت T-SQL معادل آن از procedure توسعه‌یافته xp_instance_regwrite استفاده و مقدار رجیستری را به‌روز می‌کند.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30
GO

توجه: سرویس SQL باید دوباره راه‌اندازی شود تا این تغییرات اعمال شوند.
همان‌طور که قبلاً گفته شد، به طور پیش‌فرض، اندازه ثبت خطا نامحدود است. به‌عنوان‌مثال، اگر SQL Server را برای مدت طولانی‌تری راه‌اندازی نکنید و فایل‌های لاگ را به‌صورت دستی بازیافت نکنید، این فایل بزرگ خواهد شد. در پیکربندی گزارش خطا، مقدار ۰ نشان می‌دهد که اندازه گزارش نامحدود است.می‌توانید اندازه را در حد کیلوبایت تعیین کنید و اندازه گزارش خطا را بر اساس نیاز خود محدود کنید. به‌عنوان‌مثال، در زیر، اندازه فایل لاگ را به ۱ گیگابایت محدود می‌کنیم.
اسکریپت T-SQL معادل آن ErrorLogSizeInKb را در رجیستری SQL Server به روز می کند.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 1048576
GO

گزارش های خطا بازیافت دستی

پایگاه‌ داده SQL Server  اجازه می دهد تا لاگ‌های خطا به‌صورت دستی مدیریت شوند. به‌عنوان ‌مثال، فرض کنید تعداد فایل‌های ثبت خطا را به ۳۰ افزایش داده‌اید؛ بنابراین، ما می‌توانیم یک کار (Job) در SQL Server ایجاد کنیم که گزارش های خطا را در نیمه ‌شب بازیافت کند. به‌این‌ترتیب، ما یک فایل ثبت خطا برای هر روز داریم، مگر اینکه سرویس SQL دراین‌بین راه‌اندازی مجدد شود. برای بازیافت دستی، procedure سیستم sp_cycle_errorlog را اجرا کنید. کاربر با نقش سرور ثابت sysadmin می‌تواند این procedure را اجرا کند.

EXEC sp_cycle_errorlog
GO

فایل خطا مربوط به SQL Server Agent

Agent SQL Server دارای گزارش خطای جداگانه مشابه گزارش‌های SQL Server است. برای دسترسی به گزارش به مسیر SQL Server Agent – > Error logs مراجعه کنید.روی پوشه Error log کلیک راست کرده و Configure را انتخاب کنید. محل ثبت خطای Agent و سطح گزارش Agent را نشان می‌دهد.
پسوند فایل لاگ Agent *.OUT است و طبق پیکربندی پیش فرض در پوشه گزارش ذخیره  می‌شود. به عنوان مثال، در سیستم من، دایرکتوری فایل خطا در داخل این مسیر است: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT است.
به طور پیش‌فرض، فایل لاگ Agent خطاها و هشدارها را ثبت می‌کند. بااین‌حال، ما می‌توانیم پیام‌های اطلاعاتی زیر را اضافه کنیم:

• پیام‌های هشدار: این پیام‌ها اطلاعاتی در مورد مشکلات احتمالی ارائه می‌دهند. به‌عنوان‌مثال، “Job X هنگام اجرا حذف شد”
• پیام خطا: اطلاعاتی را می‌دهد که نیاز به مداخله فوری یک DBA دارد، مانند ناتوانی در شروع یک پست الکترونیکی

برای افزودن پیام اطلاعاتی، بر روی کادر چک کلیک کنید.SQL Server از ۹ فایل گزارش Aget، SQL Server استفاده می کند. نام فایل فعلی SQLAGENT.OUT است. فایل با پسوند ۱. اولین گزارش خطای عامل بایگانی شده را نشان می‌دهد. به طور مشابه، پسوند .۹ نهمین (قدیمی ترین) گزارش خطای بایگانی شده را نشان می‌دهد.
هر بار که SQL Server Agent راه اندازی مجدد می‌شود، فایل های گزارش عامل SQL Server بازیافت می‌شوند. برای انجام این کار به صورت دستی، روی پوشه Error Logs کلیک راست کرده و Recycle را انتخاب کنید.می‌توانید از روش procedure، sp_cycle_agent_errorlo برای بازیافت فایل های گزارش عامل SQL Server به صورت دستی استفاده کنید.

USE msdb ;
GO
EXEC dbo.sp_cycle_agent_errorlog ;
GO

stored procure، گزارش خطای Agent  فعلی را با استفاده از فرایند زیر بایگانی می‌کند:

• یک گزارش خطای Agent فعلی جدید ایجاد می‌شود.
• خطای Agent فعلی out به SQLAgent.1 تبدیل می‌شود.
• ۱ به SQLAgent.2 تبدیل می‌شود.

نتیجه گیری

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

منبع

 https://www.sqlshack.com/how-to-manage-sql-server-logs-effectively/