Baza de date de compresie și jurnalul de tranzacții în serverul Microsoft sql, programare pentru începători

Mulți administratori Microsoft SQL Server s-au confruntat cu problema creșterii semnificative a dimensiunii fizice a fișierelor bazei de date și a fișierelor de tranzacții și, desigur, ar dori să reducă cumva această dimensiune pentru a nu întreprinde nicio acțiune legată de creșterea spațiului liber pe hard disk-ul. Modul de a reduce dimensiunea fizică a fișierelor din baza de date și a fișierelor de tranzacții în serverul SQL este - aceasta este compresia.

Ce este compresia în Microsoft SQL Server?

Compresia este procesul de eliminare a spațiului neutilizat în fișierele din baza de date și din fișierele de tranzacții.

Mărimea fizică a fișierelor bazei de date crește în timp, datorită adăugării de date, însă dacă le ștergeți, dimensiunea fizică a fișierelor rămâne neschimbată, cu toate acestea, spațiul logic neutilizat apare în aceste fișiere, care pot fi șterse.

Cel mai mare efect de comprimare este obținut atunci când operația de comprimare este efectuată după operarea ștergerii tabelelor din baza de date sau ștergerea datelor din tabele.

Trebuie să faceți o distincție între procedura de comprimare a jurnalului de tranzacții din procedura de trunchiere a jurnalului de tranzacții. Compresia - o scădere a dimensiunii fizice a jurnalului prin eliminarea unui spațiu nefolosit și pentru a trunchia - un spațiu de eliberare în revista logică pentru reutilizare (adică, spațiul neutilizat este format) jurnal de tranzacții atunci când dimensiunea fișierului fizic nu este redusă.

Tranșarea jurnalului de tranzacții are loc automat:

În modelul de recuperare simplu - după atingerea punctului de control, care poate avea loc, de exemplu, după crearea bazei de date BACKUP, declarația împlinirea CHECKPOINT aparentă sau atunci când un jurnal de tranzacții logică este umplut la 70 la suta, în toate aceste cazuri, există o curățare automată a părții inactive a jurnalului , și anume trunchierea sa;
În modelul de recuperare integral sau în modelul de recuperare cu înregistrare incompletă, după crearea unei copii de rezervă a jurnalului, cu condiția ca punctul de control să fi fost atins de la crearea ultimei copii de rezervă a jurnalului.

Dacă utilizați un model de recuperare completă sau un model de recuperare incompletă a înregistrării și fișierele jurnalului de tranzacții sunt prea mari, atunci cel mai probabil nu ați făcut un BACKUP (copie de siguranță) a jurnalului de tranzacții suficient de lung. În acest caz, trebuie să faceți mai întâi un jurnal de tranzacții BACKUP și apoi să efectuați o comprimare a jurnalului de tranzacții, pe care o vom examina mai jos.

De asemenea, dimensiunea fișierelor jurnalului de tranzacții este prea mare (atât pentru modelele de recuperare simple cât și pentru cele complete) prin întârzierea procedurii de trunchiere, i. E. log dimensiune, este alcătuită în principal din partea activă a revistei, iar partea activă nu poate fi trunchiat, astfel încât dimensiunea fizică a jurnalului crește. Întârzierea în astfel de factori influențează procedura trunchiere ca tranzacții active, pe termen lung, unele scenarii Flip baze de date și jurnal de tranzacții, unele script-uri atunci când tranzacțional replicare și tranzacție log, și log trunchiere nu este posibil în timpul operațiunilor de backup și recuperare. În acest caz, aveți nevoie pentru a elimina motivul întârzierii, apoi face trunchiere (adică, de exemplu, pentru modelul de recuperare jurnal complet BACKUP) și apoi comprimarea la dimensiuni acceptabile.

De obicei, dacă jurnalul de tranzacții sau baza de date este susținut în mod regulat, cu o anumită periodicitate (cu un model de recuperare simplu), fișierele jurnalului de tranzacții nu cresc, iar depășirea jurnalului de tranzacții nu are loc.

Cum se comprima o bază de date în MS SQL Server?

Puteți, de asemenea, să comprimați fișierele de jurnale de bază de date și de tranzacții utilizând GUI Management Studio și utilizând instrucțiunile Transact-SQL: DBCC SHRINKDATABASE și DBCC SHRINKFILE. De asemenea, este posibilă configurarea bazei de date pentru comprimarea automată prin setarea parametrului bazei de date AUTO_SHRINK la ON.

Comprimăm baza de date folosind Management Studio

Porniți Management Studio și deschideți obiectul Database (bază de date) în browserul de obiecte. Apoi, faceți clic dreapta pe baza de date pe care doriți să o comprimați, apoi selectați "Sarcini -> Comprimare -> Baza de date (sau Fișiere, de exemplu, dacă doriți să comprimați numai jurnalul de tranzacții)". De exemplu, aleg "Baza de date".

Baza de date de compresie și jurnalul de tranzacții în serverul Microsoft sql, programare pentru începători

Ca rezultat, veți vedea fereastra "Comprimare baze de date", în care, apropo, puteți observa dimensiunea bazei de date, precum și spațiul disponibil disponibil pe care îl puteți șterge (de exemplu, comprima). Faceți clic pe "OK".

După un timp, în funcție de mărimea bazei de date, comprimarea va fi finalizată.

Comprimăm baza de date utilizând instrucțiunile SHRINKDATABASE și SHRINKFILE

În MS SQL Server, există două instrucțiuni SHRINKDATABASE și SHRINKFILE pentru executarea compresiei fișierelor bazei de date și a jurnalului de tranzacții.

DBCC SHRINKDATABASE este comanda de a comprima baza de date;
DBCC SHRINKFILE - Cu această comandă, puteți comprima unele fișiere de bază de date (de exemplu, numai jurnalul de tranzacții).

Pentru a efectua compresia bazei de date (de exemplu, TestBase) la fel cum am făcut mai devreme în Management Studio, executați următoarea instrucțiune.

SHRINKDATABASE are următorii parametri:

database_name sau database_id - numele sau identificatorul bazei de date care trebuie comprimat. Dacă specificați o valoare de 0, se va utiliza baza de date curentă;
target_percent - spațiu liber în procente, care ar trebui să rămână în baza de date după comprimare;
NOTRUNCATE - comprimă datele în fișiere prin mutarea paginilor distribuite de la sfârșitul fișierului în locul paginilor nealocate la începutul fișierului. Dacă acest parametru este specificat, dimensiunea fizică a fișierului nu se modifică;
TRUNCATEONLY - eliberează tot spațiul liber de la sfârșitul fișierului la sistemul de operare, dar nu muta paginile din fișier. Fișierul de date este redus numai în ultima măsură selectată. Dacă acest parametru este specificat, parametrul target_percent nu este procesat;
WITH NO_INFOMSGS - suprimă toate mesajele de informație cu grade de severitate de la 0 la 10.

Pentru a comprima numai jurnalul de tranzacții, puteți utiliza instrucțiunea SHRINKFILE. de exemplu.

În acest caz, vom comprima fișierul jurnal (TestBase_log este numele fișierului jurnal de tranzacții) la valoarea inițială, adică la valoarea implicită. Pentru a comprima fișierul la o anumită dimensiune, specificați al doilea parametru în megaocteți. De exemplu, urmând instrucțiunile, vom reduce dimensiunea fișierului jurnal de tranzacții la 5 megabytes.

De asemenea, rețineți că, dacă specificați o dimensiune mai mică decât cea necesară pentru stocarea datelor într-un fișier, atunci fișierul la această dimensiune nu va fi comprimat. De exemplu, să presupunem că, dacă ați specificat 5 megaocteți, și pentru a stoca date într-un fișier este nevoie de 7 megaocteți, fișierul va fi comprimat doar la 7 megaocteți.

SHRINKFILE are de asemenea parametrii NOTRUNCATE și TRUNCATEONLY.

Recomandări și momente importante la comprimarea bazei de date

Operația de comprimare a bazei de date poate provoca fragmentarea indexurilor și poate încetini funcționarea bazei de date. Prin urmare, prea des nu se recomandă efectuarea unei comprimări a bazei de date;
Este mai bine să comprimați baza de date înainte de operația de reconstruire a indexului, adică după comprimare, executați procedura de reconstruire a indexurilor;
Parametrul bazei de date AUTO_SHRINK (compresie automată) nu trebuie să fie setat la ON, ci lăsat în mod prestabilit, adică în OFF, cu excepția cazului în care, desigur, nu aveți suficient motiv pentru acest lucru;
Instrucțiunea SHRINKDATABASE nu vă permite să reduceți mărimea bazei de date la o dimensiune mai mică decât dimensiunea inițială, adică minimul. Cu toate acestea, instrucțiunea SHRINKFILE poate face acest lucru (al doilea parametru specifică o dimensiune mai mică decât minimul). Dimensiunea minimă a bazei de date - aceasta este dimensiunea pe care este specificat la crearea bazei de date sau în mod explicit seta operațiunea de schimbare dimensiunea bazei de date, cum ar fi o DBCC SHRINKFILE sau ALTER DATABASE. De exemplu, în cazul în care baza de date a fost creată cu o dimensiune de 10 MB, apoi a crescut la 100 MB, acesta poate fi comprimat utilizând SHRINKDATABASE numai la primele 10 megabytes, chiar dacă toate datele au fost șterse din baza de date;
Nu puteți comprima fișierele din baza de date și din jurnalul de tranzacții atunci când procesul de backup este în desfășurare. În schimb, nu puteți crea copii de rezervă ale bazei de date și ale jurnalului de tranzacții în timpul procesului de compresie;
Executarea declarație DBCC SHRINKDATABASE fără a specifica un parametru sau NOTRUNCATE TRUNCATEONLY este echivalent cu declarația DBCC SHRINKDATABASE cu parametrul NOTRUNCATE după executarea DBCC SHRINKDATABASE cu instrucțiuni parametru TRUNCATEONLY;
În procesul de comprimare a bazelor de date, utilizatorii pot lucra în acesta (adică nu este nevoie să traduceți baza de date în modul de utilizator unic);
În orice moment puteți întrerupe procesul de efectuare a operațiilor SHRINKDATABASE și SHRINKFILE, în timp ce toate lucrările efectuate sunt salvate;
Înainte de a începe procedura de compresie, verificați dacă există spațiu liber de șters în fișierele bazei de date, adică. pot comprima fișierele prin rularea următoarei interogări (se va afișa în megaocteți cât de mult puteți reduce fișierele bazei de date).

Pentru a efectua procedura de comprimare a bazei de date, trebuie să fiți membru al grupului de roluri pentru serverul sysadmin sau rolul bazei de date db_owner;

Comprimarea baze de date și de tranzacție fișierele jurnal este un proces care necesită numeroase resurse, care necesită o anumită perioadă de timp (în funcție de dimensiunea fișierului), astfel încât această procedură este necesară pentru a planifica și, în general, a efectua o numai atunci când este absolut necesar (de exemplu, dimensiunea bazei de date, iar revista a devenit prea mare, și mai mult de jumătate un singur fișier ocupă spațiu neutilizat).

Articole similare

Pagina anterioară

Pagina următoare