SQL en Log file groei

[technoaddict]

Hallo allemaal,
Vrijwel geen kennis nog met SQL en loop tegen een issue aan waar ik graag wat informatie over zou willen hebben. Ik heb al een en ander gelezen op internet maar kom er niet helemaal uit.

Een disk specifiek voor de Log files loopt elke nacht vol. Ik zie in de Disk Usage rapportage van een database dat de Auto Growth ervoor zorgt dat de log midden in de nacht heel snel groter wordt tot de schijf vol is.
Een Job dat een shrink doet op de log lost het probleem op tot de volgende nacht. Dan speelt het weer.

Ik vraag me af hoe het kan dat een database van 40 GB met een Log file van 4 MB (nu) een Log van 45 GB kan maken?
Ik vraag me af of het shrinken van de DB een job moet zijn die elke nacht automatisch loopt? Of dat het iets periodieks moet zijn. De backup van SQL is volgens de backup man goed gegaan. Maar zou dat er niet voor moeten zorgen dat de Log file kleiner wordt?
De Auto Growth setting stond zo dat de Log file mocht groeien tot meer dan de disk in grootte is. Dat is nu aangepast naar 30 GB (de log groeit tot 45 GB en dan is de schijf vol).. Maar dan vraag ik me af of het juist is om te doen? Stel dat de 30 GB bereikt wordt.. Wat dan? Applicatie down?

Dank alvast.

[ElCondor]

Een log file bevat alle transacties die op de database gedaan worden. Als er dus 's nachts een proces loopt dat HEEL veel transacties op de DB uitvoert, dan kan het log exponentieel groeien.
Waar je achteraan moet is welk proces is verantwoordelijk voor die hoeveelheid transacties. Wellicht gaat daar iets mis. Mochten het toch valide transacties zijn dan is het aan te raden inderdaad een maintenance job in te plannen die 's nachts de logs ieder uur backupped en truncate. Dat komt voor en hoeft geen probleem te zijn.
Wat ook mogelijk zou zijn is dat het proces dat verantwoordelijk is voor dat grote aantal transacties dat dit één job is. Wat je zou kunnen doen, als deze job niet hoeft terug te rollen is de DB omzetten naar bulk-logged en dan na het voltooien van de job weer op full logging te zetten. Let wel je kunt dan de DB alleen terugrollen naar vóór of na de job. Je kunt individuele transacties die deel uitmaken van de job niet terug rollen.

Om je laatste vraag nog te beantwoorden:
Als je log volloopt tegen zijn max en er wordt daarna nog gepoogd berwerkingen op de database uit te voeren, dan zal SQL de melding geven, mag niet meer, transaction log is vol. Voer een backup uit en truncate het log. Hoe je applicatie daar dan verder mee omgaat, dat ligt aan de aplicatie zelf.

-Aanvulling- een backup, zelfs een full backup, is niet hetzelfde als een Trancaction log backup en aléén een transaction log backup zorgt voor het truncaten van het log. Het truncaten van het log maakt het mogelijk om nieuwe transacties aan het log toe te voegen, maar op disk wordt het bestand zelf niet kleiner. Je kunt dan inderdaad in je maintenance plan opnemen om direct na je transaction log backup een shrink transaction log files te doen, maar dat moet dan direct er achteraan, want als in de tussentijd nieuwe transactions in het log zijn bijgeschreven, dan kan de log file niet verder verkleind worden.

Ik hoop dat ik het zo een beetje duidelijk heb uitgelegd.
Je maintenance plan zou er dan zo uit moeten zien:

• Backup database
• On success: Backup transaction log
• On success: shrink transaction log file, release space to file system

[ Voor 25% gewijzigd door ElCondor op 20-02-2015 11:38 ]

[justVR]

Als aanvulling op ElCondor, een goede uitleg op Stackoverflow:
http://stackoverflow.com/...ql-server-transaction-log

[technoaddict]

Dank voor je reactie.
Je geeft aan dat het nuttig kan zijn een job te hebben draaien die elk uur backupped. Maar dat backuppen is neem ik aan iets wat je met backup tooling moet doen?

Ik zie ook dat er een Daily maintenance job draait met een Reorganize Index job op alle databases. Kan dat ook voor groei zorgen in de Log?

Zal het artikel eens lezen justVR. Thanks all!

[Meekoh]

Ja die reorganize is 99% zeker de oorzaak hiervan. Je zou eraan kunnen denken om rebuilds te doen ipv reorganize. Rebuilds komen namelijk niet in je Transaction log terecht.

[technoaddict]

Hm 1e artikel dat ik zie over Reorganizen van de index:

Are you sure you aren't talking about a rebuild? An index REBUILD is slow, expensive, consumes a humongous amount of log.

http://dba.stackexchange....l-during-index-reorganize

[ElCondor]

Om op je vraag over backups in SQL terug te komen. SQL heeft zijn eigen backup procedures. 3rd party backup tooling zijn vaak niets anders dan een (grafische) schil rondom standaard SQL backup statements die je altijd ook in je Management Studio kunt uitvoeren. Kun je het in je management studio uitvoeren, dan kun je er ook een job van maken om op te nemen in je maintenance plan.

Hoe je een transaction log backup en logfile shrink in een 3rd party backup tool uitvoert, dat hangt dan weer van de tool af.
Ik zelf gebruik bijvoorbeeld Backup Exec en daar heb ik inderdaad twee Tasks in draaien die achter elkaar de DB's backuppen en vervolgens een Transaction log backup doen en vervolgens het log truncaten.

[technoaddict]

Ah, ik zie in de backup tooling ook wel wat over de backups. Elke avond 9 PM een Full SQL backup en elke middag 11:58 een backup van de Transaction Logs.

In SQL MS zie ik een Maintenance Cleanup task die Backup files verwijderd, met meer onderin de setting dat bestanden ouder dan 4 weken verwijderd moeten worden. Maar volgens mij gaat dit niet over het kleiner krijgen van een Log File.

Mijn idee is dus dat je het backuppen van de Transaction Log eerder 11:58 PM moet uitvoeren dan AM.. Of, doe het meerdere keren per dag. En dat je daarna een actie moet uitvoeren om de Log terug te brengen tot enkele MB's..

Ik zie op de database properties wel dat Auto Shrink Enabled staat. Geen idee of dat betrekking heeft op wat ik wil bereiken en wanneer dat dan zou lopen...

[Jaymz]

Doe je indexmaintenance *alsjeblieft* niet met de Management Studio mogelijkheden. Dan wordt namelijk *elke* index rebuild. Eén van de beste oplossingen hiervoor vind je op https://ola.hallengren.com/ Ook de grote namen in SQL Server-land raden de maintenance scripts van deze meneer aan

Als dan alleen die tabellen/indexen worden rebuild die nodig zijn loop je niet meer tegen een wildgroeiende logfile aan Zolang je indexmaintenance (rebuild/reorganise) doet via de standaard Management Studio mogelijkheden zul je tegen een wildgroeiende log aanlopen. Als de hele database wordt rebuild dan heb je minstens net zoveel ruimte nodig als de Db groot is

Over logtruncation: zie http://www.sqlskills.com/...ansaction-log-throughput/ en http://www.sqlskills.com/...vlfs-too-many-or-too-few/

Wat je backups betreft:

Wat is je maximaal acceptabele dataverlies (bij calamiteiten)?

Op basis van dat antwoord richt je je backups in. Uurlijkse, zelfs kwartierlijkse transactielogbackups komen voor.

Als deze Database op enige mate belangrijk is voor je (/jou) bedrijf is het raadzaam wat meer kennis op dit gebied op te doen. Eigenlijk is dit SQL-Beheer basiskennis (die veel te vaak ontbreekt overigens )

Over Db Autoschrink: zsm uitzetten. Die optie is een worse-practise
http://www.sqlskills.com/...t-shrink-your-data-files/

Meekoh schreef op vrijdag 20 februari 2015 @ 11:52:
Ja die reorganize is 99% zeker de oorzaak hiervan. Je zou eraan kunnen denken om rebuilds te doen ipv reorganize. Rebuilds komen namelijk niet in je Transaction log terecht.

Fout, allebei zijn fully logged operations. De één wat meer dan de ander Daarnaast: In het FULL Recovery model wordt *alles* wat er in de Db gebeurd gelogged. Enige uitzondering is de version store in tempdb

[ Voor 17% gewijzigd door Jaymz op 20-02-2015 14:48 ]

[technoaddict]

Ik zal de linkjes eens bekijken. Dank daarvoor.
Morgen (of maandag ) zullen we weten of het disablen van de Reorganize Index heeft geholpen. Dan weten we in ieder geval of we hier verder mee moeten of dat de oorzaak ergens anders gezocht moet worden.

Max dataverlies is mij onbekend. Ik ben nieuw dus dat zal ik na moeten vragen. Naar wat ik begreep zijn de applicaties wel belangrijk, maar niet bedrijfs kritisch. In ieder geval is 1 keer per dag een Transaction Log backup dus vrij weinig.

Ik doe sinds vandaag eigenlijk kennis op van Databases. Je moet altijd ergens beginnen, en ik kan hier helaas niet zomaar bij iemand terecht die al wel genoeg SQL kennis heeft.

Ga het artikel over DB Autoshrink nu even lezen en uitzetten.

[Jaymz]

Doe wel wat met indexmaintenance, defragmentatie gaat je performance kosten

[Meekoh]

technoaddict schreef op vrijdag 20 februari 2015 @ 11:55:
Hm 1e artikel dat ik zie over Reorganizen van de index:

Are you sure you aren't talking about a rebuild? An index REBUILD is slow, expensive, consumes a humongous amount of log.

http://dba.stackexchange....l-during-index-reorganize

Dat verschilt. Een Reorganize is altijd in ieder recovery model fully logged.
Rebuilds (offline) kunnen echter minimally logged zijn wanneer het recovery model op bulk logged of simple staat.
Simple zou ik niet doen. Maar je zou tijdens Index maintenance kunnen switchen naar Bulk Logged.

edit:
Een reorganize op een zwaar gefragmenteerde index gaat dus heul veel logging opleveren.
Daarom wordt vaak gebruik gemaakt van dat script van Ola Hallengren(zoals Jaymz ook al aangaf), die bepaalt op basis van fragmentatie of het een rebuild of reorganize moet worden.

[ Voor 16% gewijzigd door Meekoh op 20-02-2015 15:11 ]

[ElCondor]

SQL beheer en vooral optimalisatie is een eigen tak van sport en maar weinig mensen hebben er kaas van gegegeten. Mijzelf incluis. Ik heb vandaag in ieder geval ook weer het een en ander geleerd.

[technoaddict]

Goed bezig

Nog 1 vraag. Het feit dat de Transactie log backup 1 keer per dag gedaan wordt 12 uur smiddags, wat houdt dat in voor de recovery in geval van calamiteiten? Er wordt dus ook 1 Full backup gedaan 9 uur savonds.

[Meekoh]

technoaddict schreef op vrijdag 20 februari 2015 @ 15:34:
Goed bezig

Nog 1 vraag. Het feit dat de Transactie log backup 1 keer per dag gedaan wordt 12 uur smiddags, wat houdt dat in voor de recovery in geval van calamiteiten? Er wordt dus ook 1 Full backup gedaan 9 uur savonds.

Dat als je server crasht om 20:45 je data kunt restoren tot 12:00 die middag. Alles wat gewijzigd is tussen 12:00 en 20:45 ben je kwijt.
En als hij crasht om 11:45 je data kunt restoren van 21:00 de avond ervoor.

[ Voor 5% gewijzigd door Meekoh op 20-02-2015 16:20 ]

[technoaddict]

Thanks!

[Jaymz]

Chrashed wil zeggen: data kwijt

Als je database en logfile óf full Back-up en logfile (of logbackup's) nog hebt verlies je minder en kun je in theorie terug na vlak voor de chrash, maar dat zijn scenario's die je geoefend moet hebben; die zijn wat complex

Edit:
Eigenlijk moet je altijd een scenario geoefend hebben: eventuele logins staan in master, en master recoveren komt nog wel wat bij kijken, en die verassingenen wil je niet in het eggie (stress) erbij hebben

[ Voor 29% gewijzigd door Jaymz op 20-02-2015 18:29 ]