سجل معاملات MSSQL ممتلئ: استراتيجيات الوقاية والاسترداد السريع

بالنسبة لمسؤولي قواعد البيانات (DBAs) ومهندسي DevOps الذين يديرون Microsoft SQL Server، هناك القليل من التنبيهات التي تسبب قلقاً فورياً مثل الخطأ 9002: سجل المعاملات لقاعدة البيانات ‘X’ ممتلئ. عندما يمتلئ سجل المعاملات ولا يمكنه التوسع، تصبح قاعدة البيانات فعلياً للقراءة فقط. تتوقف جميع عمليات INSERT و UPDATE و DELETE، وتفشل معاملات التطبيقات، وتتوقف بيئة الإنتاج عن العمل تماماً.

يعد فهم البنية الأساسية لسجل معاملات SQL Server، وتشخيص السبب الجذري بدقة، وتنفيذ إجراءات الاسترداد السريع مهارات بالغة الأهمية للحفاظ على التوافر العالي. يستكشف هذا الدليل الشامل آليات سجل المعاملات، وكيفية حل مشكلة امتلاء السجل في حالات الطوارئ، وأفضل الممارسات الهندسية لمنع تكرار حدوث ذلك.

فهم بنية سجل معاملات SQL Server

لاستكشاف أخطاء سجل المعاملات الممتلئ وإصلاحها بفعالية، يجب عليك أولاً فهم كيفية قيام SQL Server بكتابة البيانات وإدارتها.

تسجيل الكتابة المسبقة (WAL)

يستخدم SQL Server بروتوكول تسجيل الكتابة المسبقة (Write-Ahead Logging – WAL). كلما حدث تعديل في البيانات، يتم كتابة التغيير أولاً في سجل المعاملات في الذاكرة، ثم يتم تفريغه إلى ملف السجل الفعلي على القرص قبل تحديث صفحات البيانات الفعلية في ملفات قاعدة البيانات (MDF/NDF). يضمن هذا الامتثال لخصائص ACID (الذرية، الاتساق، العزل، المتانة)، مما يضمن أنه في حالة حدوث عطل، يمكن لـ SQL Server إعادة تشغيل (roll forward) أو التراجع عن (roll back) المعاملات.

ملفات السجل الافتراضية (VLFs) والتسجيل الدائري

داخلياً، يتم تقسيم ملف سجل المعاملات الفعلي (LDF) إلى أجزاء منطقية أصغر تسمى ملفات السجل الافتراضية (VLFs). يعمل سجل المعاملات بشكل دائري. مع كتابة سجلات المعاملات، فإنها تملأ ملف VLF واحداً وتنتقل إلى التالي.

عندما يصل السجل إلى نهاية الملف الفعلي، فإنه يحاول الالتفاف إلى البداية. ومع ذلك، لا يمكنه الكتابة فوق ملف VLF إلا إذا تم وضع علامة عليه كـ غير نشط. إذا كانت جميع ملفات VLF نشطة (بمعنى أنها تحتوي على سجلات معاملات لا تزال مطلوبة بواسطة SQL Server)، فلا يمكن للسجل الالتفاف. إذا تم تمكين النمو التلقائي (auto-growth) وتوفرت مساحة على القرص، ينمو الملف الفعلي. إذا كان القرص ممتلئاً أو كان النمو التلقائي مقيداً، فستواجه الخطأ 9002.

اقتطاع السجل مقابل تقليص السجل

من المفاهيم الخاطئة الشائعة أن اقتطاع السجل يقلل من حجم الملف الفعلي.
* اقتطاع السجل (Log Truncation): عملية وضع علامة على ملفات VLF النشطة كغير نشطة، مما يجعل المساحة متاحة لإعادة الاستخدام. وهي لا تقلل من حجم ملف LDF على القرص.
* تقليص السجل (Log Shrinking): عملية تقليل حجم ملف LDF فعلياً وإعادة المساحة إلى نظام التشغيل.

في نموذج الاسترداد الكامل (Full Recovery model)، يحدث اقتطاع السجل فقط عند اكتمال نسخة احتياطية لسجل المعاملات بنجاح (بافتراض عدم وجود عمليات أخرى تبقي السجل نشطاً).

تشخيص خطأ “سجل المعاملات ممتلئ” (الخطأ 9002)

عندما يكون السجل ممتلئاً، فإن خطوتك الأولى ليست إضافة مساحة قرص أو تقليص الملفات بشكل عشوائي. يجب عليك تحديد سبب عدم قدرة السجل على الاقتطاع. يوفر SQL Server آلية مدمجة لإخبارك بالضبط بما يمنع إعادة استخدام السجل عبر عرض الكتالوج sys.databases.

قم بتشغيل أمر T-SQL التالي لتحديد عنق الزجاجة:

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

يمكنك أيضاً التحقق من استخدام المساحة الحالي لسجلات المعاملات الخاصة بك باستخدام:

DBCC SQLPERF(LOGSPACE);

حالات log_reuse_wait_desc الشائعة

1. LOG_BACKUP: قاعدة البيانات في نموذج الاسترداد الكامل أو المجمع (Full or Bulk-Logged)، ولم يتم أخذ نسخة احتياطية لسجل المعاملات مؤخراً. هذا هو السبب الأكثر شيوعاً.
2. ACTIVE_TRANSACTION: معاملة طويلة الأمد (مثل إعادة بناء فهرس ضخم أو معاملة منسية غير ملتزم بها) تبقي السجل نشطاً.
3. REPLICATION / CDC: تم تمكين النسخ المتماثل للمعاملات (Transactional Replication) أو التقاط بيانات التغيير (CDC)، ولم يقم وكيل قارئ السجل (Log Reader Agent) بمعالجة المعاملات بعد.
4. AVAILABILITY_REPLICA: في مجموعة توفر AlwaysOn، تكون النسخة المتماثلة الثانوية غير متصلة أو تتم مزامنتها ببطء شديد، مما يجبر النسخة المتماثلة الأساسية على الاحتفاظ بسجلات المعاملات حتى يتم تثبيتها على النسخة الثانوية.

استراتيجيات الاسترداد السريع: حل المشكلة في بيئة الإنتاج

اعتماداً على حالة log_reuse_wait_desc التي تم إرجاعها، ستختلف استجابتك للطوارئ. إليك استراتيجيات الاسترداد السريع للسيناريوهات الأكثر شيوعاً.

السيناريو 1: نسخ احتياطية مفقودة أو فاشلة للسجل (LOG_BACKUP)

إذا كان نوع الانتظار هو LOG_BACKUP، فالحل مباشر: يجب عليك إجراء نسخ احتياطي لسجل المعاملات.

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

بمجرد اكتمال النسخ الاحتياطي، سيتم اقتطاع ملفات VLF غير النشطة، وسيستأنف SQL Server العمليات العادية. إذا كان محرك الأقراص الخاص بالنسخ الاحتياطي ممتلئاً، فقد تحتاج إلى النسخ الاحتياطي إلى مشاركة شبكة مؤقتة أو جهاز فارغ (لا يُنصح به بشدة ما لم تكن قاعدة البيانات سهلة الاستنساخ، لأنه يكسر سلسلة السجل):

-- تحذير: هذا يكسر سلسلة السجل ويضر بالاسترداد في نقطة زمنية محددة.
-- استخدمه فقط إذا كان ضرورياً للغاية وقم بإجراء نسخة احتياطية كاملة (FULL) فوراً بعده.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

السيناريو 2: معاملات نشطة طويلة الأمد (ACTIVE_TRANSACTION)

إذا كانت معاملة واحدة تعمل لساعات، فإنها تمنع اقتطاع السجل طوال المدة. أولاً، حدد المعاملة المسببة للمشكلة:

DBCC OPENTRAN('YourDatabaseName');

يعيد هذا الأمر أقدم معاملة نشطة ومعرف عملية الخادم (SPID) الخاص بها. يمكنك جمع المزيد من التفاصيل حول ما تفعله هذه العملية عن طريق الاستعلام عن طرق عرض الإدارة الديناميكية (DMVs):

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

إذا كانت المعاملة استعلاماً مارقاً أو عملية متوقفة، فقد تحتاج إلى إنهائها لتحرير السجل.

KILL <SPID>;

ملاحظة: سيؤدي إنهاء معاملة ضخمة إلى تشغيل عملية تراجع (rollback)، والتي قد تستغرق وقتاً طويلاً وستولد مؤقتاً نشاط سجل إضافي. لا تقم بإعادة تشغيل خدمة SQL Server أثناء التراجع، وإلا ستدخل قاعدة البيانات في وضع الاسترداد عند إعادة التشغيل.

السيناريو 3: تخصيص مساحة الطوارئ (القرص ممتلئ بنسبة 100%)

إذا استهلك ملف LDF محرك الأقراص بالكامل، فلا يمكنك حتى تشغيل نسخة احتياطية لأن SQL Server يتطلب قدراً ضئيلاً من مساحة السجل لتسجيل حدث النسخ الاحتياطي نفسه. في هذا السيناريو، يجب عليك إضافة ملف سجل ثانوي على محرك أقراص مختلف به مساحة متاحة.

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

يوفر هذا فوراً لـ SQL Server مساحة للتنفس. بمجرد أن تصبح قاعدة البيانات متصلة، قم بإجراء نسخة احتياطية لسجل المعاملات، وأفرغ ملف السجل الثانوي، ثم قم بإزالته:

-- 1. قم بإجراء نسخة احتياطية للسجل لاقتطاعه
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. إفراغ ملف السجل المؤقت
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. إزالة ملف السجل المؤقت
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

أفضل الممارسات لمنع امتلاء سجل المعاملات وإدارته

استكشاف الأخطاء وإصلاحها التفاعلي أمر مرهق ويؤثر على اتفاقيات مستوى الخدمة (SLAs). يعد تنفيذ أفضل الممارسات الهندسية والتشغيلية الاستباقية أمراً ضرورياً لاستقرار قواعد بيانات المؤسسات.

1. تنفيذ استراتيجية نسخ احتياطي قوية وآلية

إذا كانت قاعدة البيانات في نموذج الاسترداد الكامل، فإن النسخ الاحتياطي المتكرر لسجل المعاملات إلزامي. اعتماداً على هدف نقطة الاسترداد (RPO) وحجم المعاملات، يجب أن تتم نسخ السجل الاحتياطية كل 5 إلى 15 دقيقة.

تعمل حلول النسخ الاحتياطي للمؤسسات مثل CloudSave على تبسيط هذه العملية بشكل كبير. من خلال التكامل المباشر مع SQL Server عبر VDI (واجهة الجهاز الافتراضي)، تسمح CloudSave لمسؤولي قواعد البيانات بتكوين نسخ احتياطية لسجل المعاملات عالية التكرار ومبنية على السياسات. يضمن هذا اقتطاع السجلات باستمرار، وتشفيرها بشكل آمن، وتخزينها خارج الموقع أو في تخزين سحابي غير قابل للتغيير، مما يمنع حالة انتظار LOG_BACKUP دون الحاجة إلى وظائف SQL Agent مخصصة ومعقدة.

2. ضبط حجم سجل المعاملات وإدارة ملفات VLF

الاعتماد على النمو التلقائي لإدارة حجم سجل المعاملات هو نمط خاطئ وخطير. عمليات النمو التلقائي مكلفة وتوقف معالجة المعاملات أثناء تهيئة القرص بالأصفار (ما لم يتم تمكين تهيئة الملف الفوري، وهو ما لا ينطبق على ملفات السجل).

علاوة على ذلك، تؤدي عمليات النمو التلقائي الصغيرة والمتكررة (مثل النمو بنسبة 10% أو 50 ميجابايت في كل مرة) إلى تجزئة VLF. سيؤدي سجل المعاملات الذي يحتوي على آلاف ملفات VLF الصغيرة إلى تدهور أوقات بدء تشغيل قاعدة البيانات، وأداء النسخ الاحتياطي، وزمن انتقال النسخ المتماثل بشكل كبير.

• تحديد حجم السجل مسبقاً: قم بتحليل أكبر عمليات الصيانة الخاصة بك (مثل إعادة بناء الفهارس) وقم بتحديد حجم ملف LDF مسبقاً لاستيعابها دون الحاجة للنمو.
• تعيين نمو تلقائي ثابت: قم بتغيير النمو التلقائي من نسبة مئوية إلى حجم ثابت (مثل 1 جيجابايت أو 5 جيجابايت) لضمان إنشاء ملفات VLF بحجم صحي.

يمكنك التحقق من عدد ملفات VLF باستخدام الاستعلام التالي (لإصدار SQL Server 2017 وما بعده):

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

إذا كان عدد ملفات VLF لديك يزيد عن 500، ففكر في الانتظار لفترة هادئة، وتقليص السجل إلى حجم أدنى، ثم إعادة تكبيره يدوياً إلى الحجم المطلوب في أجزاء كبيرة.

3. تحسين عمليات صيانة الفهارس

عمليات إعادة بناء الفهارس هي عمليات مسجلة بالكامل، حتى في نموذج الاسترداد المجمع (اعتماداً على نوع الفهرس). سيؤدي إعادة بناء فهرس بحجم 500 جيجابايت إلى إنشاء ما لا يقل عن 500 جيجابايت من سجلات المعاملات.

للتخفيف من تضخم السجل أثناء الصيانة:
* استخدم SORT_IN_TEMPDB = ON عند إعادة بناء الفهارس. هذا ينقل مرحلة الفرز إلى TempDB، مما يقلل العبء على سجل معاملات قاعدة بيانات المستخدم.
* قم بالتبديل من إعادة بناء الفهارس إلى إعادة تنظيم الفهارس حيثما أمكن، حيث أن عمليات إعادة التنظيم أكثر كفاءة في السجل ويمكن مقاطعتها دون التراجع عن العملية بأكملها.
* قم بتجميع عمليات DELETE أو UPDATE الكبيرة. بدلاً من حذف 10 ملايين صف في معاملة واحدة، احذفها في مجموعات من 50,000، مع الالتزام والسماح بنسخ السجل الاحتياطية باقتطاع السجل بين المجموعات.

4. مراقبة التوافر العالي وطوبولوجيا النسخ المتماثل

في مجموعات توفر AlwaysOn، لا يمكن للنسخة المتماثلة الأساسية اقتطاع سجلها حتى يتم تثبيت سجلات المعاملات على جميع النسخ الثانوية المتزامنة وغير المتزامنة.

إذا توقفت نسخة ثانوية عن العمل، أو إذا كان عرض نطاق الشبكة لا يمكنه مواكبة معدل إنشاء المعاملات في النسخة الأساسية، فسوف ينمو طابور الإرسال في النسخة الأساسية، وسيمتلئ السجل (نوع انتظار AVAILABILITY_REPLICA).

قم بتنفيذ مراقبة قوية لعداد الأداء SQLServer:Replica > Log Send Queue. إذا فقدت نسخة ثانوية بشكل دائم، فيجب عليك إزالتها من مجموعة التوفر أو تعليق نقل البيانات للسماح باقتطاع السجل الأساسي.

الخلاصة

مواجهة سجل معاملات ممتلئ هي تجربة شائعة لمسؤولي قواعد البيانات، ولكن لا يجب أن تؤدي إلى وقت تعطل طويل. من خلال فهم آليات تسجيل الكتابة المسبقة وملفات VLF، يمكنك تشخيص السبب الجذري بسرعة باستخدام sys.databases وتطبيق استراتيجية الاسترداد السريع الصحيحة.

يعتمد الاستقرار طويل الأمد على الابتعاد عن الإصلاحات التفاعلية. إن تحديد حجم ملفات السجل مسبقاً، وتحسين إجراءات الصيانة، واستخدام منصات النسخ الاحتياطي على مستوى المؤسسات مثل CloudSave لفرض جداول نسخ احتياطي صارمة وآلية للسجل سيضمن بقاء سجلات معاملاتك صحية ومقتطعة وجاهزة لدعم أحمال عمل الإنتاج ذات الإنتاجية العالية.