MSSQL Transaction Log پڕبووە: ستراتیژییەکانی خۆپاراستن و چاککردنەوەی خێرا

بۆ بەڕێوەبەرانی داتابەیس (DBAs) و ئەندازیارانی DevOps کە Microsoft SQL Server بەڕێوەدەبەن، کەم ئاگادارکردنەوە هەن کە هێندەی هەڵەی 9002 ببنە هۆی دڵەڕاوکێی دەستبەجێ: لۆگی ترانسەکت بۆ داتابەیسی ‘X’ پڕ بووە. کاتێک لۆگی ترانسەکت پڕ دەبێت و ناتوانێت گەشە بکات، داتابەیسەکە بە کردەیی دەبێتە تەنها خوێندنەوە (read-only). هەموو کردارەکانی INSERT، UPDATE، و DELETE دەوەستن، ترانسەکتەکانی ئەپڵیکەیشنەکە شکست دەهێنن، و کاری بەرهەمهێنان ڕادەوەستێت.

تێگەیشتن لە تەلارسازی بنەڕەتی لۆگی ترانسەکەتی SQL Server، دەستنیشانکردنی وردی هۆکاری سەرەکی، و جێبەجێکردنی ڕێکارەکانی چاکبوونەوەی خێرا، کارامەیی گرنگن بۆ پاراستنی بەردەستبوونی بەرز. ئەم ڕێبەرە گشتگیرە میکانیزمەکانی لۆگی ترانسەکت، چۆنیەتی چارەسەرکردنی لۆگی پڕ لە کاتی فریاگوزاریدا، و باشترین پراکتیزە تەلارسازییەکان بۆ ڕێگریکردن لە دووبارەبوونەوەی ئەم کێشەیە دەخاتە ڕوو.

تێگەیشتن لە تەلارسازی لۆگی ترانسەکەتی SQL Server

بۆ چارەسەرکردنی کێشەی لۆگی ترانسەکەتی پڕ بە شێوەیەکی کاریگەر، دەبێت سەرەتا تێبگەیت کە SQL Server چۆن داتا دەنووسێت و بەڕێوەی دەبات.

نووسینی پێشوەختەی لۆگ (WAL)

SQL Server پرۆتۆکۆلی نووسینی پێشوەختەی لۆگ (WAL) بەکاردەهێنێت. هەر کاتێک دەستکاری داتا ڕوودەدات، گۆڕانکارییەکە سەرەتا لە لۆگی ترانسەکت لە بیرگەی کاتی (memory) دەنووسرێت، پاشان پێش ئەوەی پەڕەکانی داتای ڕاستەقینە لە پەڕگەکانی داتابەیس (MDF/NDF) نوێ بکرێنەوە، بۆ پەڕگەی لۆگی فیزیکی لەسەر دیسک دەگوازرێتەوە. ئەمە مسۆگەری پابەندبوون بە ACID (Atomicity, Consistency, Isolation, Durability) دەکات، کە دڵنیایی دەدات لەوەی لە کاتی ڕوودانی کێشەیەکدا، SQL Server دەتوانێت ترانسەکتەکان دووبارە بکاتەوە (roll forward) یان هەڵیان بوەشێنێتەوە (roll back).

پەڕگەکانی لۆگی ڤیرچوەڵ (VLFs) و لۆگکردنی بازنەیی

لە ناوەوە، پەڕگەی لۆگی ترانسەکەتی فیزیکی (LDF) دابەش دەکرێت بۆ بەشە لۆژیکییە بچووکترەکان کە پێیان دەوترێت پەڕگەکانی لۆگی ڤیرچوەڵ (VLFs). لۆگی ترانسەکت بە شێوەیەکی بازنەیی کاردەکات. کاتێک تۆمارەکانی لۆگ دەنووسرێن، VLF-ێک پڕ دەکەن و دەچنە سەر یەکێکی تر.

کاتێک لۆگەکە دەگاتە کۆتایی پەڕگە فیزیکییەکە، هەوڵ دەدات بگەڕێتەوە سەرەتا. بەڵام، تەنها کاتێک دەتوانێت VLF-ێک بسڕێتەوە کە ئەو VLF-ە وەک ناچالاک دیاری کرابێت. ئەگەر هەموو VLF-ەکان چالاک بن (واتە هێشتا ئەو تۆمارانەی لۆگیان تێدایە کە SQL Server پێویستی پێیانە)، لۆگەکە ناتوانێت بگەڕێتەوە. ئەگەر گەشەی خۆکار (auto-growth) چالاک کرابێت و شوێنی دیسک هەبێت، پەڕگە فیزیکییەکە گەشە دەکات. ئەگەر دیسکەکە پڕ بێت یان گەشەی خۆکار سنووردار کرابێت، تووشی هەڵەی 9002 دەبیت.

بڕینی لۆگ (Truncation) بەرامبەر بچووککردنەوەی لۆگ (Shrinking)

هەڵەیەکی باو هەیە کە وادەزانن بڕینی لۆگ قەبارەی پەڕگە فیزیکییەکە کەمدەکاتەوە.
* بڕینی لۆگ (Log Truncation): پرۆسەی دیاریکردنی VLF-ە چالاکەکان وەک ناچالاک، بۆ ئەوەی شوێنەکە بۆ بەکارهێنانەوە ئامادە بێت. ئەمە قەبارەی پەڕگەی LDF لەسەر دیسک کەم ناکاتەوە.
* بچووککردنەوەی لۆگ (Log Shrinking): پرۆسەی کەمکردنەوەی قەبارەی پەڕگەی LDF بە شێوەیەکی فیزیکی و گەڕاندنەوەی شوێنەکە بۆ سیستەمی کارپێکردن.

لە مۆدێلی Full Recovery، بڕینی لۆگ تەنها کاتێک ڕوودەدات کە بکەپێکی لۆگی ترانسەکت بە سەرکەوتوویی ئەنجام بدرێت (بە مەرجێک هیچ پرۆسەیەکی تر لۆگەکە بە چالاکی نەهێڵێتەوە).

دەستنیشانکردنی هەڵەی “لۆگی ترانسەکت پڕە” (هەڵەی 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 یان Bulk-Logged-ە، و ماوەیەکی زۆرە بکەپی لۆگی ترانسەکت نەکراوە. ئەمە باوترین هۆکارە.
2. ACTIVE_TRANSACTION: ترانسەکتێکی درێژخایەن (بۆ نموونە، دووبارە دروستکردنەوەی ئیندێکسێکی زەبەلاح یان ترانسەکتێکی هەڵنەوەشاوە) لۆگەکە بە چالاکی دەهێڵێتەوە.
3. REPLICATION / CDC: Transactional Replication یان Change Data Capture (CDC) چالاک کراوە، و Log Reader Agent هێشتا ترانسەکتەکانی یەکلا نەکردووەتەوە.
4. AVAILABILITY_REPLICA: لە AlwaysOn Availability Group، ڕیپلیکایەکی لاوەکی پچڕاوە یان بە خاوی هاوکات دەبێت، ئەمەش ناچاری دەکات ڕیپلیکای سەرەکی تۆمارەکانی لۆگ بهێڵێتەوە تاوەکو لەسەر ڕیپلیکای لاوەکی جێگیر دەبن.

ستراتیژییەکانی چاکبوونەوەی خێرا: چارەسەرکردنی کێشەکە لە بەرهەمهێناندا

بەپێی ئەو 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 کارکردنی ئاسایی خۆی دەست پێدەکاتەوە. ئەگەر درایڤی بکەپەکەت پڕە، ڕەنگە پێویست بکات بکەپەکە بۆ شوێنێکی تۆڕی کاتی یان ئامێرێکی null بگوازیتەوە (ئەمە پێشنیار ناکرێت مەگەر داتابەیسەکە بە ئاسانی دووبارە دروست بکرێتەوە، چونکە زنجیرەی لۆگەکە دەپچڕێنێت):

-- ئاگاداری: ئەمە زنجیرەی لۆگەکە دەپچڕێنێت و کاریگەری لەسەر چاکبوونەوەی کاتی دەبێت.
-- تەنها ئەگەر زۆر پێویست بوو بەکاری بهێنە و دەستبەجێ بە بکەپی FULL بەدوایدا بڕۆ.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

دۆخی 2: ترانسەکتە چالاکە درێژخایەنەکان (ACTIVE_TRANSACTION)

ئەگەر ترانسەکتێک بۆ چەند کاتژمێرێک کاری کردبێت، ڕێگری لە بڕینی لۆگ دەکات بۆ تەواوی ئەو ماوەیە. سەرەتا، ئەو ترانسەکتە دەستنیشان بکە کە کێشەکە دروست دەکات:

DBCC OPENTRAN('YourDatabaseName');

ئەم فەرمانە کۆنترین ترانسەکەتی چالاک و ناسنامەی پرۆسەی سێرڤەرەکەی (SPID) دەگەڕێنێتەوە. دەتوانیت وردەکاری زیاتر لەسەر ئەوەی SPID-ەکە چی دەکات بە پرسیارکردن لە dynamic management views (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];

باشترین پراکتیزەکان بۆ ڕێگریکردن و بەڕێوەبردنی لۆگی ترانسەکت

چارەسەرکردنی کاردانەوەیی ماندووکەرە و کاریگەری لەسەر SLA دەبێت. جێبەجێکردنی باشترین پراکتیزە تەلارسازی و کارگێڕییەکان بۆ سەقامگیری داتابەیسی کۆمپانیاکان پێویستە.

1. جێبەجێکردنی ستراتیژییەکی بکەپی بەهێز و ئۆتۆماتیکی

ئەگەر داتابەیسێک لە مۆدێلی Full recovery-دایە، بکەپی بەردەوامی لۆگی ترانسەکت ناچارییە. بەپێی ئامانجی خاڵی چاکبوونەوە (RPO) و قەبارەی ترانسەکتەکانت، بکەپی لۆگ دەبێت هەر 5 بۆ 15 خولەک جارێک ئەنجام بدرێت.

چارەسەرەکانی بکەپی کۆمپانیاکان وەک CloudSave ئەم پرۆسەیە بە شێوەیەکی بەرچاو ئاسان دەکەن. بە تێکەڵکردنی ڕاستەوخۆ لەگەڵ SQL Server لە ڕێگەی VDI (Virtual Device Interface)، CloudSave ڕێگە بە بەڕێوەبەرانی داتابەیس دەدات بکەپی لۆگی ترانسەکەتی خێرا و بەپێی سیاسەت ڕێکبخەن. ئەمە دڵنیایی دەدات کە لۆگەکان بەردەوام دەبڕدرێن، بە سەلامەتی کۆد دەکرێن، و لە دەرەوەی شوێنەکە یان لە هەوری پارێزراودا هەڵدەگیرێن، ئەمەش ڕێگری لە دۆخی چاوەڕوانی LOG_BACKUP دەکات بەبێ ئەوەی پێویست بە کارەکانی SQL Agent-ی ئاڵۆز بکات.

2. ڕێکخستنی قەبارەی لۆگی ترانسەکت و بەڕێوەبردنی VLF-ەکان

پشت بەستن بە گەشەی خۆکار بۆ بەڕێوەبردنی قەبارەی لۆگی ترانسەکتەکەت شێوازێکی مەترسیدارە. کردارەکانی گەشەی خۆکار تێچوویان زۆرە و پرۆسەی ترانسەکتەکان دەوەستێنن تاوەکو دیسکەکە بە سفر دەستپێدەکات (مەگەر Instant File Initialization چالاک کرابێت، کە بۆ پەڕگەکانی لۆگ کار ناکات).

جگە لەوەش، گەشەی خۆکاری بچووک و زۆر (بۆ نموونە، زیادکردنی 10% یان 50MB لە هەر جارێکدا) دەبێتە هۆی پارچەپارچەبوونی VLF. لۆگێکی ترانسەکت بە هەزاران VLF-ی بچووک کاتی دەستپێکردنی داتابەیس، ئەدای بکەپ، و دواکەوتنی ڕیپلیکەیشن بە توندی لاواز دەکات.

• قەبارەی پێشوەختەی لۆگ: گەورەترین کردارەکانی چاککردنەوەت (وەک دووبارە دروستکردنەوەی ئیندێکس) شیکار بکە و قەبارەی پەڕگەی LDF پێشوەختە دیاری بکە بۆ ئەوەی بەبێ گەشەکردن جێگەیان ببێتەوە.
• گەشەی خۆکاری جێگیر دابنێ: گەشەی خۆکار لە ڕێژەی سەدییەوە بگۆڕە بۆ قەبارەیەکی جێگیر (بۆ نموونە 1GB یان 5GB) بۆ دڵنیابوون لەوەی 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. باشترکردنی کردارەکانی چاککردنەوەی ئیندێکس

دووبارە دروستکردنەوەی ئیندێکسەکان کردارگەلێکی بە تەواوی لۆگکراون، تەنانەت لە مۆدێلی Bulk-Logged recovery-شدا (بەپێی جۆری ئیندێکسەکە). دووبارە دروستکردنەوەی ئیندێکسێکی 500GB لانی کەم 500GB تۆماری لۆگی ترانسەکت دروست دەکات.

بۆ کەمکردنەوەی قەڵەوبوونی لۆگ لە کاتی چاککردنەوەدا:
* لە کاتی دووبارە دروستکردنەوەی ئیندێکسەکاندا SORT_IN_TEMPDB = ON بەکاربهێنە. ئەمە قۆناغی ڕیزکردن بۆ TempDB دەگوازێتەوە، ئەمەش بارگرانی لەسەر لۆگی ترانسەکەتی داتابەیسی بەکارهێنەر کەمدەکاتەوە.
* لە دووبارە دروستکردنەوەی ئیندێکسەوە بۆ ڕێکخستنەوەی ئیندێکس (reorganize) بگۆڕە لەو شوێنانەی دەکرێت، چونکە ڕێکخستنەوەکان لە ڕووی لۆگەوە کاریگەرترن و دەکرێت بوەستێنرێن بەبێ ئەوەی پێویست بکات هەموو کردارەکە هەڵبوەشێنرێتەوە.
* کردارە گەورەکانی DELETE یان UPDATE بە پارچە ئەنجام بدە. لە جیاتی سڕینەوەی 10 ملیۆن ڕیز لە یەک ترانسەکتدا، بە پارچەی 50,000 سڕییانەوە، ئەمەش ڕێگە بە بکەپی لۆگ دەدات لە نێوان پارچەکاندا لۆگەکە ببڕێت.

4. چاودێریکردنی بەردەستبوونی بەرز و تۆپۆلۆژییەکانی ڕیپلیکەیشن

لە AlwaysOn Availability Groups-دا، ڕیپلیکای سەرەکی ناتوانێت لۆگەکەی ببڕێت تاوەکو تۆمارەکانی لۆگ لەسەر هەموو ڕیپلیکا لاوەکییە هاوکات و ناهاوکاتەکان جێگیر نەبن.

ئەگەر ڕیپلیکایەکی لاوەکی لە کار بکەوێت، یان ئەگەر باندویزی تۆڕەکە نەتوانێت لەگەڵ خێرایی دروستکردنی ترانسەکەتی سەرەکی بڕوات، ڕیزی ناردنی سەرەکی گەشە دەکات، و لۆگەکە پڕ دەبێت (جۆری چاوەڕوانی AVAILABILITY_REPLICA).

چاودێرییەکی بەهێز بۆ پێوانەی ئەدای SQLServer:Replica > Log Send Queue جێبەجێ بکە. ئەگەر ڕیپلیکایەکی لاوەکی بە یەکجاری لەدەست چوو، دەبێت لە Availability Group لای ببەیت یان جوڵەی داتا ڕابگریت بۆ ئەوەی ڕێگە بە لۆگی سەرەکی بدەیت ببڕدرێت.

کۆتایی

تووشبوون بە لۆگی ترانسەکەتی پڕ بۆ بەڕێوەبەرانی داتابەیس شتێکی باوە، بەڵام پێویست ناکات ببێتە هۆی وەستانی درێژخایەنی کارەکان. بە تێگەیشتن لە میکانیزمەکانی نووسینی پێشوەختەی لۆگ و VLF-ەکان، دەتوانیت بە خێرایی هۆکاری سەرەکی بە بەکارهێنانی sys.databases دەستنیشان بکەیت و ستراتیژی چاکبوونەوەی خێرای دروست جێبەجێ بکەیت.

سەقامگیری درێژخایەن پشت بە دوورکەوتنەوە لە چارەسەرە کاردانەوەییەکان دەبەستێت. دیاریکردنی قەبارەی پێشوەختەی پەڕگەکانی لۆگ، باشترکردنی ڕۆتینەکانی چاککردنەوە، و بەکارهێنانی پلاتفۆرمەکانی بکەپی ئاستی کۆمپانیاکان وەک CloudSave بۆ جێبەجێکردنی خشتەی بکەپی لۆگی توند و ئۆتۆماتیکی، دڵنیایی دەدات کە لۆگەکانی ترانسەکتەکەت تەندروست، بڕدراو، و ئامادە دەبن بۆ پشتیوانیکردنی کارە زۆرەکانی بەرهەمهێنان.