Narcis Miclaus

7 min di lettura

SQL Server, dalle fondamenta Lezione 30 / 40

Filegroups, file di dati, e cosa sta facendo davvero il log file

MDF, NDF, LDF, disastri di autogrowth, VLF, e perché il recovery model FULL senza backup del log è una bomba a orologeria. La struttura di storage che ogni DBA dovrebbe conoscere.

Ogni database SQL Server ha almeno due file su disco: un file di dati (.mdf) e un log file (.ldf). La maggior parte delle persone passa anni senza pensarci. Poi un giorno il disco si riempie, il database va offline, e qualcuno deve spiegare al CEO perché il sito di Runehold sta restituendo 500.

Oggi demistifichiamo i file. Cosa sono, come crescono, perché il log file è una cosa separata dai dati, e l’unica impostazione che ogni DBA prima o poi vorrebbe aver configurato prima: l’autogrowth.

I file

Un database fresco ha:

  • File di dati primario, .mdf — il container principale dei dati. Uno per database.
  • Transaction log file, .ldf — registra ogni cambiamento; essenziale per il ripristino.
  • Opzionalmente, file di dati secondari, .ndf — aggiunti per distribuire i dati su più dischi o filegroups.
-- Vedi i file per un database
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 è in pagine da 8 KB, da cui il * 8 / 1024 per convertire in MB. Ogni database di sistema (master, tempdb, model, msdb) e ogni database utente ha voci qui.

Filegroups

Un filegroup è un contenitore con nome di uno o più file di dati. Di default ogni database ha un filegroup chiamato PRIMARY. Puoi crearne altri:

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;

Usi dei filegroups:

  • Separare dati caldi e freddi. Le tabelle di archivio vanno in FG_Archive su storage più economico.
  • Restore a pezzi. Ripristina prima i filegroups critici, porta il database online, ripristina l’archivio dopo.
  • Posizionamento delle partizioni. Il pattern sliding-window della lezione 29 mette ogni partizione su un filegroup diverso.

Specifichi un filegroup al momento della creazione della tabella:

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

Senza la clausola ON, la tabella va nel filegroup di default (PRIMARY a meno che tu non lo cambi). Cambia il default:

ALTER DATABASE Runehold MODIFY FILEGROUP FG_Archive DEFAULT;

Il log file: cosa sta facendo davvero

Il log file registra ogni cambiamento fatto al database. È il write-ahead log: prima che qualsiasi pagina di dati venga scritta, il cambiamento viene loggato per primo. Per questo la durabilità (la D in ACID) funziona.

Contenuto del log:

  • Ogni INSERT, UPDATE, DELETE.
  • Ogni DDL (CREATE, ALTER, DROP).
  • Inizi e commit di transazione.
  • Checkpoint (marker che dicono “fino a questo punto, tutti i dati committati sono su disco”).

Il log è circolare: SQL Server scrive alla fine, poi torna all’inizio per riusare lo spazio quando è sicuro. Il log file può crescere ma non si restringe mai automaticamente.

Cosa significa “sicuro da riusare” dipende dal recovery model.

Recovery model

Tre opzioni:

  • SIMPLE — il log è riusabile non appena la transazione fa commit. Niente point-in-time recovery. Il log resta piccolo automaticamente. Ottimo per dev/test, pericoloso in produzione.
  • FULL — il log è riusabile solo dopo un backup del transaction log. Point-in-time recovery possibile. Il log cresce finché non avviene un backup del log.
  • BULK_LOGGED — via di mezzo; logging minimo per import bulk, il log ha comunque bisogno di backup. Di nicchia.
-- Verifica quello attuale
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'Runehold';

-- Cambialo
ALTER DATABASE Runehold SET RECOVERY FULL;

La trappola: recovery FULL senza backup del log. Il log cresce finché riempie il disco. Il database va in read-only. Tutto si ferma.

Regola: se sei in recovery FULL, devi fare backup del log con regolarità (ogni 15 minuti è un punto di partenza comune). Se non riesci, passa a SIMPLE. FULL senza backup del log è il peggio dei due mondi.

Copriremo le strategie di backup come si deve nella lezione 31.

Virtual Log File (VLF)

Dentro il log file, SQL Server divide le cose in “chunk” chiamati Virtual Log File. Ogni evento di autogrowth crea nuovi VLF proporzionali alla dimensione della crescita.

Troppi piccoli autogrowth = troppi piccoli VLF. Questo rallenta il recovery (SQL Server deve attraversare ogni VLF all’avvio), rallenta i backup del log, e occasionalmente causa misteriosi problemi di performance.

Regola pratica: mira a circa 50-500 VLF totali. Conta con:

DBCC LOGINFO;
-- Restituisce una riga per VLF. Conta le righe.

Se vedi 10.000+ VLF, hai avuto molti eventi di autogrowth minuscoli. Sistema così:

  1. Riduci il log al minimo (richiede prima un backup del log se sei in FULL).
  2. Falo ricrescere in un solo grande blocco fino alla sua dimensione di lavoro.
BACKUP LOG Runehold TO DISK = 'NUL';         -- se FULL, serve un backup del log per liberare spazio
DBCC SHRINKFILE ('Runehold_log', 100);        -- riduci a 100 MB
ALTER DATABASE Runehold MODIFY FILE
    (NAME = 'Runehold_log', SIZE = 8192MB);   -- pre-cresci a 8 GB in un colpo solo

Un file pulito da 8 GB crea circa 16 VLF (l’euristica di sizing è cambiata tra le versioni). Un log file cresciuto di 10MB 800 volte ha 6.400 VLF minuscoli. La vita è migliore con il primo.

Autogrowth: il disastro in attesa

Ogni database SQL Server ha impostazioni di autogrowth: di quanto crescere quando il file si riempie. Default:

  • File di dati: 64 MB
  • File di log: 64 MB (era il 10% nelle versioni più vecchie, terribile)

Problemi con i default:

  • Su un sistema occupato, crescere di 64 MB significa crescere ogni pochi minuti. Ogni evento di crescita blocca brevemente le scritture.
  • Molti piccoli eventi di crescita producono il casino dei VLF di cui sopra.
  • La crescita “percentuale” su un file da 500 GB significa un evento di crescita da 50 GB. Il server si ferma, il disco si riempie all’improvviso, scattano gli alert.

Raccomandazione: autogrowth a dimensione fissa esplicita, dimensionato per il tuo workload. Database di produzione di Runehold:

  • File di dati: parti da 50 GB, autogrow di 2 GB.
  • File di log: parti da 8 GB, autogrow di 1 GB.
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold_data',
    FILEGROWTH = 2GB
);

Inoltre: pre-dimensiona i file alla loro working set attesa al momento della creazione. Non partire da 8 MB e crescere. Di’ a SQL Server al momento del CREATE DATABASE “mi serviranno 50 GB, riservali ora.” Meno eventi di crescita, miglior conteggio dei VLF, meno frammentazione sul disco sottostante.

Instant file initialization

Quando SQL Server fa crescere un file di dati, deve scrivere zeri sul nuovo spazio. Su una crescita da 50 GB, sono 50 GB di scritture di zeri — secondi o minuti.

L’instant file initialization (IFI) salta quella scrittura di zeri. La documentazione Microsoft raccomanda di abilitarla. Richiede di concedere all’account del servizio SQL Server il privilegio Windows Perform Volume Maintenance Tasks. Sulle installazioni Linux / container, IFI è attivo di default.

Con IFI, una crescita da 50 GB di un file di dati impiega ~1 secondo invece di ~30. Enormemente migliore.

Nota: IFI aiuta solo i file di dati. Il log file deve sempre essere azzerato per correttezza (le letture del log dipendono dal sapere dove finiscono i dati validi).

Cosa monitorare

Lista breve di query e soglie su cui vale la pena fare alert:

-- Uso del disco per database
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;

-- Percentuale libera del log file
DBCC SQLPERF(LOGSPACE);
-- Restituisce righe che mostrano Log Space Used (%) per database.

-- Conteggio VLF per il database corrente
DBCC LOGINFO;   -- conta le righe

-- Eventi di autogrowth negli ultimi 7 giorni
SELECT database_name, file_logical_name,
       (size_change_kb / 1024) AS size_change_mb,
       start_time
FROM msdb.dbo.backupset a -- placeholder; vera DMV: ring_buffer / sessione XE di default
WHERE 1 = 0;

(Per trovare gli eventi di autogrowth, il default trace o una sessione di extended events sono la fonte giusta. Il report Disk Usage di SQL Server Management Studio è una vista GUI facile.)

Fai alert quando:

  • Uso del disco > 85%.
  • Log space used > 75%.
  • Avviene un evento di autogrowth in produzione.
  • Conteggio VLF > 1.000.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. Vedi i file e le loro dimensioni
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. Spazio del log e conteggio VLF
DBCC SQLPERF(LOGSPACE);
DBCC LOGINFO;

-- 3. Imposta un autogrowth sensato
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold',
    FILEGROWTH = 512MB
);
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold_log',
    FILEGROWTH = 256MB
);

-- 4. Crea un filegroup secondario + file (solo demo, adatta i path)
-- 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. Verifica recovery model e stato dei backup
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;

Un database in recovery FULL senza backup del log recenti è il momento “oh no” numero uno in cui un nuovo DBA si imbatte. La query qui sopra lo fa emergere subito.

Modulo 6 fatto. Partitioning (lezione 29), filegroups, e log file. Adesso i grandi temi operativi: backup, Agent, sicurezza, tempdb, manutenzione e DMV. Quello è il Modulo 7.

Cerca