Vi får ganska ofta frågor om varför transaktionsloggsfilen växer och hur stor den ska vara. 10 % av databasens storlek? 100MB? 100GB? En snabbsökning på Internet ger många träffar och ”det beror på” är ofta standardinledningen på dessa. Vad beror det på då? Jo, det är ett lika luddigt svar på den frågan; det beror på hur snabbt loggposter skapas och tas bort i transaktionsloggsfilen av de processer som internt använder dem i SQL Server.
Alla transaktioner skapar loggposter i transaktionsloggsfilen med information om vilka förändringar som skett i databasen. Dessa behövs för att kunna hålla databasen konsistent om något problem skulle inträffa. I vissa fall kan loggningen minimeras beroende på recovery model på databasen och SQL anropet som gjorts. Transaktionsloggsfilen måste rensas på loggposter för att inte bli oändligt stor men de aktiva loggenposterna som behövs för att återställa databasen kan aldrig tas bort.
SQL Server tar bort de icke aktiva loggposterna när BACKUP LOG kommandot körs för databaser i FULL eller BULK_LOGGED recovery model eller, om databasen är i simple recovery model, varje gång en checkpoint görs i databasen. En checkpoint är lite förenklat det senaste tillfället all data i minnet skrevs till disk och hanteras normal automatiskt av SQL Server. Observera att ta bort de inaktiva loggposterna i transaktionsloggsfilen inte krymper den fysiska filen utan det är bara innehållet som tas bort.
De vanligaste orsakerna som vi stöter på när transaktionsloggen inte kan rensas och fortsätter att växa är att en eller flera långa transaktioner är aktiva eller, för databaser i FULL eller BULK_LOGGED recovery model, att ingen transaktionsloggsbackupp har gjorts på dem. En annan orsak till att en transaktionsloggsfil växer mycket för databaser i FULL eller BULK_LOGGED recovery model kan vara ombyggnad av ett index eftersom det sker i en transaktion. Så även alla loggposter för detta måste få plats i transaktionsloggsfilen.
Hur stor ska då transaktionsloggsfilen då vara? Jo, lite enkelt kan man säga att den behöver vara stor nog för att innehålla alla loggposter mellan två checkpoints alternativt transaktionsloggsbackupper samt rymma det största indexet.
Så lita inte på några svar med generella storlekar, det beror på, och det är inte helt enkelt att bestämma hur stor en transaktionsloggsfil ska vara. Det kräver lite arbete för respektive databas med att konfigurera backuppintervall, index storlekar etc. för att nå fram till en passande storlek på transaktionsloggsfilen och hålla den där.