de: Sergey Gigoyan | actualizat: 2015-11-18 | Comentarii (9) | Related: mai mult> Administrarea bazei de date
problemă
SQL Server permite să utilizeze mai mult de un fișier jurnal de tranzacții, dar se pune întrebarea dacă este necesarși care este beneficiul de a avea mai multe fișiere jurnal de tranzacții. Există o concepție greșită în rândul unor dezvoltatori care au multipletransaction fișiere jurnal poate crește performanța, deoarece SQL Server le poate folosi în paralel. SQL Server utilizeazădoar un singur fișier jurnal de tranzacții în acest moment și nu există paralelism în acest caz. Uneori pot fi mai multe fișiere jurnalnecesare în scopul depanării. Deci, în mod normal, nu există nici o necesitate de a avea mai mult de un fișier jurnal.Să luăm în considerare un caz, atunci când Baza noastră de date are mai mult de un fișier jurnal și ar trebui să păstrăm doar unul.Acest sfat își propune să descrie cum să eliminați corect fișierele jurnal inutile și să păstrați doar unul.
soluție
înainte de a începe să ilustrați cum să eliminați fișierele jurnal inutile, să descriem pe scurt cum funcționează SQL Servercu fișiere jurnal: când baza de date are mai mult de un fișier jurnal, SQL Server continuă să scrie la primul până când este plin,apoi trece la al doilea și așa mai departe. După ce ultimul fișier jurnal devine complet SQL Server revine înapoi la primul și ciclul continuă.Cu toate acestea, așa cum am menționat, uneori pot fi necesare mai multe fișiere jurnal. De exemplu, când discul, unde este localizat fișierul jurnaldevine plin și trebuie să creăm al doilea fișier jurnal într-o altă locație,dar după depanarea problemei, ar trebui să ștergem al doilea fișier jurnal, deoarece nu are rost să avem mai mult de un fișier jurnal.Acum, să presupunem că avem o bază de date cu două fișiere jurnal, iar sarcina noastră este să o eliminăm pe a doua.Următorul script creează baza de date TestDB cu două fișiere jurnal și tabelul TestTable. Pentru a-l rula singur, va trebui să înlocuiți”D:\SQL date” cu o cale de folder existentă.
USE GOCREATE DATABASE ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), ( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE TestDBGOCREATE TABLE TestTable(ID INT IDENTITY(1,1),Value BIGINT)--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)ALTER DATABASE TestDB SET RECOVERY FULL
acum, să înțelegem structura fizică a jurnalului de tranzacții: fișierul jurnal de tranzacții intern constă dinfișiere jurnal virtuale (VLF), care sunt unitatea de gestionare în fișierul jurnal de tranzacții. Înseamnă că atunci când motorul bazei de date crește sau micșorează fișierul jurnal, face asta cu VLFs complet (de exemplu, nu poate micșora jumătate din VLF). Dimensiunea fișierelor jurnal virtuale, precum și numărul lor în fișierul jurnal fizic nu este fix și este gestionat dinamic bythe motor de baze de date. Pentru a monitorizafișierul jurnal de tranzacții internutilizăm comanda „DBCC LOGINFO”, care oferă informații despre fișierele jurnal virtuale. În scriptul de mai jos am folosit această comandă pentru Baza noastră de date:
--Getting log files infoDBCC LOGINFO('TestDB')
iar rezultatul este următorul:
FileID este ID-uri de fișiere jurnal pentru Baza noastră de date. Starea indică este VLF reutilizabil saunot (valori posibile: 0 – da, 2-nu). După cum putem vedea, există un singur VLF custatus=2. Acum, când vom introduce date în TestTable și să monitorizeze modul în care fișierele jurnal sunt în creștere:
USE TestDBGO--Checking log information before insertionSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files--Inserting data into TestTable;WITH ValueTable AS(SELECT 1 nUNION ALL SELECT n+ 1FROM ValueTableWHERE n
cu acest exemplu putem vedea că ambele fișiere jurnal au crescut și acum există VLFs cu „Status = 2” în al doilea fișier jurnal (FileID=3) de asemenea:
acum trebuie să eliminăm TestDB_log2.fișier ldf. Rețineți că putem elimina numai fișierele jurnal secundare. Eliminareafișierul jurnal primar nu este permis de SQL Server.Fiecare bază de date are un singur fișier jurnal primar și primul fișier jurnal care este creat în script-ul de creare a bazei de date este considerat primar.Dacă încercăm să eliminăm al doilea fișier jurnal:
USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
vom primi următorul mesaj:
putem elimina fișierul jurnal de tranzacții numai atunci când este gol, de aceea trebuie mai întâi să golim it.To fă asta, ar trebui să facem o copie de rezervă a jurnalului de tranzacții. Deoarece baza noastră de date „TestDB” este nou creată și nu există copii de rezervă complete, avem nevoiepentru a emite o copie de rezervă completă a bazei de date pentru baza de date TestDB, după care putem emite o copie de rezervă a jurnalului de tranzacții:
--Full backupBACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'
copia de rezervă a jurnalului de tranzacții trunchiază fișierul jurnal (există câteva excepții, care sunt în afara domeniului de aplicare al acestui documentp). Trunchierea jurnalului șterge fișierele jurnal virtuale inactive de laînceputul jurnalului logic și eliberează spațiu în fișierul jurnal. Cu toate acestea, trunchierea nu reduce dimensiunea unui fișier jurnal fizic. Eliberează doar spațiu în el, care poate fi reutilizat. Să rulăm din nou „DBCC LOGINFO” :
după cum putem vedea, nu există fișiere jurnal virtuale în „TestDB_log2.LDF ” fișier Cu Stare = 2 și acum fișierul nostru Jurnal este gol și gata pentru eliminare:
--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
eliminarea are succes.
cu toate acestea, atunci când vom verifica jurnal informațiile din nou, vom vedea că fișierul jurnal logic încă mai există:
--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
dacă facem o altă copie de rezervă a jurnalului, fișierul va fi șters:
--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
pașii următori
- rețineți acest sfat pentru a determina utilizarea jurnalului de tranzacții și cum să eliminați fișierul jurnal inutil.
- consultați aceste resurse:
- Administrare baze de date / jurnal de tranzacții
- https://technet.microsoft.com/en-us/library/ms191433(v = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms179355(v = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms345414(v = sql.105).aspx
despre autor
Sergey Gigoyan este un profesionist de baze de date cu mai mult de 10 ani de experiență, cu accent pe proiectarea bazei de date, dezvoltare, tuning de performanță, optimizare, disponibilitate ridicată, design BI și DW.Vezi toate sfaturile mele
articolul Ultima actualizare: 2015-11-18