Categories
Database Backup

** Discover expert strategies for preventing and resolving MSSQL transaction log full errors (Error 9002). Learn rapid recovery techniques, VLF management, and architectural best practices for DBAs.

פֿאַר דאַטאַבייס אַדמיניסטראַטאָרס (DBAs) און DevOps אינזשענירן וואָס פירן Microsoft SQL Server, ווייניק אַלערץ פאַרשאַפן אַזוי פיל גלייַך דייַגעס ווי טעות 9002: The transaction log for database ‘X’ is full (דער טראַנסאַקציע לאָג פֿאַר דאַטאַבייס ‘X’ איז פול). ווען דער טראַנסאַקציע לאָג ווערט פול און קען נישט וואַקסן, ווערט די דאַטאַבייס עפעקטיוו לייענען-בלויז. אַלע 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) טראַנסאַקציעס.

Virtual Log Files (VLFs) און סירקולאַר לאָגינג

אינערלעך, די פיזיש טראַנסאַקציע לאָג טעקע (LDF) איז צעטיילט אין קלענערע, לאַדזשיקאַל סעגמענטן גערופן Virtual Log Files (VLFs). די טראַנסאַקציע לאָג אַפּערייץ סירקולאַרלי. ווי לאָג רעקאָרדס זענען געשריבן, זיי פּלאָמבירן איין VLF און מאַך צו די ווייַטער.

ווען די לאָג ריטשאַז די סוף פון די פיזיש טעקע, עס פרוווט צו ייַנוויקלען אַרום צו די אָנהייב. אָבער, עס קענען בלויז אָווועררייט אַ VLF אויב אַז VLF איז אנגעצייכנט ווי inactive (ניט-אַקטיוו). אויב אַלע VLFs זענען אַקטיוו (טייַטש זיי אַנטהאַלטן לאָג רעקאָרדס נאָך פארלאנגט דורך SQL Server), די לאָג קען נישט ייַנוויקלען. אויב אַוטאָ-גראָוט איז ענייבאַלד און דיסק פּלאַץ איז בנימצא, די פיזיש טעקע וואקסט. אויב די דיסק איז פול אָדער אַוטאָ-גראָוט איז ריסטריקטאַד, איר טרעפן טעות 9002.

לאָג טראַנקיישאַן ווס. לאָג שרינקינג

אַ פּראָסט מיסקאַנסעפּשאַן איז אַז טראַנקייטינג די לאָג ראַדוסאַז די פיזיש טעקע גרייס.
* לאָג טראַנקיישאַן: דער פּראָצעס פון מאַרקינג אַקטיוו VLFs ווי ינאַקטיוו, מאכן די פּלאַץ בנימצא פֿאַר רייוז. עס טוט נישט רעדוצירן די גרייס פון די LDF טעקע אויף דיסק.
* לאָג שרינקינג: דער פּראָצעס פון פיזיקלי רידוסינג די LDF טעקע גרייס און צוריקקומען פּלאַץ צו די אָפּערייטינג סיסטעם.

אין די פול רעקאָווערי מאָדעל, לאָג טראַנקיישאַן בלויז אַקערז ווען אַ טראַנסאַקציע לאָג באַקאַפּ איז הצלחה געענדיקט (אַסומינג קיין אנדערע פּראַסעסאַז האַלטן די לאָג אַקטיוו).

דיאַגנאָזינג די “Transaction Log Full” טעות (טעות 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: די דאַטאַבייס איז אין די פול אָדער Bulk-Logged רעקאָווערי מאָדעל, און אַ טראַנסאַקציע לאָג באַקאַפּ איז נישט גענומען לעצטנס. דאָס איז די מערסט פּראָסט סיבה.
  2. ACTIVE_TRANSACTION: אַ לאַנג-פליסנדיק טראַנסאַקציע (למשל, אַ מאַסיוו אינדעקס ריבילד אָדער אַ פארגעסן אַנקאַמיטאַד טראַנסאַקציע) האַלט די לאָג אַקטיוו.
  3. REPLICATION / CDC: טראַנסאַקשאַנאַל רעפּליקאַטיאָן אָדער 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;

אַמאָל די באַקאַפּ קאַמפּליץ, די ינאַקטיוו VLFs וועט זיין טראַנקייטיד, און SQL Server וועט נעמענ זיכער די נאָרמאַל אַפּעריישאַנז. אויב דיין באַקאַפּ פאָר איז פול, איר קען דאַרפֿן צו באַקאַפּ צו אַ צייַטווייַליק נעץ טיילן אָדער אַ נאַל מיטל (העכסט דיסקערידזשד סייַדן די דאַטאַבייס איז לייכט רעפּראָדוסיבלע, ווייַל עס ברייקס די לאָג קייט):

-- WARNING: This breaks the log chain and compromises point-in-time recovery.
-- Only use if absolutely necessary and follow immediately with a FULL backup.
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>;

באַמערקונג: מאָרד אַ מאַסיוו טראַנסאַקציע וועט צינגל אַ ראָלל-באַק, וואָס קען נעמען אַ באַטייטיק סומע פון צייט און וועט טעמפּערעראַלי דזשענערייט נאָך לאָג טעטיקייט. דו זאלסט נישט ריסטאַרט די 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. Take a log backup to truncate the log
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Empty the temporary log file
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Remove the temporary log file
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

בעסטער פּראַקטיסיז פֿאַר טראַנסאַקציע לאָג פאַרהיטונג און פאַרוואַלטונג

ריאַקטיוו טראָובלעשאָאָטינג איז סטרעספאַל און ימפּאַקץ SLAs. ימפּלאַמענטינג פּראָאַקטיוו אַרכיטעקטוראַל און אַפּעריישאַנאַל בעסטער פּראַקטיסיז איז יקערדיק פֿאַר פאַרנעמונג דאַטאַבייס פעסטקייַט.

1. ימפּלאַמענט אַ ראָובאַסט, אָטאַמייטיד באַקאַפּ סטראַטעגיע

אויב אַ דאַטאַבייס איז אין די פול רעקאָווערי מאָדעל, אָפט טראַנסאַקציע לאָג באַקאַפּס זענען מאַנדאַטאָרי. דעפּענדינג אויף דיין Recovery Point Objective (RPO) און טראַנסאַקציע באַנד, לאָג באַקאַפּס זאָל פּאַסירן יעדער 5 צו 15 מינוט.

פאַרנעמונג באַקאַפּ סאַלושאַנז ווי CloudSave סימפּלאַפיי דעם פּראָצעס באטייטיק. דורך ינטאַגרייטינג גלייַך מיט SQL Server דורך VDI (Virtual Device Interface), CloudSave אַלאַוז DBAs צו קאַנפיגיער פּאָליטיק-געטריבן, הויך-אָפטקייַט טראַנסאַקציע לאָג באַקאַפּס. דאָס ינשורז לאָגס זענען קאַנטיניואַסלי טראַנקייטיד, סיקיורלי ינקריפּטיד, און סטאָרד אַוועק-פּלאַץ אָדער אין ימיוטאַבאַל וואָלקן סטאָרידזש, פּרעווענטינג די LOG_BACKUP וואַרטן שטאַט אָן ריקוויירינג קאָמפּלעקס מנהג SQL Agent דזשאָבס.

2. רעכט-גרייס די טראַנסאַקציע לאָג און פירן VLFs

רילייינג אויף אַוטאָ-גראָוט צו פירן דיין טראַנסאַקציע לאָג גרייס איז אַ געפערלעך אַנטי-מוסטער. אַוטאָ-גראָוט אַפּעריישאַנז זענען טייַער און פּויזע טראַנסאַקציע פּראַסעסינג בשעת די דיסק איז נול-יניטיאַלייזד (סייַדן Instant File Initialization איז ענייבאַלד, וואָס טוט נישט צולייגן צו לאָג טעקעס).

דערצו, אָפט, קליין אַוטאָ-גראָוטס (למשל, גראָוינג מיט 10% אָדער 50MB אין אַ צייט) פירן צו VLF פראַגמאַנטיישאַן. אַ טראַנסאַקציע לאָג מיט טויזנטער פון קליינטשיק VLFs וועט שטרענג פאַרערגערן דאַטאַבייס סטאַרטאַפּ צייט, באַקאַפּ פאָרשטעלונג, און רעפּליקאַטיאָן לייטאַנסי.

  • פאַר-גרייס די לאָג: אַנאַלייז דיין גרעסטע וישאַלט אַפּעריישאַנז (ווי אינדעקס ריבילדז) און פאַר-גרייס די LDF טעקע צו אַקאַמאַדייט זיי אָן גראָוינג.
  • שטעלן פאַרפעסטיקט אַוטאָ-גראָוט: טוישן אַוטאָ-גראָוט פון אַ פּראָצענט צו אַ פאַרפעסטיקט גרייס (למשל, 1GB אָדער 5GB) צו ענשור אַז VLFs זענען באשאפן אין אַ געזונט גרייס.

איר קענט קאָנטראָלירן דיין 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, רידוסינג די מאַסע אויף די באַניצער דאַטאַבייס ס טראַנסאַקציע לאָג.
* באַשטימען פון אינדעקס ריבילדז צו אינדעקס רעאָרגאַניזעס ווו מעגלעך, ווייַל רעאָרגאַניזיישאַנז זענען מער לאָג-עפעקטיוו און קענען זיין ינטעראַפּטיד אָן ראָולינג צוריק די גאנצע אָפּעראַציע.
* פּעקל גרויס DELETE אָדער UPDATE אַפּעריישאַנז. אַנשטאָט פון דיליטינג 10 מיליאָן ראָוז אין איין טראַנסאַקציע, ויסמעקן זיי אין טשאַנגקס פון 50,000, קאַמיטינג און אַלאַוינג לאָג באַקאַפּס צו טראַנקייט די לאָג צווישן באַטשאַז.

4. מאָניטאָר הויך אַוויילאַביליטי און רעפּליקאַטיאָן טאָפּאָלאָגיעס

אין AlwaysOn Availability Groups, די ערשטיק רעפּליקאַ קען נישט טראַנקייט זייַן לאָג ביז די לאָג רעקאָרדס זענען כאַרדאַנד אויף אַלע סינכראַנאַס און אַסינכראַנאַס צווייטיק רעפּליקאַס.

אויב אַ צווייטיק רעפּליקאַ גייט אָפפלינע, אָדער אויב די נעץ באַנדווידט קען נישט האַלטן זיך מיט די ערשטיק ס טראַנסאַקציע דור קורס, די ערשטיק ס שיקן ריי וועט וואַקסן, און די לאָג וועט פּלאָמבירן אַרויף (AVAILABILITY_REPLICA וואַרטן טיפּ).

ימפּלאַמענט ראָובאַסט מאָניטאָרינג פֿאַר די SQLServer:Replica > Log Send Queue פאָרשטעלונג טאָמבאַנק. אויב אַ צווייטיק רעפּליקאַ איז פּערמאַנאַנטלי פאַרלאָרן, איר מוזן אַראָפּנעמען עס פון די Availability Group אָדער סוספּענד דאַטן באַוועגונג צו לאָזן די ערשטיק לאָג צו טראַנקייט.

מסקנא

טרעפן אַ פול טראַנסאַקציע לאָג איז אַ רייט פון דורכפאָר פֿאַר דאַטאַבייס אַדמיניסטראַטאָרס, אָבער עס טוט נישט האָבן צו רעזולטאַט אין עקסטענדעד דאַונטיים. דורך פאַרשטיין די מעכאַניקס פון Write-Ahead Logging און VLFs, איר קענען געשווינד דיאַגנאָזירן די וואָרצל סיבה ניצן sys.databases און צולייגן די ריכטיק גיך אָפּזוך סטראַטעגיע.

לאַנג-טערמין פעסטקייַט רילייז אויף מאָווינג אַוועק פון ריאַקטיוו פיקסיז. פאַר-גרייזינג דיין לאָג טעקעס, אָפּטימיזינג וישאַלט רוטינז, און ניצן פאַרנעמונג-מיינונג באַקאַפּ פּלאַטפאָרמס ווי CloudSave צו דורכפירן שטרענג, אָטאַמייטיד לאָג באַקאַפּ סקעדזשולז וועט ענשור דיין טראַנסאַקציע לאָגס בלייַבן געזונט, טראַנקייטיד, און גרייט צו שטיצן הויך-טרופּוט פּראָדוקציע ווערקלאָודז.

Categories