SQLShack

den här artikeln utforskar användningen av flera SQL Server-transaktionsloggfiler och processen att ta bort den sekundära transaktionsloggfilen.

introduktion

som standard innehåller en SQL Server-databas en primär datafil och transaktionsloggfil. Det är en bra praxis att konfigurera flera datafiler och dela data över flera datafiler. Vi kan ha dessa datafiler i separata lagringsenheter för att ha flera IO. Det hjälper datahantering, förbättrar prestanda, planerar backup metoder enligt filgrupper.

vi kan också ha flera transaktionsloggfiler för en databas.

SQL Server använder flera transaktionsloggfiler i ordningsföljd. Antag att vi har två transaktionsloggfiler. SQL Server använder en loggfil i taget och när den är full använder SQL Server en annan loggfil. Eftersom SQL Server inte använder den parallellt får vi ingen prestandafördel av den. Helst bör vi bara ha en transaktionsloggfil per databas.

låt oss anta att vi har en produktionsdatabas, och vi får en kritisk varning om att disken tar slut på utrymme. Efter undersökningen har vi upptäckt att den här enheten har en transaktionsloggfil. På grund av aktiva transaktioner är denna SQL Server-transaktionsloggfil full. Vi har försökt krympa loggfilen, men det fungerade inte. Vi säkerhetskopiera transaktionsloggen också, men på grund av aktiva transaktioner, det kunde inte släppa behövs utrymme. Att lägga till en annan transaktionsloggfil på en separat disk med ledigt utrymme löser problemet.

eftersom SQL Server använder seriellt läge för att skriva data i en transaktionsloggfil, bör vi ta bort den extra loggfilen senare. Låt oss undersöka processen att ta bort en extra loggfil.

skapa en databas med flera SQL Server-transaktionsloggfiler

Anslut till en SQL-instans i SQL Server Management Studio. Högerklicka på noden databaser i rutan Object Explorer och klicka på kommandot Ny Databas:

det öppnar fönstret Ny databas. Ange ett databasnamn och Lägg till en annan transaktionsloggfil. För teständamål har vi inaktiverat automatisk tillväxt av transaktionsloggfiler. Den ursprungliga storleken på transaktionsloggfilen är 8MB:

när allt är inställt klickar du på OK-knappen för att skapa en databas i en standardkatalog för data/loggfiler. Skapa en tabell och infoga data i:

Visa status för den virtuella loggfilen i SQL Server – transaktionsloggfiler

i artikeln, SQL Server Transaction Log Architecture, undersökte vi det interna I transaktionsloggfilen. Varje transaktionsloggfil består av flera virtuella loggfiler. En transaktionsloggfil är en kombination av flera virtuella loggfiler (VLF). Följande skärmdump visar loggfilens fysiska och logiska arkitektur:

SQL Server stats en databas med minsta VLF baserat på den ursprungliga loggstorleken och auto grow-filen (baserat på automatisk tillväxtkonfiguration). I följande bild får vi en glimt av SQL Server – transaktionsloggfilen:

när det gäller en enda transaktionsloggfil använder SQL Server en cirkulär virtuell loggfilsökväg. Vi kan kontrollera antalet virtuella loggfiler och deras status med följande:

1. DBCC LOGINFO (’Database’) – det är ett gammalt uttalande och fungerar med alla SQL Server-versioner
2. Dynamic management view sys.dm_db_log_info (DBID). Den är tillgänglig från SQL Server 2016 SP2 eller senare

vilken som helst av kommandona kan användas för VLF-kontrollen. För den här artikeln kommer vi att använda dynamic management view (DMV):

i ovanstående skärmdump har vi verifierat att vår exempeldatabas innehåller två transaktionsloggfiler (file_id 2 och file_id 3).

• File_id 2 har aktiv VLF (vlf_active=1 och vlf_status = 2)
• File_id 3 har ingen aktiv VLF (vlf_active = 0 och vlf_status=2)

låt oss infoga några fler poster i tabellen så att aktiv VLF ändras:

transaktionsloggens rymdanvändningsövervakning och VLF-status kommer att göras med DMV (sys.dm_db_log_space_usage):

• VLF-status

  1 2	välj DB_name() som databasnamn,File_ID som transaction_log_file_ID, vlf_active , vlf_status från sys. dm_db_log_info (DB_ID (’MultileLogFiles’));

• Transaktionslogganvändning (använt och ledigt utrymme)

  1 2 3 4 5

  välj total_log_size_in_bytes * 1.0/1024/1024 total_log_size_in_MB, använd_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes-used_log_space_in_bytes)*1.0/1024/1024 som free_log_space_in_MB från sys. dm_db_log_space_usage;

vi får följande utmatning av ovanstående frågor:

• på vänster sida ser vi att när den primära loggfilen (file_id 2) blir full, flyttas den till nästa loggfil (file_id 3). Vid denna tidpunkt har vi aktiv VLF i både primära och sekundära loggfiler. I en databas med en fullständig återställningsmodell behöver vi en transaktionsloggbackup så att den markerar VLF som inaktiv
• den högra sidan (med DMV) visar transaktionsloggfilen som används utrymme 8.51 MB. Som ni minns, vi har satt 8 MB storlek för varje transaktionsloggfil och vi har inaktiverat log auto-tillväxt. När den primära loggfilen är full (vid 8 MB) växlar SQL Server till den sekundära transaktionsloggfilen

ta bort sekundär SQL Server-transaktionsloggfil

för att ta bort den sekundära transaktionsloggfilen (file id 3) använder vi den redigerade Alter database-satsen.

vi lägger till klausulen ta bort fil och anger filen som vi vill ta bort:

exekvering av detta uttalande kommer att resultera i följande felmeddelande:

Obs: den aktiva transaktionsloggfilen kan inte tas bort.

Tidigare såg vi att när den primära loggfilen blir full använder SQL Server den sekundära loggfilen. Vi måste göra en sekundär transaktionslogg tom, så vi kan ta bort den.

i SQL-databasen med en fullständig återställningsmodell använder vi transaktionsloggbackups så att SQL Server kan trunkera loggarna. Vi kan behöva flera loggbackups för detta beroende på transaktionsloggstorlek, aktiv VLF och aktiv transaktion.

Låt oss utföra en fullständig databas och transaktionslogg backup:

• Full databas backup

  1	backup databas multiple logfiles till disk=’C:\Temp\MultipleLogFiles.bak’

• transaktionslogg backup

  1	backup log multiple logfiles till disk=’C:\Temp\MultipleLogFiles_log.trn’

när säkerhetskopieringen är klar kontrollerar du VLF-statusen. Den aktiva VLF ska vara i den första loggfilen(primär) så att vi kan ta bort den sekundära loggfilen. Vi har verifierat att endast den primära loggfilen (file_id 2) är aktiv (VLF-Status 2):

nu bör den sekundära transaktionsloggfilen tas bort utan problem. Låt oss köra det redigerade Alter database-uttalandet igen. Den sekundära transaktionsloggfilen kommer att tas bort:

när den sekundära transaktionsloggfilen har tagits bort, verifiera den med GUI samt T-SQL med system view sys.databasfil:

den borttagna sekundära transaktionsloggfilen finns fortfarande enligt följande skärmdump:

högerklicka nu på databasen och visa befintliga filer. Vi ser också den borttagna transaktionsloggfilen i GUI. Men varför?

Låt oss köra den redigerade Alter database-satsen igen och se om SQL Server igen tar bort transaktionsloggfilen.

vi fick meddelandet att SQL Server inte kunde hitta den angivna loggfilen:

SQL Server tar bort transaktionsloggfilen efter en efterföljande loggbackup. Låt oss ta en annan loggbackup och verifiera att transaktionsloggfilen fortfarande finns:

verifiera transaktionsloggfilen i både GUI och T-SQL-metoder. Vi ser att den borttagna transaktionsloggfilen inte dyker upp nu.

slutsats

i den här artikeln undersökte vi användningen av den sekundära SQL Server-transaktionsloggen och processen att ta bort den. Du bör undvika att använda flera transaktionsloggfiler, särskilt i produktionsdatabasen. Du bör ta en databas backup innan du planerar någon aktivitet och göra det i icke-produktivitet timmar.

• författare
• Senaste inlägg

som MCSA-certifierad och Microsoft-certifierad tränare i Gurgaon, Indien, med 13 års erfarenhet, arbetar Rajendra för en mängd stora företag med fokus på prestationsoptimering, övervakning, hög tillgänglighet och katastrofåterställningsstrategier och implementering. Han är författare till hundratals auktoritativa artiklar om SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git och relaterad teknik som hittills har visats av över 10 miljoner läsare.
han är skaparen av en av de största gratis online-samlingarna av artiklar om ett enda ämne, med sin 50-delade serie på SQL Server alltid på Tillgänglighetsgrupper. Baserat på hans bidrag till SQL Server-communityn har han erkänts med olika utmärkelser inklusive den prestigefyllda ”årets bästa författare” kontinuerligt 2020 och 2021 på SQLShack.
Raj är alltid intresserad av nya utmaningar så om du behöver konsulthjälp om något ämne som omfattas av hans skrifter kan han nås på rajendra.Gupta 16 @ gmail.com
Visa alla inlägg av Rajendra Gupta

Senaste inlägg av Rajendra Gupta (se alla)

• använd ARM-mallar för att distribuera Azure container instances med SQL Server Linux – bilder-21 December 2021
• Remote desktop access för AWS RDS SQL Server med Amazon RDS Custom-14 December 2021
• lagra SQL Server-filer i beständig lagring för Azure Container Instances-December 10, 2021