[Sql Server] Waarom wordt mijn logfile zo groot?

[sig69]

Even een vraagje voor de Sql Server specialisten hier. Ik probeer parttime onze database servers een beetje te monitoren / in gezonde staat te houden, en laatst liep ik tegen een interessant dingetje aan. Eén van onze databases heeft een logfile van meer dan 10GB groot, waar slechts 0,3% van gebruikt wordt. Het is een simpele database met slechts één tabel met ongeveer 10M records die dagelijks ververst wordt. In mijn testomgeving kan ik het scenario repliceren, het dagelijkse import proces bestaat uit twee stappen:
-delete from table
-bulk insert uit csv file
Ik heb de delete vervangen door een truncate en dit scheelt de helft ongeveer, de logfile wordt nu ongeveer 4,5GB groot. Op zich prima natuurlijk, maar toen liep ik tegen iets raars aan: als ik de tabel kopieer via de import/export data wizard groeit de logfile niet. Ik zie dat de % log usage wel gestaag oploopt maar bij de 60-70% valt hij weer terug naar 1-2% en begint weer op te lopen, etc.
Lang verhaal, ik vraag me dus af wat de import/export data anders doet dan de bulkcopy, waardoor het log gebruik zo verschilt.

[Meekoh]

Is het een probleem dat hij zo groot wordt (heb wel veel grotere gezien).
In wat voor Recovery mode draait die database? (Full, Bulk Logged of Simple)
Vind er Index maintenance plaats?
Wat is je backup strategie? (hoe vaak full/hoe vaak log)

edit: ik vermoed dat de import/export zelf checkpoints triggered waardoor je log ruimte weer wordt vrijgegeven.

[ Voor 19% gewijzigd door Meekoh op 23-10-2013 17:22 ]

[CMD-Snake]

Het wordt aanbevolen om bij bulk imports je log file tijdelijk uit te zetten. Anders komt de massa import ook daar terecht.

Vergeet ook niet maintance plans te maken. Je kan in feite na elke full back-up je transaction log legen (overigens zou dit automatisch moeten gaan dacht ik...)

[sig69]

Wat antwoorden:

Meekoh schreef op woensdag 23 oktober 2013 @ 17:21:
Is het een probleem dat hij zo groot wordt (heb wel veel grotere gezien).

In principe niet, maar mijn ocd stoort zich er aan dat een database waaruit alleen de hele dag gelezen wordt 10GB aan log space in gebruik houdt

In wat voor Recovery mode draait die database? (Full, Bulk Logged of Simple)

Simple

Vind er Index maintenance plaats?

Nou ja, index maintenance:
-De indices worden disabled
-Truncate table
-Bulk copy
-De indices worden enabled

Wat is je backup strategie? (hoe vaak full/hoe vaak log)

Er wordt volgens mij dagelijk een full back van gemaakt, daar ga ik niet over.

CMD-Snake schreef op woensdag 23 oktober 2013 @ 17:56:
Het wordt aanbevolen om bij bulk imports je log file tijdelijk uit te zetten. Anders komt de massa import ook daar terecht.

Ik wist niet dat dat kon, zou voor deze database prima zijn. Ga ik uitzoeken.

Vergeet ook niet maintance plans te maken. Je kan in feite na elke full back-up je transaction log legen (overigens zou dit automatisch moeten gaan dacht ik...)

Dat gebeurt nu al ja, daarom is de 10GB log file zo goed als leeg

[Wilko990]

CMD-Snake schreef op woensdag 23 oktober 2013 @ 17:56:
Het wordt aanbevolen om bij bulk imports je log file tijdelijk uit te zetten. Anders komt de massa import ook daar terecht.

Vergeet ook niet maintance plans te maken. Je kan in feite na elke full back-up je transaction log legen (overigens zou dit automatisch moeten gaan dacht ik...)

De database staat in simple recovery mode, je kunt geen transactie log back-up maken en dus geen log truncate uitvoeren. Ook bij een simple recovery model worden transacties gelogd echter zal SQL server zelf regelmatig een checkpoint zetten waarna een truncate plaats vind.

Overigens is je truncate table command een niet gelogde transactie maar je bulk import wordt wel gelogd. Optie is om zoals aangegeven over te stappen naar een full recovery model en dan voor de import tijdelijk te switchen naar bulk logged.

Je kan te maken hebben met een langlopende transactie, DBCC OPENTRAN() die een truncate verhinderd. Kijk ook eens naar het actieve deel van de logfile, DBCC LOGINFO. Status 2 betekent actief dus wanneer een groot deel van je vlf's actief zijn kan er simpelweg geen truncate plaats vinden.