مدیریت لاگ تراکنش SQL Server با Checkpoint و Extended Events

مدیریت پیشرفته لاگ تراکنش SQL Server: درک و استفاده از Event Handlerها با Checkpoint

چک‌پوینت‌ها نقش حیاتی در حفظ یکپارچگی پایگاه داده و مدیریت فضای لاگ تراکنش در SQL Server دارند. در حالی که چک‌پوینت‌های خودکار (Automatic Checkpoints) به طور پیش‌فرض برای اطمینان از سرعت ریکاوری عمل می‌کنند، درک عمیق‌تر آن‌ها می‌تواند به بهینه‌سازی عملکرد و پایداری سیستم کمک کند. هدف اصلی چک‌پوینت‌ها این است که تمام صفحات کثیف (dirty pages) در حافظه را به دیسک بنویسند و بدین ترتیب میزان کاری که در صورت خرابی سرور باید برای ریکاوری انجام شود را به حداقل برسانند. این فرآیند مستقیماً بر روی لاگ تراکنش (Transaction Log) تأثیر می‌گذارد، زیرا فقط پس از یک چک‌پوینت موفقیت‌آمیز است که بخش‌های غیرضروری لاگ می‌توانند بریده شوند (Truncate).

SQL Server ابزارهای قدرتمندی برای نظارت بر فعالیت چک‌پوینت‌ها و تأثیر آن‌ها بر لاگ تراکنش ارائه می‌دهد. به ویژه، استفاده از Extended Events برای ردیابی رویدادهای مربوط به لاگ تراکنش می‌تواند بینش‌های ارزشمندی را فراهم کند. یکی از رویدادهای کلیدی برای این منظور، رویداد `log_truncation` است. این رویداد جزئیات کاملی از زمان و دلیل برش لاگ تراکنش را ارائه می‌دهد.

`log_truncation` یک ابزار تشخیصی فوق‌العاده است که نه تنها زمان وقوع برش لاگ را نشان می‌دهد، بلکه دلیل آن را نیز با استفاده از فیلدی به نام `truncation_reason` مشخص می‌کند. مقادیر مختلف این فیلد، دلایل متفاوتی را برای برش لاگ توضیح می‌دهند، از جمله:

0: NO_TRUNCATION – هیچ برشی اتفاق نیفتاده است.
1: CHECKPOINT – برش به دلیل چک‌پوینت رخ داده است.
2: LOG_BACKUP – برش به دلیل پشتیبان‌گیری از لاگ انجام شده است.
3: DB_MIRRORING – در سناریوهای Database Mirroring.
4: REPLICATION – در سناریوهای Replication.
5: AVAILABILITY_REPLICA – در Always On Availability Groups.
6: GUEST_LOG_CONSUMER – توسط مصرف‌کننده لاگ خارجی.
7: TDE – مربوط به Transparent Data Encryption.

درک این دلایل برای عیب‌یابی مشکلات مربوط به رشد بیش از حد لاگ تراکنش ضروری است. به عنوان مثال، اگر لاگ شما دائماً در حال رشد است و برش نمی‌یابد، بررسی `truncation_reason` می‌تواند نشان دهد که آیا مشکل از عدم وقوع چک‌پوینت‌ها، عدم پشتیبان‌گیری از لاگ، یا مسائل مربوط به High Availability است.

در ادامه یک نمونه اسکریپت برای ایجاد یک Extended Event Session جهت ردیابی رویداد `log_truncation` ارائه شده است:

CREATE EVENT SESSION [LogTruncationMonitoring] ON SERVER
ADD EVENT sqlserver.log_truncation(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.session_id)
)
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

پس از ایجاد، می‌توانید این نشست را فعال کرده و داده‌های جمع‌آوری شده را مشاهده کنید. این داده‌ها به شما امکان می‌دهند که با دقت بیشتری فعالیت لاگ تراکنش را در سیستم خود بررسی کنید. برای شروع و توقف نشست می‌توانید از دستورات زیر استفاده کنید:

ALTER EVENT SESSION [LogTruncationMonitoring] ON SERVER STATE = START;
GO

ALTER EVENT SESSION [LogTruncationMonitoring] ON SERVER STATE = STOP;
GO

برای مشاهده داده‌های جمع‌آوری شده از Ring Buffer، می‌توانید از پرس و جوی زیر استفاده کنید:

SELECT
    CAST(event_data AS XML) AS event_data_xml
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'LogTruncationMonitoring' AND xet.target_name = 'ring_buffer';

این پرس و جو، داده‌های XML مربوط به هر رویداد را برمی‌گرداند که می‌توانید آن‌ها را برای استخراج جزئیات مانند `database_name`، `truncation_reason` و `timestamp` تجزیه (parse) کنید.

همچنین، برای مشاهده اطلاعات کلی درباره فایل‌های لاگ فیزیکی و وضعیت آن‌ها، می‌توانید از توابع مدیریت پویا (DMVs) `sys.dm_db_log_info` و `sys.dm_db_log_stats` استفاده کنید.

برای مثال، تابع `sys.dm_db_log_info` اطلاعات مفصلی درباره هر Virtual Log File (VLF) در لاگ تراکنش یک پایگاه داده خاص را ارائه می‌دهد. این اطلاعات شامل اندازه، وضعیت و زمان آخرین نوشتن در VLF است.

SELECT * FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

در حالی که تابع `sys.dm_db_log_stats` یک نمای خلاصه‌تر از وضعیت کلی لاگ تراکنش برای یک پایگاه داده خاص را نمایش می‌دهد، از جمله تعداد VLFها، حجم کلی لاگ و میزان استفاده شده آن.

SELECT * FROM sys.dm_db_log_stats(DB_ID('YourDatabaseName'));

این DMVs در کنار رویداد `log_truncation`، یک جعبه ابزار جامع برای مدیریت و نظارت بر لاگ تراکنش در SQL Server فراهم می‌کنند. ترکیب این ابزارها با دانش عمیق از رفتار چک‌پوینت‌ها، به مدیران پایگاه داده (DBAها) کمک می‌کند تا عملکرد سیستم را بهینه کرده و از مشکلات مربوط به رشد کنترل‌نشده لاگ تراکنش جلوگیری کنند. این امر به ویژه در محیط‌های با دسترس‌پذیری بالا (High Availability) مانند Always On Availability Groups و Database Mirroring که مدیریت لاگ تراکنش پیچیده‌تر است، اهمیت فزاینده‌ای پیدا می‌کند. با رصد دقیق، می‌توان اطمینان حاصل کرد که لاگ‌ها به طور مؤثر برش داده می‌شوند و فضای دیسک به درستی مدیریت می‌شود.