SQL 2008 R2 Transaction Log

Binnen SQL Server 2008 R2 zijn tal van vernieuwingen ten opzichten van de voorgaande versies. Echter bij het toepassen van SQL Server 2008 R2 kwam ik er al snel achter dat de transaction log file al snel groter en groter werdt.

Wat doet het transaction log? Eigenlijk heel eenvoudig. Op het moment dat je een bewerking uitvoert in een applicatie die gekoppeld is met de SQL Server worden de acties die zijn uitgevoerd bijgehouden in een log bestand. Aangezien soms over een periode van een dag door meerdere gebruikers, gebruik wordt gemaakt van een SQL database kunnen dit heel veel bewerkingen zijn. Om te voorkomen dat indien je SQL server crashed je al deze bewerkingen kwijt bent. Kan je door middel van een recovery je database repareren met minimaal data verlies.†Echter houd bij een repair wel het volgende in je achter hoofd!!

  • je hebt een goede Back-Up van je SQL database bij de hand
  • het transaction log bestand is intact en tussen de laatste back-up en de crash is het bestand niet verwijderd cq leeg gemaakt.

Via de Repair modus in SQL Server management kan er dan een rebuild worden gedaan van je database.

Het is echter ook mogelijk dat je transaction log file na verloop van tijd erg groot zal worden, groter dan je daadwerkelijke database. Dit komt voornamelijk dat alle acties die zijn uitgevoerd worden opgeslagen in het log bestand. Bij voorgaande versies van SQL Server werd automatisch het log bestand verkleind middels een truncate actie die plaats vond na een full back-up. In SQL 2008 R2 wordt dit niet meer gedaan. Dit kan na verloop van tijd dus je harddisk ruimte aantasten terwijl dit niet nodig is. Met een simpel SQL commando die je vanuit het management consol moet uitvoeren kan je het log bestand legen. Procedure is wel dat je van zowel je Database als het Log bestand een back-up hebt. En afhankelijk van de groei van je transaction log zou je dit periodiek kunnen uitvoeren het is niet noodzakelijk om dit na elke full-backup uit te voeren. dit zou je zelf dus kunnen inschatten en inplannen in je dagelijkse werkzaamheden.

Hier onder vind je het script om handmatig dan wel dmv een geplande actie in de SQL management het script dat ik hiervoor gebruik.

USE ;<DBNAME>
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE <DBNAME>
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);† — here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
— Reset the database recovery model.
ALTER DATABASE <DBNAME>
SET RECOVERY FULL;
GO