MSSQL գործարքների մատյանի լրացում. կանխարգելման և արագ վերականգնման ռազմավարություններ

Տվյալների բազայի ադմինիստրատորների (DBA) և DevOps ինժեներների համար, որոնք կառավարում են Microsoft SQL Server-ը, քիչ ազդանշաններ են առաջացնում այնքան անհապաղ անհանգստություն, որքան 9002 սխալը՝ «X» տվյալների բազայի գործարքների մատյանը լցված է: Երբ գործարքների մատյանը լցվում է և չի կարողանում մեծանալ, տվյալների բազան փաստացի դառնում է միայն կարդալու համար (read-only): Բոլոր INSERT, UPDATE և DELETE գործողությունները դադարում են, հավելվածի գործարքները ձախողվում են, և արտադրական գործընթացը կանգ է առնում:

SQL Server-ի գործարքների մատյանի հիմքում ընկած ճարտարապետությունը հասկանալը, հիմնական պատճառը ճշգրիտ ախտորոշելը և արագ վերականգնման ընթացակարգեր իրականացնելը կարևոր հմտություններ են բարձր հասանելիությունը պահպանելու համար: Այս համապարփակ ուղեցույցը ուսումնասիրում է գործարքների մատյանի մեխանիզմները, թե ինչպես լուծել լցված մատյանի խնդիրը արտակարգ իրավիճակներում և ճարտարապետական լավագույն փորձը՝ դրա կրկնությունը կանխելու համար:

SQL Server-ի գործարքների մատյանի ճարտարապետության ըմբռնումը

Գործարքների լցված մատյանի խնդիրը արդյունավետ լուծելու համար նախ պետք է հասկանալ, թե ինչպես է SQL Server-ը գրում և կառավարում տվյալները:

Նախնական գրառման մատյան (Write-Ahead Logging — WAL)

SQL Server-ն օգտագործում է Write-Ahead Logging (WAL) արձանագրությունը: Երբ տեղի է ունենում տվյալների փոփոխություն, փոփոխությունը նախ գրվում է հիշողության մեջ գտնվող գործարքների մատյանում, այնուհետև տեղափոխվում է սկավառակի վրա գտնվող ֆիզիկական մատյան ֆայլ՝ նախքան տվյալների բազայի ֆայլերում (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 մոդելում մատյանի կրճատումը տեղի է ունենում միայն այն դեպքում, երբ գործարքների մատյանի պահուստային պատճենումը (backup) հաջողությամբ ավարտվում է (ենթադրելով, որ այլ գործընթացներ չեն պահում մատյանը ակտիվ վիճակում):

«Գործարքների մատյանը լցված է» սխալի (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');

Այս հրամանը վերադարձնում է ամենահին ակտիվ գործարքը և դրա Server Process ID-ն (SPID): Դուք կարող եք ավելի շատ մանրամասներ ստանալ այն մասին, թե ինչ է անում 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];

Գործարքների մատյանի կանխարգելման և կառավարման լավագույն փորձը

Ռեակտիվ խնդիրների լուծումը սթրեսային է և ազդում է SLA-ների վրա: Ձեռնարկատիրական տվյալների բազայի կայունության համար անհրաժեշտ է իրականացնել պրոակտիվ ճարտարապետական և գործառնական լավագույն փորձը:

1. Իրականացրեք հուսալի, ավտոմատացված պահուստավորման ռազմավարություն

Եթե տվյալների բազան գտնվում է Full վերականգնման մոդելում, գործարքների մատյանի հաճախակի պահուստային պատճենումը պարտադիր է: Կախված ձեր վերականգնման կետի նպատակից (RPO) և գործարքների ծավալից՝ մատյանի պահուստավորումները պետք է կատարվեն 5-ից 15 րոպեն մեկ:

CloudSave-ի նման ձեռնարկատիրական պահուստավորման լուծումները զգալիորեն պարզեցնում են այս գործընթացը: VDI-ի (Virtual Device Interface) միջոցով SQL Server-ի հետ անմիջականորեն ինտեգրվելով՝ CloudSave-ը թույլ է տալիս DBA-ներին կազմաձևել քաղաքականության վրա հիմնված, բարձր հաճախականությամբ գործարքների մատյանի պահուստավորումներ: Սա ապահովում է, որ մատյանները շարունակաբար կրճատվում են, ապահով կերպով գաղտնագրվում և պահվում են արտաքին կամ անփոփոխ ամպային պահեստում՝ կանխելով 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 վերականգնման մոդելում (կախված ինդեքսի տեսակից): 500GB ինդեքսի վերակառուցումը կստեղծի առնվազն 500GB գործարքների մատյանի գրառումներ:

Սպասարկման ընթացքում մատյանի ուռճացումը մեղմելու համար.
* Ինդեքսները վերակառուցելիս օգտագործեք SORT_IN_TEMPDB = ON: Սա տեսակավորման փուլը տեղափոխում է TempDB՝ նվազեցնելով օգտագործողի տվյալների բազայի գործարքների մատյանի բեռը:
* Հնարավորության դեպքում ինդեքսի վերակառուցումից անցեք ինդեքսի վերակազմակերպման (reorganize), քանի որ վերակազմակերպումներն ավելի մատյան-արդյունավետ են և կարող են ընդհատվել՝ առանց ամբողջ գործողությունը հետադարձելու:
* Խմբաքանակով կատարեք մեծ DELETE կամ UPDATE գործողություններ: 10 միլիոն տող մեկ գործարքով ջնջելու փոխարեն, ջնջեք դրանք 50,000-ական խմբաքանակներով՝ հաստատելով (commit) և թույլ տալով մատյանի պահուստային պատճեններին կրճատել մատյանը խմբաքանակների միջև:

4. Վերահսկեք բարձր հասանելիության և ռեպլիկացիայի տոպոլոգիաները

AlwaysOn Availability Groups-ում առաջնային ռեպլիկան չի կարող կրճատել իր մատյանը, քանի դեռ մատյանի գրառումները չեն հաստատվել բոլոր համաժամանակյա և ոչ համաժամանակյա երկրորդային ռեպլիկաների վրա:

Եթե երկրորդային ռեպլիկան անցնում է օֆլայն, կամ եթե ցանցի թողունակությունը չի հասցնում առաջնայինի գործարքների գեներացման տեմպին, առաջնայինի ուղարկման հերթը կմեծանա, և մատյանը կլցվի (AVAILABILITY_REPLICA սպասման տեսակ):

Իրականացրեք հուսալի մոնիտորինգ SQLServer:Replica > Log Send Queue կատարողականի հաշվիչի համար: Եթե երկրորդային ռեպլիկան ընդմիշտ կորել է, դուք պետք է հեռացնեք այն Availability Group-ից կամ կասեցնեք տվյալների տեղաշարժը՝ առաջնային մատյանը կրճատելու համար:

Եզրակացություն

Գործարքների լցված մատյանին հանդիպելը տվյալների բազայի ադմինիստրատորների համար «կնունքի» պես մի բան է, բայց դա պարտադիր չէ, որ հանգեցնի երկարատև պարապուրդի: Հասկանալով Write-Ahead Logging-ի և VLF-ների մեխանիզմները՝ դուք կարող եք արագ ախտորոշել հիմնական պատճառը՝ օգտագործելով sys.databases-ը և կիրառել արագ վերականգնման ճիշտ ռազմավարությունը:

Երկարաժամկետ կայունությունը հիմնված է ռեակտիվ լուծումներից հեռանալու վրա: Մատյան ֆայլերի նախնական չափումը, սպասարկման առօրյայի օպտիմալացումը և CloudSave-ի նման ձեռնարկատիրական պահուստավորման հարթակների օգտագործումը՝ մատյանի պահուստավորման խիստ, ավտոմատացված ժամանակացույցեր պարտադրելու համար, կապահովեն, որ ձեր գործարքների մատյանները մնան առողջ, կրճատված և պատրաստ՝ աջակցելու բարձր թողունակությամբ արտադրական աշխատանքային ծանրաբեռնվածությանը: