2010-03-25

Transaktionsloggen är jättestor, HJÄLP!!! (aka, vad är "Full" och "Simple" Recovery Mode)

Den absolut vanligaste frågan jag får när det gäller MS SQL har att göra med transaktionsloggar som växer okontrollerat... Därför skrev jag för ett par år sedan ihop detta.. :)

Ett vanligt problem med en SQL server är att databasen är på 100MB och transaktionsloggen är på typ 10GB...

Det känns inte helt rätt, och det är det oftast inte heller. Ett vanligt sätt för att lösa detta är att sätta databasen i "simple mode" (sätts i SQL 2000 i Enterprise Manager genom att högerklicka på en databas, välja "properties" och under fliken "options" ändra på "Recovery model". I SQL 2005 och 2008 högerklickar man på databasen, väljer "properties" och under "options" väljer man "recovery model"... Hyffsat lika alltså.  :)).

Dock är detta inte alltid enbra lösning!!!

Först kommer en förklaring om vad en databas egentligen är. Mycket av detta kommer från en tråd på http://www.itproffs.se/ skriven av Jesper Kråkhede (så lite creds går till honom, men han hade en ganska bra förklaring).. :)

En databas består av två eller fler filer:
  • Databasfilen med filändelsen ".MDF" (kan vara flerastycken och då heter övriga NDF)
  • Transaktionsloggen med filändelsen ".LDF"
Självklart kan man ändra dessa, men detta är standardvärden..

Databasfilen innehåller alla data, medans transaktionsloggen innehåller alla händelser som ändrar data i databasen.


Ett exempel.
En tabell innehåller värdena:
Kalle, Anka, Disney, 1
Arne, Anka, NULL, 2
Nemi, NUL, Sydsvenskan, 3

Nu skickar vi kommandot att sätta in en rad till i databasen.
INSERT INTO MinTabell VALUES ('Musse','Pigg','Disney','4')

Vad som händer då är att i transaktionsloggen skrivs det in en rad om att denna uppdatering skall göras.
Därefter görs själva uppdateringen i databasen.
När databasen är uppdaterad så markeras raden i Transaktionsloggen som genomförd.

Nu visar det sig att det skulle inte göras någon uppdatering i databasen så användaren gör en "ROLLBACK" (dvs ångrar).
Då skickar användaren en kommando om att ta bort raden:
DELETE FROM MinTabell WHERE ID = '4'

Vad som händer då är att i transaktionsloggen skrivs det in en rad om att raden där det i kollumnen "ID" står siffran fyra skall raderas. Därefter görs själva uppdateringen i databasen.
När databasen är uppdaterad så markeras raden i Transaktionsloggen som genomförd.

Detta innebär att databasen inte har kvar datat, men i transaktionsloggen finns den med två gånger, både att den skall läggas till och sedan att en skall tas bort.

Varför vill man ha det så här??
Jo, om disken där databasfilen skulle gå sönder så kan du återställa databasfilen från senaste backup och sedan återställa transaktionsloggen och plötsligt så har du inte tappat någon data alls. Finurligt va?


Så här fungerar det om du har databasen i "Full Recovery Mode". Detta är också den inställning som en databas får som standard..

Om databasen står i "Simple Recovery Mode" så när vi gör första uppdateringen (när nya raden skapas) kommer följande att hända:
  • I transaktionsloggen skrivs det in en rad om att denna uppdatering skall göras.
  • Därefter görs själva uppdateringen i databasen.
  • När databasen är uppdaterad så markeras raden i Transaktionsloggen som genomförd och att raden i transaktionsloggen får skrivas över.

Nästa transaktion kommer sedan att skriva över "vår" rad i transaktionsloggen.

Detta i sin tur innebär att Transaktionsloggen kommer aldrig att innehålla mer än de ännu inte genomförda uppdateringarna till databasen. Transaktionsloggen används enbart för att kunna säkerställa att databasen går att återställa till ett koncistent läge om det skulle ske en serverkrash.

Om databasen står som "Simple Recovery Mode" så går det inte att göra transaktionslogg-backupper.

Microsoft skriver så här:

"Important. Simple Recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable.
When using Simple Recovery, the backup interval should be long enough to keep the backup overhead from affecting production work, yet short enough to prevent the loss of significant amounts of data. "


  
Då kommer vi till själva problemet.


Varför blir transaktionsloggen 10GB när databasen bara är 100MB? Jo, det är mycket enkelt. Transaktionsloggen "töms" när man gör backup på den. Troligen så gör du inte backup på transaktionsloggen... Fy på dig!! :)


Hur löser vi detta då? Jo, ta en backup på transaktionsloggen och starta sedan "Query Analyser" och skriv:

USE DatabasNamn

GO

DBCC SHRINKFILE(MinDatabas_Log)

GO

och tryck på F5

(här utgår jag från att filnamnet för min transaktionsloggen är "MinDatabas_Log.LDF")


Detta kommando kommer att tömma all data i transaktionsloggen från slutet av transaktionsloggen och fram tills det att den första "aktiva" delen av transaktionsloggen.

En transaktionslogg kan aldrig krympas till att bli mindre än transaktionsloggen i databasen "Model" så se till att den inte är på flera Gigabyte. :)


Det skall sägas att bara för att man gör som jag beskrivit ovan så behöver inte transaktionsloggen krympa speciellt mycket. Det har att göra med att man bara kan krympa bort de virtuella loggfiler som ligger i slutet av den fysiska loggfilen som inte innehåller någon del av den logiska loggfilen. (finns en ganska bra förklaring av detta här: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp)

  
Sammanfattningsvis så kan man säga att om inte transaktionsloggen krymper första gången man gör som ovanstående så gör det igen, då skall den nämligen krympa. (detta eftersom att den logiska log-filen flyttas till början av den fysiska loggfilen) :)

1 kommentar:

  1. En klassiker är också att någon sätter upp backup både via sql studio och någon annan via backupprogram, vilket resulterar i att alla transaktionsloggsbackuper bägge börjar falera (fullbackup kommer gå igenom).

    /Skuggan

    SvaraRadera

Related Posts Plugin for WordPress, Blogger...