SQLShack

I den här artikeln kommer vi att diskutera databasegenskapen SQL Server Auto Shrink, som gör det möjligt för SQL Server att automatiskt krympa databasfilerna när värdet är konfigurerat som True i databasalternativet. Den automatiska krympningsoperationen görs av serverns databasfunktion Auto shrink, vilket är huvudkursen i den här artikeln.

För att undvika aktiviteten att krympa data/loggfiler när filstorleken överstiger det fria utrymmet i datafilen måste DBA (databasadministratör) säkerhetskopiera loggfilerna med jämna mellanrum. Att säkerhetskopiera hela databasen är inte en bra idé; man måste konstruera eller ställa in transaktionsloggen också. Transaktionsloggarna kommer att öka i antal tills de upptar allt tillgängligt diskutrymme om man inte tar backup av dem. Om du säkerhetskopierar databasen frigörs det fria utrymmet som kan återanvändas igen. Databasadministratören måste schemalägga en säkerhetskopiering av transaktionsloggar för att minska loggfilernas storlek till en rimlig storlek.

SQL Server Auto Shrink-funktionen är inaktiverad som standard på SQL Server-instansdatabaser. I det scenario där du har ett antal relativt små databaser som växer till en större storlek antingen genom att nya tupler läggs in eller genom ett stort antal tomma utrymmen som skapas genom att flera tupler tas bort, blir SQL Server Auto Shrink ganska praktiskt i denna situation. Dessutom behöver du inte oroa dig för storleksfragmenteringen av databasfilerna.

När du krymper databasen måste du överväga krympningsoperationer på både kritiska och relativt stora databaser som en del av underhållet. Dessutom bör man undvika att köra den manuella krympningsfunktionen; konsekvenserna av att göra detta är att man aldrig kommer att få kännedom om frågor som rör den nya eller befintliga begäran. Det är dock bättre att krympa transaktionsfiler än att krympa datafiler.

Sätt att aktivera och inaktivera SQL Servers automatiska krympning för databasen?

Användare kan aktivera och inaktivera databasens automatiska krympningsalternativ med hjälp av SSMS och T-SQL på båda sätten.

Aktivera databasens automatiska krympning med hjälp av SSMS:

Användare kan aktivera eller inaktivera det här alternativet från databasens egenskaper med taggen Auto Shrink. Här aktiverar True i rullgardinsmenyn det här alternativet för din databas.

Aktivera automatisk krympning av databasen med hjälp av T-SQL:

Användare kan utföra nedanstående T-SQL-anvisningar för att aktivera eller inaktivera krympning av databasen. Vi har använt AdventureWorks-databasen för ovanstående T-SQL-meddelande.

Effekter av databasens krympning på frågeprestanda

Dåliga effekter kan uppkomma på frågeprestandasidan om du aktiverar inställningarna för databasens automatiska krympningsalternativ och automatisk tillväxt tillsammans för databasen. Vi kan hålla lite utrymme för datafiler att växa och även undvika några frekventa auto-growth-händelser genom att ställa in databasstorleken till ett optimalt värde eller för det mesta har varje databas vissa parametrar med auto-growth-funktioner aktiverade.

För sådana databaser måste auto shrink-funktionerna aktiveras av oss när databasen är mindre, en och ingen mer CRUD-operation, så att det kan göra krympning av datafiler möjlig och hämta det lediga utrymmet som vi avsatte målmedvetet för att hålla våra databaser borta från auto-growth-händelser. Fritt utrymme kommer att frigöras automatiskt i datafiler och loggfiler med jämna mellanrum i round-robin sekventiell prioritet av funktionen Automatisk krympning om flera databaser har aktiverat den här funktionen.

För databaser av stor storlek kommer automatisk tillväxt och därefter automatisk krympning att utföras, vilket kommer att resultera i prestandaproblem som leder till fragmentering på systemnivå. För att sammanfatta allt detta bör automatisk krympning inte aktiveras för någon databas av följande skäl:

• Att utföra SQL Servers automatiska krympningsalgoritmer utan något syfte kommer definitivt att slösa bort resurserna i en massiv mängd
• Oavsett om du utför SQL Servers automatiska krympning eller manuell krympning kommer det uppenbarligen att leda till indexfragmentering och detta leder så småningom till att datafilerna också krymper
• Om servern också tvingar fram gränserna för IO-undersystemet kan en körning av krympning driva det till sin spets, Detta kommer att äta upp serverens IO- och CPU-resurser i en massiv mängd
• Systemprestanda kommer att fördröjas av fragmentering på diskenivå i filsystemet, vilket återigen är den direkta effekten av att krympa och växa datafiler ofta

Om du vill förstå mer om Growth and Shrink Events i SQL Server kan du läsa den här artikeln, Get details of SQL Server Database Growth and Shrink Events.

För den kritiska databasen kan manuell krympningsoperation köras på databasfilnivå av experten. Manuell krympningsfilaktivitet kan utföras när raderingsoperationen utförs och därefter har utrymmet återfåtts. Vi måste återuppbygga de fragmenterade indexen när krympningsoperationen utförs eftersom krympningsoperationen kan leda till indexfragmentering. Användaren kan kontrollera procentandelen indexfragmentering med hjälp av T-SQL-meddelanden från DMV:er. Krympning av loggfilen bör dock göras manuellt vid behov och bör inte ingå i den regelbundna underhållsverksamheten.

För att övervaka diskanvändningen regelbundet kan användaren analysera diskanvändningsrapporten innan han/hon utför den manuella krympningsoperationen med hjälp av SSMS, vilket ger dig en inblick i information om data- och loggutrymme för en databas i displayen. Om användaren vill få en instrumentpanel för en beräkning av reserverat utrymme och fritt utrymme för databasfiler, blir Disk Report mycket användbar. Diskrapporten innehåller dock information med hjälp av SQL Server DMV:s. Diskrapporten finns tillgänglig i nedanstående katalog med hjälp av SSMS.

Databas >> Rapporter >> Standardrapporter >> Diskanvändning >>

Här har vi en Diskrapport för databasen. Användaren kan se realtidsstatistik för datafiler och loggfiler. Den här rapporten innehåller primär information om totalt reserverat utrymme, reserverat utrymme för datafiler, reserverat utrymme för transaktionslogg och reserverat utrymme för In-Memory OLTP.

Skrympa databasen automatiskt med hjälp av SQL Server-jobb

Sådana skript skulle kunna utföras genom att användaren schemalägger ett jobb för att utföra databasens krympningsoperation med schemaläggaraktivitet. Det första steget i skriptet är att hitta det fria utrymmet i databasfilen och sedan krympa filen om de definierade kriterierna för det fria utrymmet uppfylls. Det fria utrymmet bör beräknas med hjälp av SQL Server DMV:s.

I grund och botten utförs databasfilövervakningen av databasadministratören, som i sin tur klickar på databasen för att övervaka databasfilens storlek. Om den fördefinierade gränsen överskrids av någon fil måste de utföra sådana åtgärder. Så den aktiviteten kan automatiseras med hjälp av ett SQL Server-jobb som utförs dagligen, veckovis eller månadsvis vid en fördefinierad tidpunkt.

Den bästa metoden är att schemalägga det här jobbet endast på databasloggfilen och övervaka datafilens lediga utrymme manuellt. Eftersom det även kan påverka frågeprestanda. SQL Server DMV returnerar datafilen och loggfilen i samma resultatuppsättning, så filtypen måste delas upp i T-SQL-angivelsen för SQL Server DMV. Innan filen krymps kan användaren tillämpa frågelogik på det totala utrymmet och det fria utrymmet för loggfilen. Om beräkningskriterierna matchar filegenskapen kommer filen att krympa med målfilstorleken.

Kontrollera ledigt utrymme för databasfilerna:

Om sådana kriterier matchar databasfilerna, t.ex. om ledigt utrymme är större än (n) MB/GB, om n(%) ledigt utrymme jämför med totalt utrymme, och många fler. När det inte finns något ledigt utrymme för loggfilen och filstorleken ligger nära parametervärdet för maximal filstorlek måste databasadministratören leta i transaktionsloggen.

Krymp databasfilen:

Här är 5120 en målfilstorlek i MB. Filen kommer alltså att få en storlek på 5120 MB. Konfigurera T-SQL-frågan med nödvändiga kriterier i SQL Server-jobbsteget och schemalägg den på off-timmar.

Alternativet för automatisk krympning av SQL Server kan inte aktiveras för alla databaser. Specifikt hjälper det för den mindre databasen, som utför mindre CRUD-operation jämförelsevis.

Slutsats

I den här artikeln diskuterade vi databasen Auto Shrink-egenskapen i SQL Server för att krympa data- och loggfiler och ta bort oanvänt utrymme. Att krympa en databas är en dyr operation och bör användas med försiktighet.

• Author
• Recent Posts

Jignesh har god erfarenhet inom databaslösningar och arkitektur, Han har arbetat med flera kunder med databasdesign & arkitektur, SQL-utveckling, administration, optimering av frågor, prestandatuning, HA och katastrofåterställning.
Se alla inlägg av Jignesh Raiyani

Nästa inlägg av Jignesh Raiyani (se alla)

• Page Life Expectancy (PLE) in SQL Server – July 17, 2020
• Hur man automatiserar Table Partitioning i SQL Server – juli 7, 2020
• Konfigurera SQL Server Always On Availability Groups på AWS EC2 – juli 6, 2020