SQLShack

În acest articol, vom discuta despre proprietatea de bază de date SQL Server Auto Shrink, care permite SQL Server să micșoreze automat fișierele bazei de date atunci când valoarea este configurată ca True în opțiunea de bază de date. Operațiunea de micșorare automată este efectuată de către caracteristica de bază de date de micșorare automată a serverului, care este cursul principal al acestui articol.

Pentru a evita activitatea de micșorare a fișierelor de date/log ori de câte ori dimensiunea fișierului depășește spațiul liber din fișierul de date, DBA (administratorul bazei de date) trebuie să facă o copie de rezervă a fișierelor de log la un interval regulat. Efectuarea unui backup al întregii baze de date nu este o idee bună; trebuie să se construiască sau să se configureze și jurnalul de tranzacții. Jurnalele de tranzacții vor crește în număr până când vor ocupa tot spațiul disponibil pe disc dacă nu se face un back-up al acestora. Dacă faceți o copie de rezervă a bazei de date, se eliberează spațiul liber pentru a fi reutilizat din nou. Administratorul bazei de date trebuie să programeze o copie de rezervă a jurnalului de tranzacții pentru a reduce dimensiunea fișierelor de jurnal la o dimensiune corectă.

Caracteristica SQL Server Auto Shrink este dezactivată în mod implicit pe bazele de date de instanță SQL Server. În scenariul în care aveți un număr de baze de date relativ mici care cresc la o dimensiune mai mare fie prin inserarea de tupluri noi, fie printr-un număr mare de spații goale create prin ștergerea mai multor tupluri, SQL Server Auto Shrink devine destul de util în această situație. În plus, trebuie să nu trebuie să vă faceți griji cu privire la fragmentarea dimensiunii fișierelor bazelor de date.

În timp ce micșorați baza de date, trebuie să trebuiască să luați în considerare operațiunile de micșorare atât a bazelor de date critice, cât și a celor relativ mari, ca parte a întreținerii. În afară de aceasta, trebuie să se evite rularea funcției de micșorare manuală; consecințele acestei acțiuni sunt că nu se va lua niciodată cunoștință de problemele legate de cererea nouă sau existentă. Cu toate acestea, micșorarea fișierelor de tranzacție este mai bună decât micșorarea fișierelor de date.

Cum se activează și se dezactivează opțiunea de micșorare automată a bazei de date SQL Server?

Utilizatorii pot activa și dezactiva opțiunea de micșorare automată a bazei de date folosind SSMS și T-SQL în ambele moduri.

Activarea micșorării automate a bazei de date prin utilizarea SSMS:

Utilizatorii pot activa sau dezactiva această opțiune din proprietățile bazei de date, cu o etichetă de Auto Shrink. Aici, True în meniul derulant va activa această opțiune pentru baza de date.

Enable database auto shrink using T-SQL:

Utilizatorii pot executa instrucțiunile T-SQL de mai jos pentru a activa sau dezactiva baza de date Shrink. Am utilizat baza de date AdventureWorks pentru instrucțiunea T-SQL de mai sus.

Efectele micșorării bazei de date asupra performanței interogării

Efectele rele pot fi ridicate pe partea de performanță a interogării dacă activați împreună opțiunea de micșorare automată a bazei de date și setările de creștere automată pentru baza de date. Putem păstra un anumit spațiu pentru ca fișierele de date să crească și, de asemenea, putem evita unele evenimente frecvente de creștere automată prin configurarea dimensiunii bazei de date la o valoare optimă sau, în cea mai mare parte, fiecare bază de date are anumiți parametri cu caracteristici de creștere automată activate.

Pentru astfel de baze de date, caracteristicile de micșorare automată trebuie să fie activate de noi atunci când baza de date este mai mică, una și nu mai există nicio operațiune CRUD, astfel încât să poată face posibilă micșorarea fișierelor de date și să recupereze spațiul liber pe care l-am provizionat în mod intenționat pentru a ne feri bazele de date de evenimente de creștere automată. Spațiul liber va fi eliberat automat în mod automat în fișierele de date și în fișierele jurnal, periodic, cu prioritate secvențială de tip round-robin, de către caracteristica Auto shrink, dacă mai multe baze de date au activat această caracteristică.

Pentru bazele de date de dimensiuni mari, se va executa creșterea automată și, ulterior, contracția automată, care va duce la probleme de performanță conduse de fragmentarea la nivel de sistem. Pentru a rezuma toate acestea, pentru orice bază de date, auto shrink nu ar trebui să fie activată pe baza următoarelor motive:

• Executarea algoritmilor SQL Server auto shrink fără nici un scop va risipi cu siguranță resursele într-o cantitate masivă
• Indiferent dacă executați SQL Server auto shrink sau manual shrink, în mod evident se va produce fragmentarea indexului și acest lucru execută în cele din urmă și micșorarea fișierelor de date
• Dacă serverul forțează și limitele subsistemului IO, executarea shrink îl poate împinge peste, rezultând lungimi mari ale cozilor de așteptare pe disc și, eventual, timeout-uri IO, acest lucru va consuma resursele IO și CPU ale serverului într-o cantitate masivă
• Performanța sistemului va fi întârziată de fragmentarea la nivel de disc a sistemului de fișiere, care este, din nou, efectul direct al efectuării frecvente a micșorării și creșterii fișierelor de date

Pentru a înțelege mai multe despre evenimentele de creștere și micșorare în SQL Server, citiți acest articol, Obțineți detalii despre evenimentele de creștere și micșorare a bazelor de date SQL Server.

Pentru baza de date critică, operațiunea de micșorare manuală ar putea fi executată la nivelul fișierului de bază de date de către expert. Activitatea de micșorare manuală a fișierului poate fi efectuată atunci când se execută operația de ștergere și, ulterior, spațiul a fost recuperat. Trebuie să reconstruim indicii fragmentați la executarea operației de micșorare, deoarece operația de micșorare poate duce la fragmentarea indicilor. Procentul de fragmentare a indexului ar putea fi verificat de către utilizator cu ajutorul instrucțiunilor T-SQL ale DMV-urilor. Cu toate acestea, micșorarea fișierului de jurnal ar trebui să se facă manual, după cum și când este necesar, și nu ar trebui să facă parte din activitatea de întreținere regulată.

Pentru a monitoriza în mod regulat utilizarea discului, raportul de utilizare a discului ar putea fi analizat de către utilizator înainte de a efectua operația manuală de micșorare cu ajutorul SSMS, care vă oferă o imagine de ansamblu a informațiilor privind spațiul de date și de jurnal pentru o bază de date pe ecran. În cazul în care utilizatorul ar dori să obțină un tablou de bord pentru un calcul al spațiului rezervat și al spațiului liber pentru fișierele bazei de date, atunci Disk Report devine foarte util. Cu toate acestea, raportul de disc locuiește informațiile cu ajutorul DMV-urilor SQL Server. Raportul de disc este disponibil în directorul de mai jos folosind SSMS.

Database >> Reports >> Standard Reports >> Disk Usage

Aici, avem un Disk Report pentru baza de date. Statisticile în timp real pentru fișierele de date și fișierele jurnal au putut fi văzute de către utilizator. Acest raport încorporează informații primare despre Total Space Reserved, Data Files Space Reserved, Transaction Log Space Reserved și In-Memory OLTP Space Reserved.

Shrink database automatically uses SQL Server Jobs

Astfel de scripturi ar putea fi realizate prin programarea unui job de către utilizator pentru a executa operația de micșorare a bazei de date cu activitate de programare. Ca prim pas în script, se găsește spațiul liber în fișierul bazei de date și apoi se micșorează acel fișier dacă criteriile definite pentru spațiul liber se potrivesc. Spațiul liber ar trebui să fie calculat cu ajutorul DMV-urilor SQL Server.

Practic, monitorizarea fișierelor bazei de date este efectuată de administratorul bazei de date, care la rândul său intervine asupra bazei de date pentru a monitoriza dimensiunea fișierului bazei de date. În cazul în care limita predefinită este depășită de orice fișier, atunci trebuie să efectueze astfel de pași. Așadar, această activitate poate fi automatizată cu ajutorul unui job SQL Server pentru a se efectua zilnic, săptămânal sau lunar la un moment predefinit.

Cea mai bună practică este de a programa acest job doar pe fișierul jurnal al bazei de date și de a monitoriza manual spațiul liber al fișierului de date. Deoarece poate afecta și performanța interogărilor. SQL Server DMV va returna fișierul de date și fișierul jurnal în același set de rezultate, astfel încât tipul de fișier trebuie să se bifurce în instrucțiunea T-SQL a SQL Server DMV. Înainte de a micșora fișierul, utilizatorul poate aplica logica de interogare asupra spațiului total și a spațiului liber al fișierului jurnal. Dacă criteriile de calcul se potrivesc cu proprietatea fișierului, atunci fișierul se va micșora cu dimensiunea țintă a fișierului.

Verificarea spațiului liber pentru fișierele bazei de date:

Dacă astfel de criterii se potrivesc cu fișierele bazei de date, de exemplu, dacă spațiul liber este mai mare de (n) MB/GB, dacă n(%) spațiu liber se compară cu spațiul total și multe altele. Atunci când spațiul liber nu este disponibil pentru fișierul jurnal și dimensiunea fișierului este aproape de valoarea parametrului max. al dimensiunii fișierului, atunci administratorul bazei de date trebuie să cerceteze în jurnalul de tranzacții.

Reduceți fișierul bazei de date:

Aici, 5120 este o dimensiune țintă a fișierului în MB. Așadar, fișierul va avea o dimensiune de 5120 MB. Configurați interogarea T-SQL Query cu criteriile necesare în etapa de lucru SQL Server și programați-o on-off hours.

Opțiunea de micșorare automată SQL Server nu poate fi activată pentru toate bazele de date. În mod specific, ajută pentru bazele de date mai mici, care efectuează mai puține operații CRUD comparativ.

Concluzie

În acest articol, am discutat despre proprietatea de bază de date Auto Shrink din SQL Server pentru a micșora fișierele de date și de jurnal și pentru a elimina spațiul neutilizat. Micșorarea unei baze de date este o operațiune costisitoare și trebuie utilizată cu atenție.

• Autor
• Postări recente

Jignesh are o bună experiență în soluții și arhitectură de baze de date, lucrând cu mai mulți clienți în ceea ce privește proiectarea bazelor de date & Arhitectura, dezvoltarea SQL, administrarea, optimizarea interogărilor, reglarea performanțelor, HA și recuperarea în caz de dezastru.
Vezi toate postările lui Jignesh Raiyani

Ultimele postări ale lui Jignesh Raiyani (vezi toate)

• Page Life Expectancy (PLE) in SQL Server – 17 iulie, 2020
• Cum se automatizează partiționarea tabelelor în SQL Server – 7 iulie 2020
• Configurarea grupurilor de disponibilitate SQL Server Always On pe AWS EC2 – 6 iulie 2020