Narcis Miclaus

7 min de citit

SQL Server, de la zero Lecția 30 / 40

Filegroups, fișiere de date și ce face de fapt fișierul de log

MDF, NDF, LDF, dezastre de autogrowth, VLF-uri și de ce FULL recovery fără log backups e o bombă cu ceas. Layout-ul de stocare pe care fiecare DBA ar trebui să-l știe.

Fiecare bază de date SQL Server are cel puțin două fișiere pe disc: un fișier de date (.mdf) și un fișier de log (.ldf). Cei mai mulți oameni trec ani buni fără să se gândească la ele. Apoi, într-o zi, discul se umple, baza de date iese offline și cineva trebuie să-i explice CEO-ului de ce site-ul Runehold returnează 500-uri.

Astăzi demistificăm fișierele. Ce sunt, cum cresc, de ce fișierul de log e ceva separat de date și acea unică setare pe care fiecare DBA ajunge până la urmă să-și dorească s-o fi configurat mai devreme: autogrowth.

Fișierele

O bază de date proaspătă are:

  • Fișierul primar de date, .mdf — containerul principal de date. Unul per bază de date.
  • Fișierul de transaction log, .ldf — înregistrează fiecare modificare; esențial pentru recovery.
  • Opțional, fișiere secundare de date, .ndf — adăugate ca să distribui datele pe drive-uri sau filegroups.
-- Vezi fișierele unei baze de date
SELECT
    name,
    physical_name,
    type_desc,
    size * 8 / 1024   AS size_mb,
    growth,
    is_percent_growth,
    max_size
FROM sys.master_files
WHERE database_id = DB_ID('Runehold');

size e în pagini de 8 KB, de aici * 8 / 1024 ca să convertești în MB. Fiecare bază de date de sistem (master, tempdb, model, msdb) și fiecare bază de date user au intrări aici.

Filegroups

Un filegroup e un container denumit cu unul sau mai multe fișiere de date. By default, fiecare bază de date are un filegroup numit PRIMARY. Poți crea altele:

ALTER DATABASE Runehold ADD FILEGROUP FG_Archive;

ALTER DATABASE Runehold ADD FILE (
    NAME = 'Runehold_Archive',
    FILENAME = 'D:\SQLData\Runehold_Archive.ndf',
    SIZE = 1024MB
) TO FILEGROUP FG_Archive;

Utilizări ale filegroups:

  • Separi datele hot și cold. Tabelele de arhivă merg în FG_Archive pe storage mai ieftin.
  • Piecemeal restore. Restaurezi întâi filegroups critice, aduci baza de date online, restaurezi arhiva mai târziu.
  • Plasarea partițiilor. Pattern-ul sliding-window din lecția 29 pune fiecare partiție pe un filegroup diferit.

Specifici un filegroup la momentul creării tabelului:

CREATE TABLE Archive.OrdersOld (
    OrderId BIGINT PRIMARY KEY,
    ...
) ON FG_Archive;

Fără clauza ON, tabelul merge în filegroup-ul default (PRIMARY dacă nu schimbi). Schimbă default-ul:

ALTER DATABASE Runehold MODIFY FILEGROUP FG_Archive DEFAULT;

Fișierul de log: ce face cu adevărat

Fișierul de log înregistrează fiecare modificare făcută bazei de date. E write-ahead log-ul: înainte ca orice pagină de date să fie scrisă, modificarea e logată întâi. De aceea funcționează durabilitatea (D-ul din ACID).

Conținutul logului:

  • Fiecare INSERT, UPDATE, DELETE.
  • Fiecare DDL (CREATE, ALTER, DROP).
  • Începuturi și commit-uri de tranzacție.
  • Checkpoint-uri (markeri care spun „până în acest punct, toate datele commited sunt pe disc”).

Logul e circular: SQL Server scrie la sfârșit, apoi se întoarce ca să reutilizeze începutul când e sigur. Fișierul de log poate să crească, dar niciodată nu se micșorează automat.

Ce înseamnă „sigur de reutilizat” depinde de recovery model.

Recovery models

Trei opțiuni:

  • SIMPLE — logul e reutilizabil imediat ce tranzacția e commited. Fără point-in-time recovery. Logul rămâne mic automat. Excelent pentru dev/test, periculos pentru prod.
  • FULL — logul e reutilizabil doar după un transaction log backup. Point-in-time recovery posibil. Logul crește până se face un log backup.
  • BULK_LOGGED — la mijloc; logging minimal pentru import-uri bulk, logul tot are nevoie de backups. De nișă.
-- Verifică cel curent
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'Runehold';

-- Schimbă-l
ALTER DATABASE Runehold SET RECOVERY FULL;

Capcana: FULL recovery fără log backups. Logul crește până umple discul. Baza de date devine read-only. Totul se oprește.

Regulă: dacă ești pe FULL recovery, trebuie să faci backup la log regulat (la fiecare 15 minute e un punct comun de plecare). Dacă nu poți face asta, treci pe SIMPLE. FULL fără log backups e ce e mai rău din ambele lumi.

Vom acoperi strategiile de backup cum trebuie în lecția 31.

Virtual Log Files (VLF-uri)

În interiorul fișierului de log, SQL Server împarte lucrurile în „chunks” numite Virtual Log Files. Fiecare eveniment de autogrowth creează VLF-uri noi proporțional cu mărimea creșterii.

Prea multe autogrowth-uri mici = prea multe VLF-uri mici. Asta încetinește recovery-ul (SQL Server trebuie să parcurgă fiecare VLF la pornire), încetinește log backups și uneori cauzează probleme misterioase de performanță.

Regulă generală: țintește în jur de 50-500 de VLF-uri în total. Numără cu:

DBCC LOGINFO;
-- Returnează un rând per VLF. Numără rândurile.

Dacă vezi 10.000+ VLF-uri, ai avut multe evenimente mici de autogrowth. Repară prin:

  1. Shrink la log până la minim (necesită întâi un log backup dacă ești pe FULL).
  2. Crește-l înapoi într-un singur chunk mare la dimensiunea de lucru.
BACKUP LOG Runehold TO DISK = 'NUL';         -- dacă FULL, ai nevoie de log backup ca să eliberezi spațiu
DBCC SHRINKFILE ('Runehold_log', 100);        -- shrink la 100 MB
ALTER DATABASE Runehold MODIFY FILE
    (NAME = 'Runehold_log', SIZE = 8192MB);   -- pre-grow la 8 GB dintr-o lovitură

Un fișier curat de 8 GB creează cam 16 VLF-uri (euristica de dimensionare s-a schimbat de-a lungul versiunilor). Un fișier de log care a crescut cu 10MB de 800 de ori are 6.400 de VLF-uri mici. Viața e mai bună cu prima variantă.

Autogrowth: dezastrul care așteaptă să se întâmple

Fiecare bază de date SQL Server are setări de autogrowth: cât de mult să crească când fișierul se umple. Default-uri:

  • Fișiere de date: 64 MB
  • Fișiere de log: 64 MB (era 10% pe versiunile mai vechi, oribil)

Probleme cu default-urile:

  • Pe un sistem ocupat, creșterea cu 64 MB înseamnă creștere la fiecare câteva minute. Fiecare eveniment de creștere blochează scrierile pentru scurt timp.
  • Multe evenimente mici de creștere produc mizeria VLF de mai sus.
  • Creșterea „percent” pe un fișier de 500 GB înseamnă un eveniment de creștere de 50 GB. Serverul se oprește, discul se umple brusc, alertele țipă.

Recomandare: autogrowth explicit cu mărime fixă, dimensionat pentru workload-ul tău. Baza de date prod a Runehold:

  • Fișier de date: începe la 50 GB, autogrow cu 2 GB.
  • Fișier de log: începe la 8 GB, autogrow cu 1 GB.
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold_data',
    FILEGROWTH = 2GB
);

De asemenea: pre-dimensionează fișierele la working set-ul așteptat la momentul creării. Nu începe la 8 MB și să crească. Spune-i SQL Server-ului la CREATE DATABASE „voi avea nevoie de 50 GB, rezervă-i acum.” Mai puține evenimente de creștere, VLF count mai bun, mai puțină fragmentare pe discul subiacent.

Instant file initialization

Când SQL Server crește un fișier de date, trebuie să scrie zerouri în spațiul nou. La o creștere de 50 GB, înseamnă 50 GB de zero-writes — secunde până la minute.

Instant file initialization (IFI) sare peste acel zero-write. Documentația Microsoft recomandă activarea lui. Necesită acordarea contului serviciului SQL Server a privilegiului Windows Perform Volume Maintenance Tasks. Pe instalări Linux / container, IFI e on by default.

Cu IFI, o creștere de 50 GB a fișierului de date durează ~1 secundă în loc de ~30. Imens mai bine.

Notă: IFI ajută doar fișierele de date. Fișierul de log trebuie mereu zeroizat pentru corectitudine (citirile din log depind de a ști unde se termină datele valide).

Ce să monitorizezi

Listă scurtă de query-uri și praguri pentru care merită să setezi alerte:

-- Utilizare disc per bază de date
SELECT name, type_desc, size * 8 / 1024 AS size_mb,
       FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mb,
       size * 8 / 1024 - FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS free_mb
FROM sys.database_files;

-- Procent liber în log
DBCC SQLPERF(LOGSPACE);
-- Returnează rânduri care arată Log Space Used (%) per bază de date.

-- VLF count pentru baza de date curentă
DBCC LOGINFO;   -- numără rândurile

-- Evenimente de autogrowth în ultimele 7 zile
SELECT database_name, file_logical_name,
       (size_change_kb / 1024) AS size_change_mb,
       start_time
FROM msdb.dbo.backupset a -- placeholder; DMV efectiv: ring_buffer / sesiune XE default
WHERE 1 = 0;

(Pentru a găsi evenimente de autogrowth, default trace sau o sesiune extended event e sursa corectă. Raportul Disk Usage din SQL Server Management Studio e o vedere GUI ușoară.)

Setează alerte când:

  • Utilizare disc > 85%.
  • Log space used > 75%.
  • Se întâmplă un eveniment de autogrowth pe prod.
  • VLF count > 1.000.

Rulează asta pe propria mașină

USE Runehold;
GO

-- 1. Vezi fișierele și mărimile lor
SELECT name, type_desc, size * 8 / 1024 AS size_mb,
       FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mb,
       growth, is_percent_growth
FROM sys.database_files;

-- 2. Spațiu de log și VLF count
DBCC SQLPERF(LOGSPACE);
DBCC LOGINFO;

-- 3. Setează autogrowth sensibil
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold',
    FILEGROWTH = 512MB
);
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold_log',
    FILEGROWTH = 256MB
);

-- 4. Fă un filegroup secundar + fișier (doar demo, ajustează căile)
-- ALTER DATABASE Runehold ADD FILEGROUP FG_Archive;
-- ALTER DATABASE Runehold ADD FILE (
--     NAME = 'Runehold_Archive',
--     FILENAME = 'C:\Data\Runehold_Archive.ndf',
--     SIZE = 256MB, FILEGROWTH = 64MB
-- ) TO FILEGROUP FG_Archive;

-- 5. Verifică recovery model și statusul backup-ului
SELECT d.name, d.recovery_model_desc,
       DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS hours_since_last_full_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
       ON bs.database_name = d.name AND bs.type = 'D'
WHERE d.name NOT IN ('tempdb', 'model')
GROUP BY d.name, d.recovery_model_desc;

O bază de date pe FULL recovery fără log backups recente e momentul „ah, nu” numărul unu peste care dă un DBA nou. Query-ul de mai sus o scoate la suprafață imediat.

Modulul 6 e gata. Partiționare (lecția 29), filegroups și fișierul de log. Acum subiectele operaționale mari: backup-uri, Agent, securitate, tempdb, mentenanță și DMV-uri. Asta e Modulul 7.

Caută