I denne artikel vil vi diskutere SQL Server Auto Shrink-databaseegenskaben, som gør det muligt for SQL Server automatisk at krympe databasefilerne, når værdien er konfigureret som True i databaseindstillingen. Automatisk krympningsoperation udføres af serveren auto shrink databasefunktion, som er hovedforløbet i denne artikel.
For at undgå aktiviteten med krympning af data/logfiler, når filstørrelsen overskrider den frie plads i datafilen, skal DBA (Databaseadministrator) sikkerhedskopiere logfilerne med jævne mellemrum. Det er ikke en god idé at sikkerhedskopiere hele databasen; man skal også konstruere eller opsætte transaktionsloggen. Transaktionslogfilerne vil stige i antal, indtil de optager al den tilgængelige diskplads, hvis man ikke tager backup af dem. Hvis du sikkerhedskopierer databasen, frigiver det den frie plads, som kan genbruges igen. Databaseadministrator skal planlægge en backup af transaktionsloggen for at reducere logfilernes størrelse til en rimelig størrelse.
SQL Server Auto Shrink-funktionen er som standard deaktiveret på SQL Server-instansdatabaser. I det scenarie, hvor du har en række relativt små databaser, der vokser til en større størrelse enten ved indsættelse af nye tupler eller ved et stort antal tomme pladser, der er skabt ved sletning af flere tupler, bliver SQL Server Auto Shrink ret praktisk i denne situation. Desuden behøver du ikke at bekymre dig om størrelsesfragmenteringen af databasefilerne.
Mens du krymper databasen, skal du være nødt til at overveje krympningsoperationer på både kritiske såvel som relativt større databaser som en del af vedligeholdelsen. Udover dette bør man undgå at køre manuel krympningsfunktion; konsekvenserne af at gøre dette er, at man aldrig vil blive bekendt med problemer vedrørende den nye eller eksisterende anmodning. Skrumpning af transaktionsfiler er dog bedre end skrumpning af datafiler.
Måde at aktivere og deaktivere SQL Server auto shrink for databasen?
Brugere kan aktivere og deaktivere database auto shrink mulighed ved hjælp af SSMS og T-SQL begge måder.
Aktivering af database auto shrink ved hjælp af SSMS:
Brugere kan aktivere eller deaktivere denne indstilling fra egenskaberne for databasen med et tag af Auto Shrink. Her vil True i rullelisten aktivere denne indstilling for din database.
Aktiver automatisk krympning af database ved hjælp af T-SQL:
Brugere kan udføre nedenstående T-SQL-anvisninger for at aktivere eller deaktivere databasen krympning. Vi har brugt AdventureWorks-databasen til ovenstående T-SQL-anvisning.
|
1
2
3
4
5
6
|
–Aktiver automatisk krympning for databasen AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
–Deaktiver automatisk krympning for databasen AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Virkninger af databasekrympning på forespørgselsydelsen
Dårlige virkninger kan opstå på forespørgselsydelsessiden, hvis du aktiverer indstillingen for automatisk krympning af databasen og indstillingerne for automatisk vækst sammen for databasen. Vi kan holde noget plads til datafiler til at vokse og også undgå nogle hyppige auto-væksthændelser ved at indstille databasestørrelsen til en optimal værdi eller for det meste hver database har nogle parametre med auto-vækstfunktioner aktiveret.
For sådanne databaser skal auto shrink-funktioner aktiveres af os, når databasen er mindre, en og ikke mere CRUD-operation, så det kan gøre krympning af datafiler mulig og hente den frie plads, som vi har tilvejebragt målrettet for at holde vores databaser væk fra auto-væksthændelser. Fri plads frigives automatisk i datafiler og logfiler med jævne mellemrum i round-robin sekventiel prioritet af funktionen Auto shrink, hvis flere databaser har slået denne funktion til.
For databaser af stor størrelse, auto-growth, og derefter vil auto shrink blive udført, hvilket vil resultere i præstationsproblemer føre af fragmentering på systemniveau. For at opsummere alt dette bør automatisk krympning ikke aktiveres for nogen database af følgende grunde:
- Udførelse af SQL Server auto shrink-algoritmer uden noget formål vil helt sikkert komme til at spilde ressourcerne i en massiv mængde
- Uanset om du udfører SQL Server auto shrink eller manuel krympning, vil det naturligvis komme til at forårsage indeksfragmentering, og dette udfører i sidste ende datafiler krympning også
- Hvis serveren også tvinger grænserne for IO-undersystemet, kan kørende krympning skubbe det over, hvilket resulterer i lange disk kø-længder og muligvis IO timeouts, dette vil æde Server IO og CPU ressourcer i en massiv mængde
- Systemets ydeevne vil blive forsinket af filsystem disk-niveau fragmentering, hvilket igen er den direkte effekt af at udføre krympning og voksende datafiler ofte
For at forstå mere om Growth og Shrink Events i SQL Server, læs denne artikel, Få detaljer om SQL Server Database Growth og Shrink Events.
For den kritiske database kan den manuelle krympningsoperation køres på databasefilniveau af eksperten. Manuel krympningsfilaktivitet kan udføres, når sletteoperationen udføres, og dernæst er der blevet genvundet plads. Vi skal genopbygge de fragmenterede indekser ved udførelse af krympningsoperationen, da krympningsoperationen kan føre til indeksfragmentering. Procentdelen af indeksfragmentering kan kontrolleres af brugeren ved hjælp af T-SQL-udsagn fra DMV’er. Skrumpning af logfilen bør dog foretages manuelt efter behov og bør ikke være en del af den regelmæssige vedligeholdelsesaktivitet.
For at overvåge diskforbruget regelmæssigt kan diskforbrugsrapporten analyseres af brugeren, inden den manuelle krympningsoperation udføres ved hjælp af SSMS, hvilket giver dig et indblik i data- og logpladsoplysninger for en database i displayet. Hvis brugeren gerne vil have et dashboard til en beregning af reserveret plads og ledig plads til databasefiler, så bliver Disk Report meget nyttigt. Diskrapporten bebor dog oplysningerne ved hjælp af SQL Server DMV’er. Diskrapporten er tilgængelig på nedenstående mappe ved hjælp af SSMS.
Database >> Rapporter >> Standardrapporter >> Standardrapporter >> Disk Usage
Her har vi en Diskrapport for databasen. Realtidsstatistikker for datafiler og logfiler kan ses af brugeren. Denne rapport indeholder primære oplysninger om Samlet reserveret plads, reserveret plads til datafiler, reserveret plads til transaktionslogfiler, reserveret plads til transaktionslogfiler og reserveret plads til In-Memory OLTP.
Skrump database bruger automatisk SQL Server Jobs
Sådanne scripts kunne udføres ved at brugeren planlægger et job for at udføre databasen krympeoperation med scheduleraktivitet. Som det første trin i scriptet skal du finde den frie plads i databasefilen og derefter krympe filen, hvis de definerede kriterier for den frie plads er opfyldt. Fri plads skal beregnes ved hjælp af SQL Server DMV’er.
Grundlæggende udføres databasefilovervågning af databaseadministratoren, som igen træder på databasen for at overvåge databasens filstørrelse. Hvis den foruddefinerede grænse overskrides af en fil, skal de udføre sådanne trin. Så denne aktivitet kan automatiseres ved hjælp af SQL Server job til at udføre på daglig basis, ugentligt eller månedligt på et foruddefineret tidspunkt.
Den bedste praksis er at planlægge dette job på databasen logfilen kun og overvåge datafilen ledig plads manuelt. Fordi det også kan påvirke forespørgselsydelsen. SQL Server DMV returnerer datafilen og logfilen i det samme resultatsæt, så filtype skal bifurkeres i T-SQL-angivelsen i SQL Server DMV. Før filen krympes, kan brugeren anvende forespørgselslogik på den samlede plads og den frie plads i logfilen. Hvis beregningskriterierne er matchet med filegenskaben, vil filen krympe med målfilstørrelsen.
Kontroller ledig plads til databasefilerne:
Hvis sådanne kriterier er matchet med databasefilerne, f.eks. hvis ledig plads er større end (n) MB/GB, hvis n(%) ledig plads sammenligner med den samlede plads og mange flere. Når der ikke er ledig plads til rådighed for logfilen, og filstørrelsen er tæt på omkring parameterværdien for maks. filstørrelse, skal databaseadministratoren søge i transaktionsloggen.
Shrink databasefil:
|
1
|
DBCC SHRINKFILE(file_name, 5120);
|
Her er 5120 en målfilstørrelse i MB. Så filen vil få en størrelse på 5120 MB. Konfigurer T-SQL-forespørgslen med de nødvendige kriterier i SQL Server-jobtrinnet, og planlæg den til og fra timer.
SQL Server-indstillingen for automatisk krympning kan ikke aktiveres for alle databaser. Specifikt hjælper det til den mindre database, som udfører mindre CRUD-operation forholdsmæssigt.
Konklusion
I denne artikel diskuterede vi Auto Shrink-databaseegenskaben i SQL Server for at krympe data- og logfiler og fjerne ubrugt plads. Skrumpning af en database er en dyr operation og bør bruges med omtanke.
- Author
- Recent Posts
Se alle indlæg af Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Sådan automatiseres Table Partitioning i SQL Server – 7. juli 2020
- Konfigurering af SQL Server Always On Availability Groups på AWS EC2 – 6. juli 2020