In dit artikel bespreken we de SQL Server Auto Shrink database eigenschap, die SQL Server in staat stelt om automatisch de database bestanden te verkleinen wanneer de waarde als True is geconfigureerd in de database optie. Automatische krimp operatie wordt gedaan door de server auto shrink database eigenschap, dat is de hoofdcursus van dit artikel.
Om de activiteit van het krimpen van data / log-bestanden te voorkomen wanneer de bestandsgrootte de vrije ruimte in het gegevensbestand overschrijdt, moet DBA (Database Administrator) een back-up van de log-bestanden met een regelmatige interval. Een back-up maken van de hele database is geen goed idee; men moet ook het transactielog aanmaken of opzetten. Transactielogboeken zullen in aantal toenemen totdat ze alle beschikbare schijfruimte innemen als je er geen back-up van maakt. Als je een back-up maakt van de database, komt de vrije ruimte vrij om opnieuw te worden gebruikt. Database Administrator moet een transactielogboek back-up plannen om de grootte van de logbestanden op een redelijke grootte te verminderen.
SQL Server Auto Shrink functie is standaard uitgeschakeld op SQL Server instance databases. In het scenario waarin u een aantal relatief kleine databases heeft die groeien naar een grotere omvang, hetzij door het invoegen van nieuwe tuples of door een groot aantal lege ruimtes gecreëerd door het verwijderen van een aantal tuples, SQL Server Auto Shrink wordt vrij handig in deze situatie. Bovendien hoeft u zich geen zorgen te maken over de grootte fragmentatie van database bestanden.
Bij het krimpen van de database moet u rekening houden met krimp operaties op zowel kritische als relatief grotere databases als onderdeel van het onderhoud. Bovendien moet worden vermeden de handmatige krimpfunctie uit te voeren; de gevolgen hiervan zijn dat men nooit op de hoogte zal zijn van problemen met betrekking tot het nieuwe of bestaande verzoek. Transactiebestanden verkleinen is echter beter dan de gegevensbestanden verkleinen.
Hoe kan men de SQL Server auto shrink voor de database activeren en deactiveren?
Gebruikers kunnen de database auto shrink optie in- en uitschakelen met behulp van SSMS en T-SQL op beide manieren.
Het activeren van de database auto shrink met behulp van SSMS:
Gebruikers kunnen deze optie in- of uitschakelen vanuit de eigenschappen van de database, met een tag van Auto Shrink. Hier, True in de drop-down zal deze optie inschakelen voor uw database.
Database automatisch verkleinen met T-SQL inschakelen:
Gebruikers kunnen de onderstaande T-SQL-statements uitvoeren om de database te verkleinen of uit te schakelen. We hebben de AdventureWorks database gebruikt voor de bovenstaande T-SQL verklaring.
|
1
2
3
4
5
6
|
–Deactiveer Auto Shrink voor de database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
–Auto Shrink uitschakelen voor de database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Effecten van het verkleinen van de database op de query performance
Effecten op de query performance kunnen ontstaan als u de database auto shrink optie en auto-growth instellingen samen inschakelt voor de database. We kunnen wat ruimte te houden voor de gegevens-bestanden om te groeien en ook te voorkomen dat een aantal frequente auto-groei gebeurtenissen door het opzetten van de database grootte op een optimale waarde of meestal elke database hebben een aantal parameters met auto-groei functies ingeschakeld.
Voor dergelijke databases, auto krimpen functies moeten worden geactiveerd door ons wanneer de database kleiner is, een en geen CRUD operatie meer, dus het kan krimpen van gegevens-bestanden mogelijk te maken en op te halen de vrije ruimte die we doelbewust provisioned om weg te houden onze databases van auto-groei gebeurtenissen. Vrije ruimte wordt automatisch vrijgemaakt in de databestanden en logbestanden periodiek in round-robin sequentiële prioriteit door de Auto shrink-functie als meerdere databases deze functie hebben ingeschakeld.
Voor databases van grote omvang zal auto-growth, en daarna, de auto shrink worden uitgevoerd, wat zal resulteren in prestatieproblemen die worden veroorzaakt door fragmentatie op systeemniveau. Samenvattend, voor elke database, auto shrink moet niet worden geactiveerd op basis van de volgende redenen:
- Het uitvoeren van de SQL Server auto shrink algoritmes zonder enig doel zal zeker de resources massaal verspillen
- Of u nu SQL Server auto shrink of handmatig shrink uitvoert, het zal uiteraard de index fragmentatie veroorzaken en dit voert uiteindelijk ook het krimpen van de data bestanden uit
- Als de server ook de grenzen van het IO subsysteem opzoekt, kan het uitvoeren van shrink het over duwen, resulterend in lange schijf wachtrijen en mogelijk IO time-outs, dit zal server IO en CPU-bronnen opeten in een enorme hoeveelheid
- Systeemprestaties zullen worden vertraagd door bestandssysteem schijf-niveau fragmentatie, wat weer het directe effect is van het vaak doen krimpen en groeien van databestanden
Om meer te begrijpen over Groei en Krimp Gebeurtenissen in SQL Server, lees dit artikel, Krijg details over SQL Server Database Groei en Krimp Gebeurtenissen.
Voor de kritische database kan de expert handmatig een krimpoperatie uitvoeren op databasebestandniveau. Handmatig krimpen bestand activiteit kan worden gedaan wanneer de delete operatie is uitgevoerd, en daarna, ruimte is teruggewonnen. De gefragmenteerde indexen moeten na het krimpen opnieuw worden opgebouwd, omdat het krimpen tot indexfragmentatie kan leiden. Het fragmentatiepercentage van de indexen kan door de gebruiker worden gecontroleerd met behulp van T-SQL statements of DMV’s. Het krimpen van het logbestand moet echter handmatig worden uitgevoerd wanneer dit nodig is en mag geen deel uitmaken van de reguliere onderhoudsactiviteiten.
Om het schijfgebruik regelmatig te controleren, kan de gebruiker het schijfgebruiksrapport analyseren voordat hij de handmatige krimpoperatie uitvoert met SSMS, waarmee hij inzicht krijgt in de gegevens- en logruimte-informatie voor een database op het scherm. Als de gebruiker een dashboard wil hebben voor een berekening van Gereserveerde ruimte en Vrije ruimte voor databasebestanden, dan is Disk Report zeer nuttig. Echter, het schijfrapport bewoont de informatie met behulp van SQL Server DMV’s. Het schijfrapport is beschikbaar in de onderstaande directory met behulp van SSMS.
Database >> Rapporten >> Standaardrapporten >> Schijfgebruik
Hier hebben we een Schijfrapport voor de database. Real-time statistieken voor de gegevensbestanden en logbestanden kunnen door de gebruiker worden bekeken. Dit rapport bevat primaire informatie over de Totale Gereserveerde Ruimte, de Gereserveerde Ruimte voor gegevensbestanden, de Gereserveerde Ruimte voor transactielogboeken en de Gereserveerde Ruimte voor OLTP in het geheugen.
Database krimpen automatisch met behulp van SQL Server-banen
Dergelijke scripts kunnen worden uitgevoerd door de gebruiker een taak te laten plannen om de databasekrimpoperatie met scheduler-activiteit uit te voeren. Als eerste stap in het script moet de vrije ruimte in het databasebestand worden gevonden en vervolgens moet dat bestand worden verkleind als de gedefinieerde criteria voor de vrije ruimte worden gehaald. De vrije ruimte moet worden berekend met behulp van SQL Server DMV’s.
In principe wordt de database file monitoring uitgevoerd door de database administrator, die op zijn beurt stappen op de database om de database file grootte te controleren. Als de vooraf gedefinieerde grens wordt overschreden door een bestand, dan moeten ze dergelijke stappen uit te voeren. Dus die activiteit kan worden geautomatiseerd met behulp van SQL Server job uit te voeren op een dagelijkse basis, wekelijkse of maandelijkse op een vooraf bepaalde tijd.
De beste praktijk is om deze taak te plannen op de database log-bestand alleen en controleer de gegevens bestand vrije ruimte handmatig. Omdat het de query performance ook kan beïnvloeden. SQL Server DMV retourneert het databestand en het logbestand in dezelfde resultatenset, dus het bestandstype moet worden opgesplitst in het T-SQL statement van SQL Server DMV. Alvorens het bestand te verkleinen, kan de gebruiker query logica toepassen op de totale ruimte en vrije ruimte van het logbestand. Als de berekening criteria overeenkomen met het bestand eigendom, dan is het bestand zal krimpen met het doel bestandsgrootte.
Controle vrije ruimte voor de database-bestanden:
Als dergelijke criteria overeenkomen met de database-bestanden, bijvoorbeeld als Vrije ruimte is groter dan (n) MB / GB, als n (%) vrije ruimte te vergelijken met de totale ruimte, en nog veel meer. Wanneer vrije ruimte is niet beschikbaar voor het log-bestand en de bestandsgrootte is in de buurt over de max. bestandsgrootte parameterwaarde, dan is de database beheerder moet scouten in het transactielogboek.
Klein database bestand:
|
1
|
DBCC SHRINKFILE(file_name, 5120);
|
Hier is 5120 een doelbestandsgrootte in MB. Dus, het bestand zal een grootte krijgen van 5120 MB. Stel de T-SQL Query op met de vereiste criteria in de SQL Server job step en plan deze op de juiste tijd.
De SQL Server auto shrink optie kan niet voor alle databases worden aangezet. Specifiek, het helpt voor de kleinere database, die minder CRUD bewerkingen vergelijkenderwijs uitvoert.
Conclusie
In dit artikel hebben we de Auto Shrink database eigenschap in SQL Server besproken om data en log bestanden te krimpen en ongebruikte ruimte te verwijderen. Het krimpen van een database is een dure operatie en moet zorgvuldig worden gebruikt.
- Auteur
- Recent Posts
Bekijk alle berichten van Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – 17 juli, 2020
- Hoe tabelpartities automatiseren in SQL Server – 7 juli 2020
- SQL Server Always On Availability Groups configureren op AWS EC2 – 6 juli 2020