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.

Database Administrators (DBAs) hinallataq DevOps ingenierokunapaq, Microsoft SQL Server kamachiqkunapaqqa, manchay sasachakuymi Error 9002 nisqa: The transaction log for database ‘X’ is full. Transaction log hunt’aykuptinqa, manañataqmi wiñayta atinchu, chaymi databaseqa read-only nisqaman tukun. Tukuy INSERT, UPDATE, hinallataq DELETE ruwaykunam sayan, mañakuykunapas (application transactions) manam purinchu, chaymi llapa ruwaykuna sayaykun.

SQL Server transaction logpa imayna ruwasqa kasqanta yachayqa, imarayku chay sasachakuy kasqanta allinta riqsiyqa, hinallataq utqaylla allichayqa ancha chaniyuqmi allin puriyta (high availability) waqaychanapaq. Kay qillqasqam yachachisunki transaction log imayna kasqanta, imaynatam utqaylla allichawaq, hinallataq imaynatam harkawaq yapamanta mana kananpaq.

SQL Server Transaction Logpa imayna kasqanta yachay

Transaction log hunt’asqa kaptin allichanapaqqa, ñawpaqtaqa yachanaykim imaynatam SQL Server qillqan hinallataq kamachin willañiqikunata.

Write-Ahead Logging (WAL)

SQL Serverqa Write-Ahead Logging (WAL) nisqatam llamk’achin. Imapas k’askachikuptinqa, chaymi ñawpaqta transaction logman qillqakun, chaymantam diskman (physical log file) apakun, chaymantallam database willañiqikunapi (MDF/NDF) musuqyachikun. Kaymi ACID (Atomicity, Consistency, Isolation, Durability) nisqata hunt’an, chaymi SQL Serverqa sasachakuy kaptinpas (crash) imayna kasqanman kutichiyta atin.

Virtual Log Files (VLFs) hinallataq Circular Logging

Ukhunpiqa, physical transaction log (LDF) nisqaqa huch’uy segmentokunamanmi rakiykun, chaykunatam Virtual Log Files (VLFs) ninku. Transaction logqa muyuynintam purin. Log qillqakuna hunt’aptinqa, huk VLFmanta hukmanmi purin.

Log physical willañiqipa tukukuyninman chayaqtinqa, qallariymanmi kutiyta munan. Ichaqa, huk VLFtaqa musuqmanta qillqayta atinman inactive (mana llamk’achisqa) kaptillanmi. Sichus llapa VLFkuna active kachkaptinqa, logqa manam kutiyta atinchu. Sichus auto-growth kichasqa kachkan hinallataq diskpi kachkan chayqa, physical willañiqim wiñan. Sichus disk hunt’asqa kachkan utaq auto-growth harkasqa kachkan chayqa, Error 9002 nisqatam tarinki.

Log Truncation vs. Log Shrinking

Huk pantay yuyaymi log truncation nisqa physical willañiqipa hatun kayninta pisiyachinmi nispa.
* Log Truncation: Active VLFkunata inactiveman tukuchiy, chaywantaq musuqmanta llamk’achiyta atikun. Kayqa manan LDF willañiqipa hatun kaynintachu pisiyachin.
* Log Shrinking: LDF willañiqipa hatun kayninta pisiyachiy hinallataq diskpi kachkaq rakiykunata kutichiy.

Full Recovery modelpiqa, log truncation chayllaraqmi ruwakun transaction log backup allinta ruwakuptin (mana huk ruwaykuna harkaptinqa).

“Transaction Log Full” (Error 9002) sasachakuyta riqsiy

Log hunt’asqa kaptinqa, ama utqaylla diskta yapaychu nitaq willañiqikunata pisiyachiychu. Ñawpaqtaqa yachanaykim imarayku log mana truncayta atisqanta. SQL Serverqa sys.databases catalog view nisqawanmi willasunki imarayku harkasqa kachkasqanta.

Kay T-SQL kamachiyta ruway imarayku harkasqa kasqanta yachanaykipaq:

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

Transaction logkuna imayna kachkasqantapas kaywanmi qhawawaq:

DBCC SQLPERF(LOGSPACE);

log_reuse_wait_desc nisqapa riqsisqa kasqankuna

  1. LOG_BACKUP: Databaseqa Full utaq Bulk-Logged recovery modelpim kachkan, hinallataq manam transaction log backup ruwakunchu. Kaymi aswan riqsisqa imarayku hunt’asqa kasqan.
  2. ACTIVE_TRANSACTION: Unayña purichkaq transaction (ahinataq hatun index rebuild utaq mana tukusqa transaction) logta harkachkan.
  3. REPLICATION / CDC: Transactional Replication utaq Change Data Capture (CDC) kichasqam kachkan, hinallataq Log Reader Agent manam llapa transactionkunata ruwanchu.
  4. AVAILABILITY_REPLICA: AlwaysOn Availability Group nisqapi, huk secondary replica harkasqa kachkan utaq pisi-pisimanta purichkan, chaymi primary replica log qillqakunata waqaychan secondarypi allin churasqa kanankama.

Utqaylla allichay: Sasachakuyta allichay

log_reuse_wait_desc nisqamanta qhawarispa, imayna allichayta akllanki. Kaykunam utqaylla allichanapaq ñankuna.

Scenario 1: Log Backups mana kasqan (LOG_BACKUP)

Sichus wait type nisqa LOG_BACKUP kachkan chayqa, allichayqa facilmi: transaction logta backup ruwanaykim.

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

Backup tukukuptinqa, inactive VLFkuna truncasqam kanqa, hinallataq SQL Serverqa normal puriyta qallarinqa. Sichus backup diskniyki hunt’asqa kachkan chayqa, huk network shareman utaq null device-man apayta atinki (mana allinchu, database facil-llata ruway atikuptillanmi, log chain nisqatam p’akin):

-- WARNING: Kayqa log chain nisqatam p'akin.
-- Sichus ancha chaniyuq kaptillan ruway, chaymantataq FULL backup ruway.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenario 2: Unayña purichkaq transaction (ACTIVE_TRANSACTION)

Sichus huk transaction unayña purichkan chayqa, log truncation nisqatam harkachkan. Ñawpaqtaqa, harkachkaq transactionta taripay:

DBCC OPENTRAN('YourDatabaseName');

Kay kamachiyqa ñawpaq kaq active transactionta hinallataq Server Process ID (SPID) nisqatam qispin. SPID imata ruwachkasqanta yachanaykipaqqa, kay DMVkuna (dynamic management views) llamk’achiy:

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>;

Sichus transactionqa mana allin query utaq sayasqa ruway kaptinqa, logta kacharichinaykipaqmi KILL kamachiyta ruwawaq.

KILL <SPID>;

Yuyaykuy: Hatun transactionta wañuchiyqa rollback nisqatam qallarin, chayqa unaymi kanman hinallataq logta yapamanta hunt’achinman. SQL Serverta ama restart ruwaychu rollback kachkaptin, mana chayqa databaseqa recovery mode nisqamanmi yaykunqa.

Scenario 3: Emergency Space Allocation (Disk 100% hunt’asqa)

Sichus LDF willañiqiyki llapa diskta hap’in chayqa, manam backup ruwaytapas atinkichu, SQL Serverqa pisi log space-tam munan backup ruwayta qillqanapaq. Kaypiqa, huk secondary log willañiqitam huk diskpi churanayki.

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

Kayqa SQL Serverta utqaylla kacharichin. Database kichasqa kaptinqa, transaction log backup ruway, secondary log willañiqita ch’usaqyachiy, hinallataq qichuy:

-- 1. Log backup ruway logta truncanapaq
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Temporary log willañiqita ch'usaqyachiy
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Temporary log willañiqita qichuy
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Transaction Logta allin kamachinapaq yuyaychaykuna

Sasachakuykuna kaptin allichayqa ancha sasam hinallataq SLAs nisqamanpas chayan. Allin kamachiykuna ruwayqa ancha chaniyuqmi database allin kananpaq.

1. Allin, Automated Backup Strategy ruway

Sichus database Full recovery modelpi kachkan chayqa, sapa kutim transaction log backup ruwanayki. RPO nisqaykiman hinallataq transaction volumenki manam, 5manta 15 minutokama log backup ruwanayki.

CloudSave hina enterprise backup ruwaykunaqa kayta facil-llatam ruwan. SQL Serverwan VDI (Virtual Device Interface) nisqawan k’askachikuspa, CloudSaveqa DBAs nisqakunata yanapan policy-driven, high-frequency transaction log backup ruwanankupaq. Kayqa logkuna sapa kutim truncasqa kananta, waqaychasqa kananta, hinallataq cloud storage-pi kananta qhawarin, chaymi LOG_BACKUP wait state nisqa mana kanqachu.

2. Transaction Logta allin hatun kayniyuqta churanayki hinallataq VLFkuna kamachiy

Auto-growth nisqallapi hapipakuyqa manam allinchu. Auto-growth ruwaykunaqa ancha unaymi hinallataq transaction puriyta harkanchu diskta ch’usaqyachispa.

Chaymantapas, sapa kutim huch’uy auto-growth (ahinataq 10% utaq 50MB) ruwayqa VLF fragmentation nisqamanmi apan. Achka huch’uy VLFniyuq transaction logqa database qallariytam, backup ruwayta, hinallataq replication puriyta harkanchu.

  • Logta ñawpaqta hatunchay: Hatun ruwayniykikunata (ahinataq index rebuilds) qhawariy hinallataq LDF willañiqita chayman hina hatunchay.
  • Fixed auto-growth churanayki: Auto-growth nisqata percentage-manta fixed size-man (ahinataq 1GB utaq 5GB) tikray, VLFkuna allin hatun kayniyuq kanankupaq.

VLF yupayniykita kaywan qhawawaq (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'));

Sichus VLF yupayniyki 500manta aswan kachkan chayqa, ch’usaq pachapi logta huch’uyyachiy, hinallataq yapamanta hatunchay.

3. Index Maintenance ruwaykunata allinchay

Index rebuilds nisqakunaqa llapa log nisqapim qillqakun, Bulk-Logged recovery modelpipas. 500GB index rebuild ruwayqa 500GB transaction log qillqakunatam ruwan.

Maintenance ruwaypi log hunt’ayta pisiyachinapaq:
* SORT_IN_TEMPDB = ON llamk’achiy index rebuild ruwaypi. Kayqa TempDB-manmi sorting ruwayta apan, user database logta kacharichispa.
* Index rebuilds nisqamanta index reorganizes nisqaman tikray, reorganizations nisqakunaqa aswan allinmi logpaq hinallataq mana llapa ruwayta rollback ruwanachu.
* Hatun DELETE utaq UPDATE ruwaykunata batch-pi ruway. 10 millon row-kunata huk kutillapi qichuyta munaspaqa, 50,000-kama batch-pi ruway, chaywantaq log backup truncayta atinqa.

4. High Availability hinallataq Replication Topologies qhawariy

AlwaysOn Availability Groups nisqapi, primary replicaqa manam logta truncayta atinchu llapa secondary replica-kunapi log qillqakuna allin churasqa kanankama.

Sichus secondary replica harkasqa kachkan, utaq network bandwidth mana primary transaction puriyta atinchu chayqa, primarypa send queue nisqam wiñanqa, hinallataq log hunt’anqa (AVAILABILITY_REPLICA wait type).

SQLServer:Replica > Log Send Queue performance counter nisqata qhawariy. Sichus secondary replica chinkasqa kachkan chayqa, Availability Group-manta qichunayki utaq data puriyta sayachinayki primary log truncayta atinaykipaq.

Tukuy

Transaction log hunt’ayqa database administrator-kunapaqqa huk sasachakuymi, ichaqa manam unay pachachu sayaykuna kanqa. Write-Ahead Logging hinallataq VLFkuna imayna kasqanta yachaspaqa, sys.databases nisqawan utqaylla sasachakuyta taripayta atinki hinallataq allichayta ruwayta atinki.

Allin puriyqa sasachakuykuna manaraq kaptin allichaymantam hamun. Log willañiqikunata ñawpaqta hatunchay, maintenance ruwaykunata allinchay, hinallataq CloudSave hina backup platformkunata llamk’achiyqa transaction logkuna allin, truncasqa, hinallataq hatun ruwaykunata yanapananpaqmi kanqa.

Categories