Gigantische SQL Log Bestanden (en AlwaysOn)
Heb jij een SQL server die in een AlwaysOn configuratie staat?
Heb jij de melding ontvangen dat de LOG schijf vol loopt?
OF
Heb jij geconstateerd dat de LOG bestanden gigantisch zijn in verhouding met de DATA file?
En heb jij een LOG shrink uitgevoerd maar hij wordt niet kleiner?
Dan zou ik vooral verder lezen ;)
Als er geen goede back-up inrichting is die de LOG bestanden klein houd kan de LOG zijn inhoud niet kwijt, hierdoor zal die blijven groeien. Ook als er een goede back-up inrichting is kan het voor komen dat de LOG bestanden blijven groeien (wel een stuk langzamer). Bij een stand-alone SQL server kan je er voor kiezen om de database van FULL om te zetten naar SIMPLE (vergeet hem niet terug te zetten naar FULL als je point in time restores nodig hebt), maar dat gaat bij een AlwaysOn omgeving niet zo makkelijk. Hieronder zal ik beschrijven wat de stappen zijn om je LOG bestand weer kleiner te maken:
1- Als eerste gaan wij het formaat van de LOG naast die van de DATA file houden.
a. Open SSMS en vraag de properties op van de database in kwestie, navigeer naar Files en kijk naar het tabje “Inital Size (MB)”.
2- Nu we weten dat de LOG file vele malen groter is dan de DATA file gaan we kijken of er ook iets in staat (Misschien is die leeg door de backup, maar moet die een shrink krijgen)
a. Rechter muis klik op de database in kwestie -> Tasks -> Shrink -> Files.
b. Selecteer bij “File type” Log en kijk naar de regels “Currently allocated space” & “Available free space”
3- Zo te zien is de LOG nog helemaal vol en kunnen wij deze niet zomaar shrinken, hiervoor gaan we een losse LOG backup uitvoeren.
a. Rechter muis klik op de database in kwestie -> Tasks -> Backup.
b. Selecteer bij “Backup type” Transaction Log, kies een locatie en naam en druk op “OK”. (Aan de hand van stap 2b kan je zien hoeveel ruimte je nodig hebt voor de backup)
4- De backup draait nu, en afhankelijk van de grote kan dit even duren, wil je graag inzien hoe ver de backup is, tot op de percentage nauwkeurig en de mogelijke eind tijd?
a. Rechter muis klik op de database in kwestie -> New Query.
USE master
SELECT
session_id as SPID,
CONVERT(VARCHAR(50),start_time,100) AS start_time,
percent_complete,
CONVERT(VARCHAR(50),dateadd(second,estimated_completion_time/1000, getdate()),100) as estimated_completion_time,
command, a.text AS Query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%' OR r.command LIKE 'RESTORE%'
b. Druk daarna in de menu balk op “Execute”.
c. Resultaat zal er uit zien als onderstaand:
Deze query pakt alle backups actief op de server, controleer dan ook even bij Query om welke database het gaat. Door op “Execute” te drukken zal je de query opnieuw draaien en de status op zien lopen (Het is niet automatisch!)
5- Wanneer je op “Execute” drukt en de regel van 4d is weg, dan is de backup afgerond en kunnen wij de shrink uitvoeren.
a. Rechter muis klik op de database in kwestie -> Tasks -> Shrink -> Files.
b. Selecteer bij “File type” Log en kijk naar de regel “Available free space”
c. Je zal zien dat hier nu 99% vrij is, kies de opties aan de hand van de onderstaande screenshot en druk op “OK”.
Kiezen voor “Release unused space” wil niet altijd werken na een LOG backup, probeer deze nog na 5c wanneer je niet genoeg ruimte hebt. Als het scherm gelijk weg is nadat je op “OK” drukt heeft het waarschijnlijk niet gewerkt, verhoog het formaat dan.
6- Controleer of de shrink succesvol is, dit kan je doen door naar het LOG bestand op de schijf te gaan en te kijken hoe groot die is , of stap 2b herhalen en kijken naar “Currently allocated space”.
a. Het komt vaak voor dat een LOG bestand niet wil shrinken, er kunnen een hoop oorzaken voor zijn, gelukkig houdt SQL dit (beknopt) bij.
b. Rechter muis klik op de database in kwestie -> New Query.
select log_reuse_wait_desc from sys.databases where name = 'Database_Name_Here'
c. De output in mijn geval was als volgt:
7- We hebben net een LOG backup gedaan, je zou dus denken dat het niet de blokkerende factor mag zijn.
a. In dit geval voeren wij nog een LOG backup uit, herhaal de stappen bij 3a en 3b, (Kies nu wel voor “Release unused space”) Deze gaat een stuk sneller aangezien het LOG bestand leeg is.
b. Trap daarna je query opnieuw af, dan zou er de output “NOTHING” moeten zijn. Nu kan je weer een shrink actie uitvoeren.
Wil de shrink na de bovenstaande punten niet werken, herhaal dan de stappen 3a, 3b, 5a, 5b en 5c.
Wanneer je de LOG bestanden weer op orde hebt zal je naar de backup inrichting voor de LOG bestanden moeten kijken. Er is niks mis met vaker backuppen van de LOG bestanden! Verhoog het interval van je LOG backups en geniet van alle vrije ruimte op je schijven :)
