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.

Ar gyfer Gweinyddwyr Cronfeydd Data (DBAs) a pheirianwyr DevOps sy’n rheoli Microsoft SQL Server, ychydig o rybuddion sy’n achosi cymaint o bryder ar unwaith â Gwall 9002: The transaction log for database ‘X’ is full. Pan fydd y log trafodion yn llenwi ac na all dyfu, mae’r gronfa ddata yn dod yn un darllen-yn-unig i bob pwrpas. Mae pob gweithred INSERT, UPDATE, a DELETE yn dod i ben, mae trafodion cymhwysiad yn methu, ac mae cynhyrchiant yn dod i stop.

Mae deall pensaernïaeth sylfaenol log trafodion SQL Server, diagnosio’r achos gwreiddiol yn gywir, a gweithredu gweithdrefnau adfer cyflym yn sgiliau hanfodol ar gyfer cynnal argaeledd uchel. Mae’r canllaw cynhwysfawr hwn yn archwilio mecaneg y log trafodion, sut i ddatrys log llawn mewn argyfwng, ac arferion gorau pensaernïol i’w atal rhag digwydd eto.

Deall Pensaernïaeth Log Trafodion SQL Server

Er mwyn datrys problem log trafodion llawn yn effeithiol, rhaid i chi ddeall yn gyntaf sut mae SQL Server yn ysgrifennu ac yn rheoli data.

Logio Ysgrifennu-Ymlaen (WAL)

Mae SQL Server yn defnyddio protocol Logio Ysgrifennu-Ymlaen (WAL). Pryd bynnag y bydd addasiad data yn digwydd, mae’r newid yn cael ei ysgrifennu gyntaf i’r log trafodion yn y cof, yna’n cael ei fflysio i’r ffeil log ffisegol ar y ddisg cyn i’r tudalennau data gwirioneddol gael eu diweddaru yn y ffeiliau cronfa ddata (MDF/NDF). Mae hyn yn gwarantu cydymffurfiaeth ACID (Atomicity, Consistency, Isolation, Durability), gan sicrhau, rhag ofn y bydd chwalfa, y gall SQL Server ailchwarae (roll forward) neu ddadwneud (roll back) trafodion.

Ffeiliau Log Rhithwir (VLFs) a Logio Cylchol

Yn fewnol, mae’r ffeil log trafodion ffisegol (LDF) wedi’i rhannu’n segmentau llai, rhesymegol o’r enw Ffeiliau Log Rhithwir (VLFs). Mae’r log trafodion yn gweithredu’n gylchol. Wrth i gofnodion log gael eu hysgrifennu, maent yn llenwi un VLF ac yn symud i’r nesaf.

Pan fydd y log yn cyrraedd diwedd y ffeil ffisegol, mae’n ceisio lapio o amgylch i’r dechrau. Fodd bynnag, dim ond os yw VLF wedi’i farcio fel anweithredol y gall drosysgrifennu VLF. Os yw pob VLF yn weithredol (sy’n golygu eu bod yn cynnwys cofnodion log sydd eu hangen o hyd gan SQL Server), ni all y log lapio. Os yw auto-growth wedi’i alluogi ac mae lle ar y ddisg ar gael, mae’r ffeil ffisegol yn tyfu. Os yw’r ddisg yn llawn neu os yw auto-growth wedi’i gyfyngu, byddwch yn dod ar draws Gwall 9002.

Torri Log vs. Crebachu Log

Camdybiaeth gyffredin yw bod torri’r log yn lleihau maint y ffeil ffisegol.
* Torri Log (Log Truncation): Y broses o farcio VLFs gweithredol fel rhai anweithredol, gan wneud y lle ar gael i’w ailddefnyddio. Nid yw’n lleihau maint y ffeil LDF ar y ddisg.
* Crebachu Log (Log Shrinking): Y broses o leihau maint y ffeil LDF yn ffisegol a dychwelyd lle i’r system weithredu.

Yn y model Adfer Llawn (Full Recovery), dim ond pan fydd copi wrth gefn o’r log trafodion wedi’i gwblhau’n llwyddiannus y mae torri log yn digwydd (gan dybio nad oes prosesau eraill yn cadw’r log yn weithredol).

Diagnosio’r Gwall “Transaction Log Full” (Gwall 9002)

Pan fydd y log yn llawn, eich cam cyntaf nid yw’n ychwanegu lle ar y ddisg neu grebachu ffeiliau yn ddall. Rhaid i chi nodi pam na all y log dorri. Mae SQL Server yn darparu mecanwaith adeiledig i ddweud wrthych yn union beth sy’n atal ailddefnyddio log trwy’r olygfa catalog sys.databases.

Rhedwch y gorchymyn T-SQL canlynol i nodi’r tagfa:

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

Gallwch hefyd wirio defnydd lle cyfredol eich logiau trafodion gan ddefnyddio:

DBCC SQLPERF(LOGSPACE);

Cyflyrau log_reuse_wait_desc Cyffredin

  1. LOG_BACKUP: Mae’r gronfa ddata yn y model adfer Llawn neu Bulk-Logged, ac nid yw copi wrth gefn o’r log trafodion wedi’i gymryd yn ddiweddar. Dyma’r achos mwyaf cyffredin.
  2. ACTIVE_TRANSACTION: Mae trafodiad hir-redeg (e.e., ailadeiladu mynegai enfawr neu drafodiad heb ei ymrwymo sydd wedi’i anghofio) yn cadw’r log yn weithredol.
  3. REPLICATION / CDC: Mae Replication Trafodion neu Change Data Capture (CDC) wedi’i alluogi, ac nid yw’r Log Reader Agent wedi prosesu’r trafodion eto.
  4. AVAILABILITY_REPLICA: Mewn Grŵp Argaeledd AlwaysOn, mae replica eilaidd wedi’i ddatgysylltu neu’n cysoni’n rhy araf, gan orfodi’r replica cynradd i gadw cofnodion log nes eu bod wedi’u caledu ar yr eilaidd.

Strategaethau Adfer Cyflym: Datrys y Broblem mewn Cynhyrchiad

Yn dibynnu ar y log_reuse_wait_desc a ddychwelwyd, bydd eich ymateb brys yn amrywio. Dyma’r strategaethau adfer cyflym ar gyfer y senarios mwyaf cyffredin.

Senario 1: Copïau Wrth Gefn Log ar Goll neu’n Methu (LOG_BACKUP)

Os yw’r math aros yn LOG_BACKUP, mae’r ateb yn syml: rhaid i chi wneud copi wrth gefn o’r log trafodion.

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

Unwaith y bydd y copi wrth gefn yn cwblhau, bydd y VLFs anweithredol yn cael eu torri, a bydd SQL Server yn ailddechrau gweithrediadau arferol. Os yw eich gyriant copi wrth gefn yn llawn, efallai y bydd angen i chi wneud copi wrth gefn i gyfran rhwydwaith dros dro neu ddyfais null (wedi’i anghalonogi’n gryf oni bai bod y gronfa ddata yn hawdd i’w hatgynhyrchu, gan ei fod yn torri’r gadwyn log):

-- RHYBUDD: Mae hyn yn torri'r gadwyn log ac yn peryglu adferiad pwynt-mewn-amser.
-- Defnyddiwch dim ond os yw'n gwbl angenrheidiol a dilynwch ar unwaith gyda chopi wrth gefn LLAWN.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Senario 2: Trafodion Gweithredol Hir-redeg (ACTIVE_TRANSACTION)

Os yw trafodiad sengl wedi bod yn rhedeg am oriau, mae’n atal torri log am yr holl gyfnod. Yn gyntaf, nodwch y trafodiad sy’n achosi’r broblem:

DBCC OPENTRAN('YourDatabaseName');

Mae’r gorchymyn hwn yn dychwelyd y trafodiad gweithredol hynaf a’i ID Proses Gweinydd (SPID). Gallwch gasglu mwy o fanylion am yr hyn y mae’r SPID yn ei wneud trwy holi golygfeydd rheoli deinamig (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>;

Os yw’r trafodiad yn ymholiad twyllodrus neu’n broses sydd wedi aros, efallai y bydd angen i chi ei derfynu i ryddhau’r log.

KILL <SPID>;

Nodyn: Bydd lladd trafodiad enfawr yn sbarduno rholio yn ôl (rollback), a all gymryd cryn dipyn o amser a bydd yn cynhyrchu gweithgaredd log ychwanegol dros dro. Peidiwch ag ailgychwyn gwasanaeth SQL Server yn ystod rholio yn ôl, neu bydd y gronfa ddata yn mynd i mewn i’r modd adfer wrth ailgychwyn.

Senario 3: Dyraniad Lle Argyfwng (Disg yn 100% Llawn)

Os yw’r ffeil LDF wedi bwyta’r gyriant cyfan, ni allwch hyd yn oed redeg copi wrth gefn oherwydd bod SQL Server angen ychydig bach o le log i gofnodi’r digwyddiad copi wrth gefn ei hun. Yn y senario hwn, rhaid i chi ychwanegu ffeil log eilaidd ar yriant gwahanol gyda lle ar gael.

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

Mae hyn yn darparu lle anadlu i SQL Server ar unwaith. Unwaith y bydd y gronfa ddata ar-lein, cymerwch gopi wrth gefn o’r log trafodion, gwagiwch y ffeil log eilaidd, a’i thynnu:

-- 1. Cymerwch gopi wrth gefn o'r log i dorri'r log
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Gwagiwch y ffeil log dros dro
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Tynnwch y ffeil log dros dro
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Arferion Gorau ar gyfer Atal a Rheoli Log Trafodion

Mae datrys problemau adweithiol yn llawn straen ac yn effeithio ar SLAs. Mae gweithredu arferion gorau pensaernïol a gweithredol rhagweithiol yn hanfodol ar gyfer sefydlogrwydd cronfa ddata menter.

1. Gweithredu Strategaeth Copi Wrth Gefn Awtomataidd a Chadarn

Os yw cronfa ddata yn y model adfer Llawn, mae copïau wrth gefn o’r log trafodion yn orfodol. Yn dibynnu ar eich Amcan Pwynt Adfer (RPO) a chyfaint trafodion, dylai copïau wrth gefn o’r log ddigwydd bob 5 i 15 munud.

Mae datrysiadau copi wrth gefn menter fel CloudSave yn symleiddio’r broses hon yn sylweddol. Trwy integreiddio’n uniongyrchol â SQL Server trwy VDI (Rhyngwyneb Dyfais Rhithwir), mae CloudSave yn caniatáu i DBAs ffurfweddu copïau wrth gefn o’r log trafodion sy’n cael eu gyrru gan bolisi ac amlder uchel. Mae hyn yn sicrhau bod logiau’n cael eu torri’n barhaus, eu hamgryptio’n ddiogel, a’u storio oddi ar y safle neu mewn storfa cwmwl annewidiol, gan atal y cyflwr aros LOG_BACKUP heb fod angen swyddi SQL Agent personol cymhleth.

2. Maint Cywir y Log Trafodion a Rheoli VLFs

Mae dibynnu ar auto-growth i reoli maint eich log trafodion yn batrwm gwrth-beryglus. Mae gweithrediadau auto-growth yn ddrud ac yn oedi prosesu trafodion tra bod y ddisg yn cael ei gychwyn i sero (oni bai bod Instant File Initialization wedi’i alluogi, nad yw’n berthnasol i ffeiliau log).

Ar ben hynny, mae auto-growths bach ac aml (e.e., tyfu 10% neu 50MB ar y tro) yn arwain at ffragmentiad VLF. Bydd log trafodion gyda miloedd o VLFs bach yn diraddio amseroedd cychwyn cronfa ddata, perfformiad copi wrth gefn, a hwyrni replikasiwn yn ddifrifol.

  • Rhag-feintio’r log: Dadansoddwch eich gweithrediadau cynnal a chadw mwyaf (fel ailadeiladu mynegai) a rhag-feintiwch y ffeil LDF i’w lletya heb dyfu.
  • Gosod auto-growth sefydlog: Newidiwch auto-growth o ganran i faint sefydlog (e.e., 1GB neu 5GB) i sicrhau bod VLFs yn cael eu creu ar faint iach.

Gallwch wirio eich cyfrif VLF gan ddefnyddio’r ymholiad canlynol (ar gyfer 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'));

Os yw eich cyfrif VLF dros 500, ystyriwch aros am gyfnod tawel, crebachu’r log i faint lleiaf, a’i dyfu’n ôl â llaw i’w faint gofynnol mewn talpiau mawr.

3. Optimeiddio Gweithrediadau Cynnal a Chadw Mynegai

Mae ailadeiladu mynegai yn weithrediadau sydd wedi’u logio’n llawn, hyd yn oed yn y model adfer Bulk-Logged (yn dibynnu ar y math o fynegai). Bydd ailadeiladu mynegai 500GB yn cynhyrchu o leiaf 500GB o gofnodion log trafodion.

I liniaru chwydd log yn ystod cynnal a chadw:
* Defnyddiwch SORT_IN_TEMPDB = ON wrth ailadeiladu mynegai. Mae hyn yn dadlwytho’r cam didoli i TempDB, gan leihau’r baich ar log trafodion y gronfa ddata defnyddiwr.
* Newidiwch o ailadeiladu mynegai i ail-drefnu mynegai lle bo modd, gan fod ail-drefnu yn fwy effeithlon o ran log a gellir ei dorri ar draws heb rolio yn ôl yr holl weithrediad.
* Gweithrediadau DELETE neu UPDATE mawr swp. Yn lle dileu 10 miliwn o resi mewn un trafodiad, dilewch nhw mewn talpiau o 50,000, gan ymrwymo a chaniatáu i gopïau wrth gefn o’r log dorri’r log rhwng y swpiau.

4. Monitro Topolegau Argaeledd Uchel a Replikasiwn

Mewn Grwpiau Argaeledd AlwaysOn, ni all y replica cynradd dorri ei log nes bod y cofnodion log wedi’u caledu ar bob replica eilaidd cydamserol ac anghydamserol.

Os yw replica eilaidd yn mynd all-lein, neu os na all lled band y rhwydwaith gadw i fyny â chyfradd cynhyrchu trafodion y cynradd, bydd ciw anfon y cynradd yn tyfu, a bydd y log yn llenwi (math aros AVAILABILITY_REPLICA).

Gweithredwch fonitro cadarn ar gyfer y cownter perfformiad SQLServer:Replica > Log Send Queue. Os yw replica eilaidd wedi’i golli’n barhaol, rhaid i chi ei dynnu o’r Grŵp Argaeledd neu atal symudiad data i ganiatáu i’r log cynradd dorri.

Casgliad

Mae dod ar draws log trafodion llawn yn rhan o brofiad gweinyddwyr cronfeydd data, ond nid oes rhaid iddo arwain at amser segur estynedig. Trwy ddeall mecaneg Logio Ysgrifennu-Ymlaen a VLFs, gallwch ddiagnosio’r achos gwreiddiol yn gyflym gan ddefnyddio sys.databases a chymhwyso’r strategaeth adfer cyflym gywir.

Mae sefydlogrwydd hirdymor yn dibynnu ar symud i ffwrdd o atebion adweithiol. Bydd rhag-feintio eich ffeiliau log, optimeiddio arferion cynnal a chadw, a defnyddio llwyfannau copi wrth gefn gradd menter fel CloudSave i orfodi amserlenni copi wrth gefn log llym ac awtomataidd yn sicrhau bod eich logiau trafodion yn parhau’n iach, wedi’u torri, ac yn barod i gefnogi llwythi gwaith cynhyrchu trwybwn uchel.

Categorïau