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.

Bagi Pentadbir Pangkalan Data (DBA) dan jurutera DevOps yang menguruskan Microsoft SQL Server, hanya sedikit amaran yang mencetuskan kebimbangan serta-merta seperti Ralat 9002: Log transaksi untuk pangkalan data ‘X’ penuh. Apabila log transaksi penuh dan tidak dapat berkembang, pangkalan data secara berkesan menjadi baca-sahaja (read-only). Semua operasi INSERT, UPDATE, dan DELETE terhenti, transaksi aplikasi gagal, dan pengeluaran tergendala sepenuhnya.

Memahami seni bina asas log transaksi SQL Server, mendiagnosis punca utama dengan tepat, dan melaksanakan prosedur pemulihan pantas adalah kemahiran kritikal untuk mengekalkan ketersediaan tinggi. Panduan komprehensif ini meneroka mekanik log transaksi, cara menyelesaikan log penuh dalam kecemasan, dan amalan terbaik seni bina untuk menghalangnya daripada berlaku lagi.

Memahami Seni Bina Log Transaksi SQL Server

Untuk menyelesaikan masalah log transaksi penuh dengan berkesan, anda mesti memahami terlebih dahulu cara SQL Server menulis dan mengurus data.

Log Tulis-Dahulu (Write-Ahead Logging – WAL)

SQL Server menggunakan protokol Log Tulis-Dahulu (WAL). Setiap kali pengubahsuaian data berlaku, perubahan tersebut ditulis terlebih dahulu ke dalam log transaksi dalam memori, kemudian disalurkan ke fail log fizikal pada cakera sebelum halaman data sebenar dikemas kini dalam fail pangkalan data (MDF/NDF). Ini menjamin pematuhan ACID (Atomicity, Consistency, Isolation, Durability), memastikan bahawa sekiranya berlaku kerosakan, SQL Server boleh memainkan semula (roll forward) atau membatalkan (roll back) transaksi.

Fail Log Maya (VLFs) dan Pengelogan Pekeliling

Secara dalaman, fail log transaksi fizikal (LDF) dibahagikan kepada segmen logik yang lebih kecil yang dipanggil Fail Log Maya (VLF). Log transaksi beroperasi secara pekeliling. Apabila rekod log ditulis, ia mengisi satu VLF dan beralih ke yang seterusnya.

Apabila log mencapai penghujung fail fizikal, ia cuba berpusing kembali ke permulaan. Walau bagaimanapun, ia hanya boleh menulis ganti VLF jika VLF tersebut ditandakan sebagai tidak aktif. Jika semua VLF aktif (bermaksud ia mengandungi rekod log yang masih diperlukan oleh SQL Server), log tidak boleh berpusing. Jika pertumbuhan automatik (auto-growth) didayakan dan ruang cakera tersedia, fail fizikal akan berkembang. Jika cakera penuh atau pertumbuhan automatik dihadkan, anda akan menghadapi Ralat 9002.

Pemotongan Log (Truncation) lwn. Pengecilan Log (Shrinking)

Salah tanggapan umum ialah memotong log akan mengurangkan saiz fail fizikal.
* Pemotongan Log: Proses menandakan VLF aktif sebagai tidak aktif, menjadikan ruang tersedia untuk digunakan semula. Ia tidak mengurangkan saiz fail LDF pada cakera.
* Pengecilan Log: Proses mengurangkan saiz fail LDF secara fizikal dan mengembalikan ruang kepada sistem pengendalian.

Dalam model Pemulihan Penuh (Full Recovery), pemotongan log hanya berlaku apabila sandaran log transaksi berjaya diselesaikan (dengan andaian tiada proses lain yang memegang log tersebut sebagai aktif).

Mendiagnosis Ralat “Log Transaksi Penuh” (Ralat 9002)

Apabila log penuh, langkah pertama anda bukanlah menambah ruang cakera atau mengecilkan fail secara membuta tuli. Anda mesti mengenal pasti mengapa log tidak boleh dipotong. SQL Server menyediakan mekanisme terbina dalam untuk memberitahu anda dengan tepat perkara yang menghalang penggunaan semula log melalui paparan katalog sys.databases.

Jalankan arahan T-SQL berikut untuk mengenal pasti kesesakan tersebut:

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

Anda juga boleh menyemak penggunaan ruang semasa log transaksi anda menggunakan:

DBCC SQLPERF(LOGSPACE);

Keadaan log_reuse_wait_desc yang Biasa

  1. LOG_BACKUP: Pangkalan data berada dalam model pemulihan Penuh atau Bulk-Logged, dan sandaran log transaksi tidak diambil baru-baru ini. Ini adalah punca yang paling biasa.
  2. ACTIVE_TRANSACTION: Transaksi yang berjalan lama (contohnya, pembinaan semula indeks yang besar atau transaksi yang belum disahkan yang terlupa) mengekalkan log sebagai aktif.
  3. REPLICATION / CDC: Replikasi Transaksi atau Tangkapan Data Perubahan (CDC) didayakan, dan Ejen Pembaca Log belum memproses transaksi tersebut.
  4. AVAILABILITY_REPLICA: Dalam Kumpulan Ketersediaan AlwaysOn, replika sekunder terputus sambungan atau menyegerak terlalu perlahan, memaksa replika utama mengekalkan rekod log sehingga ia dikukuhkan pada replika sekunder.

Strategi Pemulihan Pantas: Menyelesaikan Isu dalam Pengeluaran

Bergantung pada log_reuse_wait_desc yang dikembalikan, respons kecemasan anda akan berbeza. Berikut adalah strategi pemulihan pantas untuk senario yang paling biasa.

Senario 1: Sandaran Log Hilang atau Gagal (LOG_BACKUP)

Jika jenis tunggu adalah LOG_BACKUP, penyelesaiannya mudah: anda mesti menyandarkan log transaksi.

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

Setelah sandaran selesai, VLF yang tidak aktif akan dipotong, dan SQL Server akan menyambung semula operasi biasa. Jika pemacu sandaran anda penuh, anda mungkin perlu menyandarkan ke perkongsian rangkaian sementara atau peranti nol (sangat tidak digalakkan melainkan pangkalan data mudah dihasilkan semula, kerana ia memutuskan rantaian log):

-- AMARAN: Ini memutuskan rantaian log dan menjejaskan pemulihan titik masa.
-- Hanya gunakan jika benar-benar perlu dan ikuti serta-merta dengan sandaran PENUH.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Senario 2: Transaksi Aktif yang Berjalan Lama (ACTIVE_TRANSACTION)

Jika satu transaksi telah berjalan selama berjam-jam, ia menghalang pemotongan log sepanjang tempoh tersebut. Pertama, kenal pasti transaksi yang bermasalah:

DBCC OPENTRAN('YourDatabaseName');

Arahan ini mengembalikan transaksi aktif tertua dan ID Proses Pelayan (SPID) miliknya. Anda boleh mengumpulkan butiran lanjut tentang perkara yang dilakukan oleh SPID tersebut dengan membuat pertanyaan kepada paparan pengurusan dinamik (DMV):

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

Jika transaksi tersebut adalah pertanyaan penyangak atau proses yang terhenti, anda mungkin perlu menamatkannya untuk membebaskan log.

KILL <SPID>;

Nota: Mematikan transaksi yang besar akan mencetuskan rollback, yang boleh mengambil masa yang lama dan akan menjana aktiviti log tambahan buat sementara waktu. Jangan mulakan semula perkhidmatan SQL Server semasa rollback, atau pangkalan data akan memasuki mod pemulihan apabila dimulakan semula.

Senario 3: Peruntukan Ruang Kecemasan (Cakera 100% Penuh)

Jika fail LDF telah menggunakan keseluruhan pemacu, anda tidak boleh menjalankan sandaran kerana SQL Server memerlukan sedikit ruang log untuk merekodkan acara sandaran itu sendiri. Dalam senario ini, anda mesti menambah fail log sekunder pada pemacu berbeza yang mempunyai ruang tersedia.

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

Ini serta-merta memberikan SQL Server ruang untuk bernafas. Setelah pangkalan data dalam talian, ambil sandaran log transaksi, kosongkan fail log sekunder, dan alih keluarnya:

-- 1. Ambil sandaran log untuk memotong log
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Kosongkan fail log sementara
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Alih keluar fail log sementara
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Amalan Terbaik untuk Pencegahan dan Pengurusan Log Transaksi

Penyelesaian masalah reaktif adalah membebankan dan menjejaskan SLA. Melaksanakan amalan terbaik seni bina dan operasi yang proaktif adalah penting untuk kestabilan pangkalan data perusahaan.

1. Laksanakan Strategi Sandaran Automatik yang Teguh

Jika pangkalan data berada dalam model pemulihan Penuh, sandaran log transaksi yang kerap adalah wajib. Bergantung pada Objektif Titik Pemulihan (RPO) dan volum transaksi anda, sandaran log harus berlaku setiap 5 hingga 15 minit.

Penyelesaian sandaran perusahaan seperti CloudSave memudahkan proses ini dengan ketara. Dengan menyepadukan terus dengan SQL Server melalui VDI (Antara Muka Peranti Maya), CloudSave membolehkan DBA mengkonfigurasi sandaran log transaksi berfrekuensi tinggi yang dipacu oleh polisi. Ini memastikan log dipotong secara berterusan, disulitkan dengan selamat, dan disimpan di luar tapak atau dalam storan awan yang tidak boleh diubah, menghalang keadaan tunggu LOG_BACKUP tanpa memerlukan kerja Ejen SQL tersuai yang kompleks.

2. Saizkan Log Transaksi dengan Betul dan Urus VLF

Bergantung pada pertumbuhan automatik untuk mengurus saiz log transaksi anda adalah corak anti yang berbahaya. Operasi pertumbuhan automatik adalah mahal dan menjeda pemprosesan transaksi semasa cakera dimulakan sifar (kecuali jika Permulaan Fail Segera didayakan, yang tidak terpakai pada fail log).

Tambahan pula, pertumbuhan automatik yang kerap dan kecil (contohnya, berkembang sebanyak 10% atau 50MB pada satu masa) membawa kepada pemecahan VLF. Log transaksi dengan beribu-ribu VLF kecil akan merendahkan masa permulaan pangkalan data, prestasi sandaran, dan kependaman replikasi dengan teruk.

  • Pra-saiz log: Analisis operasi penyelenggaraan terbesar anda (seperti pembinaan semula indeks) dan pra-saiz fail LDF untuk menampungnya tanpa berkembang.
  • Tetapkan pertumbuhan automatik tetap: Tukar pertumbuhan automatik daripada peratusan kepada saiz tetap (contohnya, 1GB atau 5GB) untuk memastikan VLF dicipta pada saiz yang sihat.

Anda boleh menyemak kiraan VLF anda menggunakan pertanyaan berikut (untuk 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'));

Jika kiraan VLF anda melebihi 500, pertimbangkan untuk menunggu tempoh yang tenang, mengecilkan log kepada saiz minimum, dan membesarkannya semula secara manual kepada saiz yang diperlukan dalam ketulan besar.

3. Optimumkan Operasi Penyelenggaraan Indeks

Pembinaan semula indeks adalah operasi yang direkodkan sepenuhnya, walaupun dalam model pemulihan Bulk-Logged (bergantung pada jenis indeks). Membina semula indeks 500GB akan menjana sekurang-kurangnya 500GB rekod log transaksi.

Untuk mengurangkan pembengkakan log semasa penyelenggaraan:
* Gunakan SORT_IN_TEMPDB = ON apabila membina semula indeks. Ini memindahkan fasa pengisihan ke TempDB, mengurangkan beban pada log transaksi pangkalan data pengguna.
* Tukar daripada pembinaan semula indeks kepada penyusunan semula indeks jika boleh, kerana penyusunan semula lebih cekap log dan boleh diganggu tanpa membatalkan keseluruhan operasi.
* Kelompokkan operasi DELETE atau UPDATE yang besar. Daripada memadam 10 juta baris dalam satu transaksi, padamkannya dalam kelompok 50,000, melakukan pengesahan dan membenarkan sandaran log memotong log di antara kelompok.

4. Pantau Topologi Ketersediaan Tinggi dan Replikasi

Dalam Kumpulan Ketersediaan AlwaysOn, replika utama tidak boleh memotong lognya sehingga rekod log telah dikukuhkan pada semua replika sekunder segerak dan tidak segerak.

Jika replika sekunder terputus talian, atau jika lebar jalur rangkaian tidak dapat mengikuti kadar penjanaan transaksi utama, baris gilir hantar utama akan berkembang, dan log akan penuh (jenis tunggu AVAILABILITY_REPLICA).

Laksanakan pemantauan yang teguh untuk pembilang prestasi SQLServer:Replica > Log Send Queue. Jika replika sekunder hilang secara kekal, anda mesti mengalih keluarnya daripada Kumpulan Ketersediaan atau menggantung pergerakan data untuk membenarkan log utama dipotong.

Kesimpulan

Menghadapi log transaksi penuh adalah satu ujian bagi pentadbir pangkalan data, tetapi ia tidak perlu mengakibatkan masa henti yang berpanjangan. Dengan memahami mekanik Log Tulis-Dahulu dan VLF, anda boleh mendiagnosis punca utama dengan cepat menggunakan sys.databases dan menggunakan strategi pemulihan pantas yang betul.

Kestabilan jangka panjang bergantung pada beralih daripada pembetulan reaktif. Pra-saiz fail log anda, optimumkan rutin penyelenggaraan, dan gunakan platform sandaran gred perusahaan seperti CloudSave untuk menguatkuasakan jadual sandaran log automatik yang ketat akan memastikan log transaksi anda kekal sihat, dipotong, dan bersedia untuk menyokong beban kerja pengeluaran berdaya pemprosesan tinggi.

Kategori