Liberare spazio su Microsoft SQL Server 2000

Nota: questo è un articolo di qualche tempo fa, ma visto che ci sono in giro ancora parecchi Microsoft SQL Server 2000 lo postiamo lo stesso sperando possa essere ancora utile a qualcuno.

A chi non conosce bene come funzionano i log delle transazioni in Microsoft SQL Server 2000 può succedere di avere inaspettati problemi di spazio dopo un periodo variabile di utilizzo del database. Vediamo di capire quali sono i motivi e quali le soluzioni, sia a lungo termine che immediate.

Premessa: i log delle transazioni

Allora, quando si crea un nuovo database, il modello di recovery di default è “FULL”: questo significa che per quel database verrà usato un log delle transazioni. Senza andare troppo nel dettaglio, diciamo che il log delle transazioni è un archivio che contiene tutte le operazioni fatte sul database. Ecco perchè vi troverete un file NomeDatabase.MDF e un altro file NomeDatabase_log.LDF.

L’idea è che uno possa salvare una volta ogni tanto (ad esempio la notte) il database completo, e invece salvi più frequentemente (ad esempio ogni ora) le sole transazioni compiute a partire dall’ultimo backup completo. Questi salvataggi saranno più veloci, quindi non andranno a interferire troppo sulle prestazioni. Allo stesso tempo però ci garantiscono di poter recuperare i dati fino a un massimo di un’ora prima.

Quando le transazioni vengono salvate, nell’ambito di un normale programma di manutenzione del database, vengono anche cancellate dal file di log che quindi poi può essere “ristretto” per recuperare lo spazio su disco. Il problema nasce però dal fatto che il programma di backup / manutenzione non è attivo di default. E’ chiaro che un buon amministratore di sistema dovrebbe saperlo fare, tuttavia ho incontrato diverse situazioni in cui questa faccenda dei log non era per niente chiara, e sul sistema era attivo solo il backup periodico del database.

In questi casi, il file di log delle transazioni continua ad aumentare,  e prima o poi finisce per riempire anche i dischi più capienti.

Soluzioni

Per risolvere definitivamente la situazione ci sono due scelte:

– attivare il backup e la manutenzione del file di log;

– impostare il metodo di recovery (nelle proprietà del database) a “SIMPLE”. In tal modo le transazioni verranno cancellate non appena applicate al database e il file di log non crescerà mai.

La scelta tra le due dipende ovviamente dalla criticità dei dati nel db e se possiamo permetterci in caso di problemi di perdere i dati fino alla notte precedente o se vogliamo un intervallo di tempo minore.

Soluzioni più immediate?

Okay, supponiamo che il disco sia pieno adesso, e che dobbiate risolvere la cosa prima che tutto il sistema si fermi – se non lo è già. Supponendo che abbiate già individuato qual è il file di log che si è gonfiato a dismisura, aprite Query Analyzer, selezionate il database che ha problemi e lanciate il comando

BACKUP LOG NomeDatabase WITH TRUNCATE_ONLY

Questo lancia un backup del log delle transazioni ma senza di fatto salvare i dati, limitandosi invece a cancellare le transazioni esistenti (ovviamente a patto che queste siano già state applicate al db, ma di solito è così). Proseguite poi con

DBCC SHRINKDATABASE ( 'NomeDatabase' )

Questo comprime tutti i file del db recuperando lo spazio libero. Se volete lavorare solo sul file di log anzichè su tutti i file del db, usate il comando

DBCC SHRINKFILE ( 'NomeFileLogico' )

Fate attenzione a:

• usare il nome logico del file di log: lo trovate nelle proprietà del db
• inserire tra apici (‘) i nomi del db o del file

Risolto il problema? Bene. A questo punto è ora di dare un’occhiata alla configurazione di tutti gli altri database per evitare che la cosa si ripeta. 🙂