SQLShack

Denne artikkelen utforsker bruken av FLERE SQL Server-Transaksjonsloggfiler og prosessen med å fjerne den sekundære transaksjonsloggfilen.

Introduksjon

SOM standard inneholder EN SQL Server-database en primær datafil og transaksjonsloggfil. Det er en god praksis å konfigurere flere datafiler og dele data på tvers av flere datafiler. Vi kan ha disse datafilene i separate lagringsstasjoner for å ha flere IO. Det hjelper datastyring, forbedrer ytelsen, planer backup tilnærminger i henhold til filegroups.

Vi kan også ha flere transaksjonsloggfiler for en database.

SQL Server bruker loggfilene for flere transaksjoner i sekvensiell rekkefølge. Anta at vi har to transaksjonsloggfiler. SQL Server bruker en loggfil om gangen, og NÅR DEN er full, BRUKER SQL Server en annen loggfil. SIDEN SQL Server ikke bruker den parallelt, får vi ingen ytelsesfordel av det. Ideelt sett bør vi bare ha en transaksjonsloggfil per database.

la oss anta at vi har en produksjonsdatabase, og vi mottar et kritisk varsel om at disken går tom for plass. Etter undersøkelse har vi funnet ut at denne stasjonen har en transaksjonsloggfil. På grunn av aktive transaksjoner er DENNE SQL Server-transaksjonsloggfilen full. Vi har prøvd å krympe loggfilen, men det fungerte ikke. Vi sikkerhetskopierer transaksjonsloggen også, men på grunn av aktive transaksjoner kunne den ikke frigjøre nødvendig plass. Legge til en annen transaksjonsloggfil i en egen disk med ledig plass vil løse dette problemet.

SOM SQL Server bruker seriell modus for å skrive data i en transaksjonsloggfil, bør vi fjerne den ekstra loggfilen senere. La oss utforske prosessen med å fjerne en ekstra loggfil.

Opprette en database med FLERE SQL Server-Transaksjonsloggfiler

Koble til EN SQL-forekomst I SQL Server Management Studio. Høyreklikk På Databasenoden i Objektutforsker-ruten og klikk På Den Nye Databasekommandoen:

den åpner Det Nye databasevinduet. Angi et databasenavn og legg til en annen transaksjonsloggfil. For testformål har vi deaktivert automatisk vekst av transaksjonsloggfiler. Den opprinnelige størrelsen på transaksjonsloggfilen ER 8 MB:

når alt er angitt, klikker DU PÅ OK-knappen for å opprette en database i en standard data / loggfilkatalog. Opprette en tabell og sette inn data i:

Vis statusen for den virtuelle loggfilen i SQL Server-transaksjonsloggfiler

i ARTIKKELEN SQL Server-Transaksjonsloggarkitektur utforsket vi det interne i transaksjonsloggfilen. Hver transaksjonsloggfil består av flere virtuelle loggfiler. En transaksjonsloggfil er en kombinasjon av flere virtuelle loggfiler (VLF). Følgende skjermbilde viser den fysiske og logiske arkitekturen til loggfilen:

SQL Server stats en database med minimum VLF basert på den opprinnelige loggstørrelsen og auto grow-filen (basert på auto-grow-konfigurasjonen). I det følgende bildet får vi et glimt AV SQL Server-transaksjonsloggfilen:

NÅR det gjelder en enkelt transaksjonsloggfil, BRUKER SQL Server en sirkulær virtuell loggfilbane. Vi kan sjekke antall virtuelle loggfiler og deres status ved hjelp av følgende:

1. Dbcc LOGINFO(‘Database’) – Det er en gammel setning og fungerer med ALLE SQL Server-versjoner
2. Dynamic management view sys. dm_db_log_info (DBID). DEN er tilgjengelig FRA SQL Server 2016 SP2 eller senere

noen av kommandoene kan brukes TIL VLF-kontrollen. For denne artikkelen vil vi bruke dynamic management view (DMV):

i skjermbildet ovenfor har vi bekreftet at vår utvalgsdatabase inneholder to transaksjonsloggfiler(file_id 2 og file_id 3).

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

la oss sette inn noen flere poster i tabellen slik at aktiv VLF endres:

transaksjonsloggen plassbruk overvåking OG VLF status vil bli gjort MED DMV (sys.dm_db_log_space_usage):

• VLF-status

  1 2	VELG DB_name() Som Databasenavn,File_ID som transaction_log_file_ID, vlf_active , vlf_status fra sys.dm_db_log_info (Db_id (‘MultileLogFiles’));

• Transaksjonsloggbruk (brukt og ledig plass)

  1 2 3 4 5

  VELG total_log_size_in_bytes * 1.0/1024/1024 total_log_size_in_MB, brukt_log_space_in_bytes*1.0/1024/1024 brukt_log_space_in_mb, (total_log_size_in_bytes-brukt_log_space_in_bytes)*1.0/1024/1024 SOM free_log_space_in_MB fra sys. dm_db_log_space_usage;

vi får følgende utdata av de ovennevnte spørringene:

• på venstre side ser vi at når den primære loggfilen (file_id 2) blir full, flyttes den til neste loggfil (file_id 3). PÅ dette punktet har VI AKTIV VLF i både primære og sekundære loggfiler. I en database med full gjenopprettingsmodell trenger vi en transaksjonslogg backup slik at DEN markerer VLF som inaktiv
• høyre side utgang (VED HJELP AV DMV) viser transaksjonsloggfilen brukt plass 8.51 MB. Som du husker, har vi satt 8 MB størrelse for hver transaksjonsloggfil, og vi har deaktivert logg auto-vekst. NÅR den primære loggfilen er full (ved 8 MB), VIL SQL Server bytte til den sekundære transaksjonsloggfilen

Fjern sekundær SQL Server-transaksjonsloggfilen

hvis du vil fjerne den sekundære transaksjonsloggfilen (fil-id 3), bruker vi den redigerte Alter database-setningen.

vi vil legge TIL FJERN FIL klausulen og angi filen som vi vil fjerne:

Utførelse av denne setningen vil resultere i følgende feilmelding:

Merk: den aktive transaksjonsloggfilen kan ikke fjernes.

tidligere så vi at NÅR den primære loggfilen blir full, BRUKER SQL Server den sekundære loggfilen. Vi må lage en sekundær transaksjonslogg tom, slik at vi kan fjerne den.

I SQL-databasen med en full gjenopprettingsmodell bruker VI sikkerhetskopier av transaksjonslogger slik AT SQL Server kan avkorte loggene. Vi kan trenge flere loggbackups for dette, avhengig av transaksjonsloggstørrelse, aktiv VLF og aktiv transaksjon.

la oss utføre en full database og transaksjonslogg backup:

• full database backup

  1	Backup Database MultipleLogFiles til disk=’C:\Temp\MultipleLogFiles.bak’

• Sikkerhetskopiering Av Transaksjonslogg

  1	Backup logg MultipleLogFiles til disk=’C:\Temp\MultipleLogFiles_log.trn’

når sikkerhetskopien er fullført, kontroller VLF-statusen. Den aktive VLF skal være i den første loggfilen(primær) slik at vi kan fjerne den sekundære loggfilen. Vi har bekreftet at bare den primære loggfilen (file_id 2) er aktiv (VLF Status 2):

nå skal den sekundære transaksjonsloggfilen fjernes uten problemer. La oss utføre den redigerte Alter database-setningen igjen. Den sekundære transaksjonsloggfilen vil bli fjernet:

når den sekundære transaksjonsloggfilen er fjernet, må du kontrollere DEN ved HJELP AV GUI samt T-SQL med system view sys.database_files:

den fjernede sekundære transaksjonsloggfilen er fortsatt til stede i henhold til følgende skjermbilde:

høyreklikk nå på databasen og se eksisterende filer. Vi ser den fjernede transaksjonsloggfilen i GUI også. Men hvorfor?

la oss kjøre den redigerte Alter database-setningen på nytt og se om SQL Server igjen fjerner transaksjonsloggfilen.

vi fikk meldingen OM AT SQL Server ikke kunne finne den angitte loggfilen:

SQL Server fjerner transaksjonsloggfilen etter en etterfølgende logg backup. La oss ta en annen logg backup og kontrollere at transaksjonsloggfilen fortsatt eksisterer:

Kontroller transaksjonsloggfilen i BÅDE GUI-og T-SQL-metoder. Vi ser at den fjernede transaksjonsloggfilen ikke vises nå.

Konklusjon

i denne artikkelen utforsket vi bruken av den sekundære SQL Server-transaksjonsloggen og prosessen med å fjerne den. Du bør unngå å bruke flere transaksjonsloggfiler, spesielt på produksjonsdatabasen. Du bør ta en database backup før du planlegger en aktivitet og gjøre det i ikke-produktivitet timer.

• Forfatter
• Siste Innlegg

Som EN MCSA-sertifisert Og Microsoft-Sertifisert Trener I Gurgaon, India, med 13 års erfaring, Jobber Rajendra for en rekke store selskaper som fokuserer på ytelsesoptimalisering, overvåking, høy tilgjengelighet og katastrofegjenopprettingsstrategier og implementering. Han er forfatter av hundrevis av autoritative artikler om SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git og relaterte teknologier som har blitt sett av over 10m lesere til dags dato.
han er skaperen av en av de største gratis online samlingene av artikler om et enkelt emne, med sin 50-delers serie På SQL Server Alltid På Tilgjengelighetsgrupper. Basert på hans bidrag TIL SQL Server-fellesskapet, har han blitt anerkjent med ulike priser, inkludert den prestisjetunge «Best author of the year» kontinuerlig i 2020 og 2021 På SQLShack.
Raj er alltid interessert i nye utfordringer, så hvis du trenger konsulenthjelp på et emne som er dekket i hans skrifter, kan Han nås på rajendra.gupta16 @ gmail.com
Vis alle innlegg Av Rajendra Gupta

Siste innlegg Av Rajendra Gupta (se alle)

• Bruk ARM-maler til å distribuere Azure container instances med SQL Server Linux – bilder-21. desember 2021
• Ekstern skrivebordstilgang for AWS RDS SQL Server Med Amazon Rds Custom-14. desember 2021
• LAGRE SQL Server – filer I Vedvarende Lagring For Azure Container Instances-desember 10, 2021