SQLShack

acest articol explorează utilizarea mai multor fișiere jurnal de tranzacții SQL Server și procesul de eliminare a fișierului jurnal de tranzacții secundare.

Introducere

în mod implicit, o bază de date SQL Server conține un fișier de date primare și fișierul jurnal de tranzacții. Este o bună practică configurarea mai multor fișiere de date și împărțirea datelor în mai multe fișiere de date. Putem avea aceste fișiere de date în unități de stocare separate pentru a avea mai multe IO. Ajută la gestionarea datelor, îmbunătățește performanța, planifică abordări de rezervă în funcție de grupurile de fișiere.

putem avea mai multe fișiere jurnal de tranzacții pentru o bază de date, de asemenea.

SQL Server utilizează mai multe fișiere jurnal de tranzacții în ordine secvențială. Să presupunem că avem două fișiere jurnal de tranzacții. SQL Server utilizează un fișier jurnal la un moment dat și odată ce este plin, SQL Server utilizează un alt fișier jurnal. Deoarece SQL Server nu îl folosește în paralel, nu obținem niciun beneficiu de performanță. În mod ideal, ar trebui să avem un singur fișier jurnal de tranzacții pe bază de date.

să presupunem că avem o bază de date de producție și primim o alertă critică că discul rămâne fără spațiu. După investigații, am aflat că această unitate conține un fișier jurnal de tranzacții. Datorită tranzacțiilor active, acest fișier jurnal de tranzacții SQL Server este plin. Am încercat să micșorăm fișierul jurnal, dar nu a funcționat. Am copie de rezervă Jurnalul de tranzacții, de asemenea,, dar din cauza tranzacțiilor active, nu a putut elibera spațiul necesar. Adăugarea unui alt fișier jurnal de tranzacții într-un disc separat cu spațiu liber va rezolva această problemă.

deoarece SQL Server utilizează modul serial pentru scrierea datelor într-un fișier jurnal de tranzacții, ar trebui să eliminăm ulterior fișierul jurnal suplimentar. Să explorăm procesul de eliminare a unui fișier jurnal suplimentar.

creați o bază de date cu mai multe fișiere jurnal de tranzacții SQL Server

Conectați-vă la o instanță SQL în SQL Server Management Studio. Faceți clic dreapta pe nodul baze de date din panoul Object Explorer și faceți clic pe comanda bază de date nouă:

se deschide fereastra bazei de date noi. Specificați un nume de bază de date și adăugați un alt fișier jurnal de tranzacții. În scopuri de testare, am dezactivat creșterea automată a fișierelor jurnal de tranzacții. Dimensiunea inițială a fișierului jurnal de tranzacții este de 8 MB:

după ce totul este setat, faceți clic pe butonul OK pentru a crea o bază de date într-un director implicit de fișiere de date/jurnal. Creați un tabel și introduceți date în:

Vizualizați starea fișierului jurnal virtual în SQL Server transaction log files

în articol, SQL Server Transaction Log Architecture, am explorat internul fișierului jurnal de tranzacții. Fiecare fișier jurnal de tranzacții este format din mai multe fișiere jurnal virtuale. Un fișier jurnal de tranzacții este o combinație de mai multe fișiere jurnal virtuale (VLF). Următoarea captură de ecran arată arhitectura fizică și logică a fișierului jurnal:

SQL Server stats o bază de date cu VLF minim bazat pe dimensiunea jurnalului inițial și fișierul de creștere automată (bazat pe configurația de creștere automată). În imaginea următoare, obținem o privire asupra fișierului jurnal de tranzacții SQL Server:

în cazul unui singur fișier jurnal de tranzacții, SQL Server utilizează o cale circulară fișier jurnal virtual. Putem verifica numărul de fișiere jurnal virtuale și starea acestora folosind următoarele:

1. DBCC LOGINFO (‘baza de date’) – este o declarație veche și funcționează cu toate versiunile SQL Server
2. Dynamic management view SYS.dm_db_log_info (DBID). Este disponibil de la SQL Server 2016 SP2 sau mai târziu

oricare dintre comenzile pot fi utilizate pentru verificarea VLF. Pentru acest articol, vom folosi vizualizarea de gestionare dinamică (DMV):

în captura de ecran de mai sus, am verificat că baza noastră de date eșantion conține două fișiere jurnal de tranzacții (file_id 2 și file_id 3).

• File_id 2 are VLF activ (vlf_active=1 și vlf_status = 2)
• File_id 3 nu are nici un VLF activ (vlf_active = 0 și vlf_status=2)

să inserăm câteva înregistrări în tabel, astfel încât VLF-ul activ să se schimbe:

monitorizarea utilizării spațiului jurnalului de tranzacții și starea VLF se vor face cu DMV (SYS. dm_db_log_space_usage):

• starea VLF

  1 2	selectați DB_name() ca DatabaseName,File_ID ca transaction_log_file_ID, vlf_active , vlf_status din sys. dm_db_log_info(DB_ID (‘MultileLogFiles’));

• utilizarea jurnalului de tranzacții (spațiu utilizat și liber)

  1 2 3 4 5

  selectați total_log_size_in_bytes * 1.0/1024/1024 total_log_size_in_MB, used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes-used_log_space_in_bytes)*1.0/1024/1024 ca free_log_space_in_MB din sys. dm_db_log_space_usage;

obținem următoarea ieșire a interogărilor de mai sus:

• în partea stângă, vedem că odată ce fișierul jurnal primar (file_id 2) devine plin, acesta trece la următorul fișier jurnal (file_id 3). În acest moment, avem VLF activ atât în fișierele jurnal primare, cât și în cele secundare. Într-o bază de date cu un model de recuperare completă, avem nevoie de o copie de rezervă a jurnalului de tranzacții, astfel încât să marcheze VLF ca inactiv
• ieșirea din partea dreaptă (folosind DMV) arată fișierul jurnal de tranzacții utilizat spațiu 8.51 MB. După cum vă amintiți, am setat dimensiunea 8 MB pentru fiecare fișier jurnal de tranzacții și am dezactivat creșterea automată a jurnalului. Când fișierul jurnal principal este plin (la 8 MB), SQL Server va trece la fișierul jurnal de tranzacții secundar

eliminați fișierul jurnal de tranzacții SQL Server secundar

pentru a elimina fișierul jurnal de tranzacții secundar (id fișier 3), vom folosi instrucțiunea editată Alter database.

vom adăuga clauza eliminare fișier și vom specifica fișierul pe care dorim să îl eliminăm:

executarea acestei instrucțiuni va duce la următorul mesaj de eroare:

notă: fișierul jurnal de tranzacții activ nu poate fi eliminat.

anterior, am văzut că odată ce fișierul jurnal primar devine plin, SQL Server utilizează fișierul jurnal secundar. Avem nevoie pentru a face un jurnal de tranzacții secundar gol, astfel încât să putem elimina.

în baza de date SQL cu un model de recuperare completă, folosim backup-uri jurnal de tranzacții astfel încât SQL Server poate trunchia jurnalele. Este posibil să avem nevoie de mai multe copii de rezervă pentru jurnal, în funcție de dimensiunea jurnalului de tranzacții, VLF activ și tranzacție activă.

să efectuăm o bază de date completă și o copie de rezervă a jurnalului de tranzacții:

• backup complet al bazei de date

  1	Backup MultipleLogFiles de baze de date pe disc=’C:\Temp\MultipleLogFiles.bak’

• copie de rezervă a jurnalului de tranzacții

  1	jurnal de rezervă MultipleLogFiles pe disc=’C:\Temp\MultipleLogFiles_log.trn’

după finalizarea copiei de rezervă, verificați starea VLF. VLF activ ar trebui să fie în primul fișier jurnal(primar), astfel încât să putem elimina fișierul jurnal secundar. Am verificat că numai fișierul jurnal primar (file_id 2) este activ (starea VLF 2):

acum, fișierul jurnal de tranzacții secundare ar trebui eliminat fără probleme. Să executăm din nou instrucțiunea editată Alter database. Fișierul jurnal al tranzacțiilor secundare va fi eliminat:

odată ce fișierul jurnal de tranzacții secundar este eliminat, verificați-l folosind GUI, precum și T-SQL cu system view sys.database_files:

fișierul jurnal de tranzacții secundar eliminat este încă prezent conform următoarei capturi de ecran:

Acum faceți clic dreapta pe baza de date și vizualizați fișierele existente. Vedem fișierul jurnal de tranzacții eliminat și în GUI. Dar de ce?

să executăm din nou instrucțiunea editată Alter database și să vedem dacă SQL Server elimină din nou fișierul jurnal de tranzacții.

am primit mesajul că SQL Server nu a putut găsi fișierul jurnal specificat:

SQL Server elimină fișierul jurnal de tranzacții după o copie de rezervă jurnal ulterioară. Să luăm o altă copie de rezervă a jurnalului și să verificăm dacă fișierul jurnal de tranzacții există în continuare:

verificați fișierul jurnal de tranzacții atât în metodele GUI, cât și în metodele T-SQL. Vedem că fișierul jurnal de tranzacții eliminat nu apare acum.

concluzie

în acest articol, am explorat utilizarea jurnalului de tranzacții SQL Server secundar și procesul de eliminare a acestuia. Ar trebui să evitați utilizarea mai multor fișiere jurnal de tranzacții, în special în baza de date de producție. Ar trebui să faceți o copie de rezervă a bazei de date înainte de a planifica orice activitate și să o faceți în ore neproductive.

• autor
• Postări recente

în calitate de formator certificat MCSA și certificat Microsoft în Gurgaon, India, cu 13 ani de experiență, Rajendra lucrează pentru o varietate de companii mari, concentrându-se pe optimizarea performanței, monitorizarea, disponibilitatea ridicată și strategiile și implementarea de recuperare în caz de dezastru. Este autorul a sute de articole autoritare despre SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git și tehnologii conexe care au fost vizualizate de peste 10 milioane de cititori până în prezent.
este creatorul uneia dintre cele mai mari colecții online gratuite de articole pe un singur subiect, cu seria sa de 50 de părți pe SQL Server Always On Availability Groups. Pe baza contribuției sale la comunitatea SQL Server, a fost recunoscut cu diverse premii, inclusiv prestigiosul „cel mai bun autor al anului” continuu în 2020 și 2021 la SQLSHACK.
Raj este întotdeauna interesat de noi provocări, așa că dacă aveți nevoie de ajutor de consultanță cu privire la orice subiect acoperit în scrierile sale, el poate fi contactat la rajendra.gupta16 @ gmail.com
Vezi toate mesajele de Rajendra Gupta

ultimele postări de Rajendra Gupta (vezi toate)

• utilizați șabloane ARM pentru a implementa instanțe Azure container cu imagini SQL Server Linux-21 decembrie 2021
• acces desktop la distanță pentru AWS RDS SQL Server cu Amazon RDS Custom – 14 decembrie 2021
• stocați fișiere SQL Server în stocare persistentă pentru instanțe Azure Container-decembrie 10, 2021