siquellando

Un log che non cresce anche in FULL logging

Posto una particolarita' del comportamento del file di log la cui utilita' pratica rasenta lo zero, ma puo' essere utilizzata per vincere un caffe' facendo una scommessa

Domanda: e' possibile che un database in cui vengono effettuate numerose operazioni di scrittura, settato in modalita'di recovery FULL e per cui non venga mai effettuato un backup del log  non presenti un aumento delle dimensioni del suo file di log  (o per lo meno esso non tenda ad "esplodere") ?

Risposta: (sorprendente):  SI.

Capita in una condizione piuttosto particolare: se non e' mai stato effettuato un backup completo dei dati da quando il database e' in recovery model FULL.

Questo e' dovuto al fatto che, in realta',  la condizione che il log "non cresca", ovvero che recuperi via via lo spazio utilizzato dalle transazioni committate ed effettivamente scritte nel database (truncation del log) non e' legata "esattamente" al modello di recovery , ma piu' precisamente al fatto che SQL Server abbia dedotto che, per quel database, non viene mantenuta una sequenza di backup del log.

In altre parole, se SQL capisce che non abbiamo intenzione di utilizzare il log nella nostra strategia di backup-restore, correttamente non si preoccupa (se ne frega...)  di salvare le transazioni gia' scritte nel database (dato che non ci servono per il restore) e recupera appena puo', con un bel truncate, lo spazio a loro allocato.

Ok, e quando SQL puo' essere sicuro che non ci interessa il restore dal log ??? Nei seguenti tre casi:

• Lo richiediamo espressamente scegliendo il modello di recovery SIMPLE
• Buttiamo via i dati del log con l'istruzione backup log with no_log ( opzione deprecata che non sara' piu' supportata nelle prossime versioni)

e, dulcis in fundo...

• Non abbiamo MAI fatto un backup full dei dati.
  (se non abbiamo il back up di base come facciamo ad applicargli il restore del log !!??!!) 

Eh, si, c'e' poco da fare SqlServer e' ... furbo !!

PS: vi interessa sapere (in Sql2005) quali database sono in questa situazione (ovvero in autotruncate mode) ?  Lo vedete da questa select:

SELECT db_name(database_id)

FROM sys.database_recovery_status

WHERE last_log_backup_lsn is NULL

Script per recuperare le dimensioni dei log

Ho modificato uno script pubblicato dalla Delaney per farmi restituire, per tutti i database, le dimensioni dei dati, del file di log, la sua % di occupazione ed il modello di recovery attivo.

Lo script, valido per Sql2005, e' veramente semplice, ma potrebbe comunque essere utile per cui eccolo.

 /* restituisce per tutti i database le dimensioni dei dati, del log la % di occupazione del log ed il modello di recovery */

select rtrim (pc1.instance_name) as [Database Name],

pc3.cntr_value/1024.0 as [Data Size (MB)],

pc1.cntr_value/1024.0 as [Log Size (MB)],

cast (pc2.cntr_value*100.0/pc1.cntr_value as dec (5,2)) as [Log Space Used (%)],

from sys.dm_os_performance_counters as pc1,

sys.dm_os_performance_counters as pc2,

sys.dm_os_performance_counters as pc3,

and pc1.instance_name = pc3.instance_name

and pc1.counter_name = 'Log file(s) Size (KB)'

and pc2.counter_name = 'Log file(s) Used Size (KB)'

and pc3.counter_name = 'Data File(s) Size (KB)'

order by pc1.instance_name;

Il recovery model SIMPLE

In merito al recovery model SIMPLE spesso girano dubbi e spesso capita di sentirsi chiedere cosa logga e se le sue dimensioni crescono  e perche' crescono  e quando si "sgonfia" etc etc. Sperando di fare cosa utile in questo post provero' a fare un po' di chiarezza in merito...

Innazitutto chiariamo che una modalita' di logging SIMPLE non significa che non loggo ... Tutt'altro: SIMPLE significa solo che il transaction log non potra' essere utilizzato in fase di restore (infatti in questa modalita' non e' concesso il backup del log), ma che comunque viene assicurata tutta l'attivita' relativa alla gestione delle transazioni ... e ci mancherebbe che non lo facesse !!!! 

Pertanto, anche in questa modalita', il transaction log conterra' tutte le informazioni necessarie a Sql per poter gestire un eventuale roll back. Questo comporta che il file di log avra' una dimensione tale da memorizzare tutte le transazioni non ancora committate o, piu' precisamente, conterra' "almeno" tutte le transazioni avvenute a partire dalla piu' vecchia presente ancora non committata.

Per questa ragione, in presenza di transazioni ricche di dati, anche in modalita' SIMPLE il file di log crescera' e potrebbe assumere dimensioni anche elevate.

La differenza con le altre due modalita di recovery (FULL e BULK_INSERT) risiede nel fatto che, man mano che le transazioni vengono committate lo spazio a loro associato viene riutilizzato per memorizzare nuove transazioni.[per dirla piu' correttamente viene riutilizzato lo spazio associato alle transazioni antecedenti alla piu' vecchia non committata]. Questo in FULL e BULK_INSERT avviene dopo il backup del log.

Nella modalita' SIMPLE periodicamente e con una frequenza legata alle dimensioni del file di log e al suo grado di riempimento, viene verificata la posizione della transazione attiva piu' vecchia e viene marcato come riutilizzabile lo spazio del log allocato a tutte le transazioni antecedenti a questa. Tale attivita' prende il nome di Troncamento (truncation) del log.

Il Log truncation e' un attivita' di tipo logico: ad essa non e' associata una riduzione fisica del log.

La diminuzione del file di log e' legata all'attivita' di Shrink che, se non e' abilitata tramite l'opzione AUTO_SHRINK ON (cosa da non fare MAI ), avverra' solo dietro un preciso comando (ad esempio un DBCC SHRINKDATABASE).

Detto cio' bisogna ricordare che il recovery model SIMPLE, visto che non consente il recovery tramite log, andrebbe usato raramente: di fatto solo con DB in read only o di test... Averlo in produzione significa sapere che, in caso di failure, verranno perse tutte le transazioni intercorse dall'ultimo backup (full o differenziale) cosa che ritengo sia accettabile in ben poche situazioni....

Di seguito, per i curiosi, un po' di codice "pronto all'uso" per vedere la crescita del file di log in modalita' SIMPLE.

Ciao.

-- Test sul recovery model Simple

USE [master]

GO

-- Creazione db e tabelle di test

-- Il file di log e' VOLUTAMENTE inizializzato piccolo con bassa crescita

-- per valutarne l'aumento di dimensioni

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestRecoverySimple')

DROP DATABASE TestRecoverySimple

GO

 

CREATE DATABASE TestRecoverySimple ON PRIMARY

( NAME = N'TestRecoverySimple', FILENAME = N'C:\Temp\TestRecoverySimple.mdf' ,

SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB )

LOG ON

( NAME = N'TestRecoverySimple_log', FILENAME = N'C:\Temp\TestRecoverySimple_log.ldf' ,

SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB)

GO

-- setto il recovery model a SIMPLE

alter database TestRecoverySimple set recovery Simple;

GO

-- disabilito l'auto shrink del DB(da non abilitare mai)

alter database TestRecoverySimple set Auto_Shrink OFF;

GO

 

--- Creo la tabella di test

use TestRecoverySimple

GO

create table BigInsert (Id int identity(1,1),

Data datetime default getdate(),

Valore varchar (8000));

GO

-- creo la store procedure di inserimento record

create procedure fpsp_InsertBigRecord (@NumRecord int, @Testo char(1))

as

declare @Counter int

set @Counter = 1

while @Counter <= @NumRecord Begin

insert into dbo.BigInsert (Valore) values (replicate(@Testo,8000));

set @Counter = @Counter + 1;

end

GO

-- creo la Store procedures (per comodita) che presenta i dati sul log

create procedure fpsp_ShowLogDimension (@InstanceName varchar(50))

as

select Instance_Name, Counter_name, cntr_value

from sys.dm_os_performance_counters

where instance_name= 'TestRecoverySimple'

and cntr_type = 65792

and Counter_Name like '%Log%'

;

GO

-- leggo i valori iniziali del log

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- 1016 Kb (40% utilizzato), 0 Shrinks, 0 Growths, 1 Truncation

--- effettuo degli insert

exec fpsp_InsertBigRecord 2000, 'A';

--Leggo le dimensioni del log

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- NON e' aumentato: 1016 Kb (53% utilizzato), ma ha subito 75 Truncations

 

-- Inserimenti sotto transazione

begin tran;

exec fpsp_InsertBigRecord 100, 'B';

-- Rileggo le dimensioni del log

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- E' aumentato: 2040 Kb (73% utilizzato), 1 Growths e 2 truncation rispetto a prima (ora il valore e' 77)

commit;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- L'autoshrink e' disabilitato per cui Non scende la dimensione (2040 Kb)

-- altri inserimenti senza transazioni attive

exec fpsp_InsertBigRecord 2000, 'C';

-- valuto di nuovo il log

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- Nessun inserimento da committare: Non ha avuto bisogno di aumentare (2040 Kb)

-- e' solo aumentato il numero di truncation effettuate (ora 96)

 

-- effettuo uno shrink

DBCC SHRINKDATABASE (TestRecoverySimple);

-- valuto di nuovo il log

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- le dimensioni sono scese:1016 Kb (36% utilizzato)

-- nuovi inserimenti sotto transazione

begin tran;

exec fpsp_InsertBigRecord 200, 'D';

-- valuto di nuovo il log

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- ha subito due grow (il valore ora e' 3) le dimensioni sono: 3064 Kb (83% usato) - log truncation = 123

commit;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- 3064 kb (72%)

--effettuo lo shrink

DBCC SHRINKDATABASE (TestRecoverySimple);

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- ritorno alle dimensioni minime iniziali 1016 kb (35% occupato)

 

-- effettuo un select into

select * into dbo.NewBigTable from dbo.BigInsert;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- Log size 2040 Kb (61% usato) l'attivita' di insert ha richiesto piu' spazio per il log

-- nuovo select into

select * into dbo.NewBigTable2 from dbo.BigInsert;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- Non e' aumentato l'operazione era uguale a prima e non ha richiesto altro spazio

-- Log size 2040 Kb (57% usato)

 

-- nuovo select into

select * into dbo.NewBigTable3 from dbo.BigInsert;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- Continua a non aumentare 2040 Kb (61% usato)

 

-- ora select into sotto transazione

begin tran

-- 1° select into

select * into dbo.NewBigTable4 from dbo.BigInsert;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- non aumenta

---2° select into

select * into dbo.NewBigTable5 from dbo.BigInsert;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- siamo sotto transazione la richiesta di spazio aumenta 3064 Kb (78% usate)

---3° select into

select * into dbo.NewBigTable6 from dbo.BigInsert;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- siamo sotto transazione la richiesta di spazio aumenta ancora 4088 Kb (83% usate)

---4° select into

select * into dbo.NewBigTable7 from dbo.BigInsert;

exec fpsp_ShowLogDimension 'TestRecoverySimple';

-- siamo sotto transazione la richiesta di spazio aumenta ancora 5112 Kb (83% usate)

-- annullo

Rollback

exec fpsp_ShowLogDimension 'TestRecoverySimple';

Restore di un DB Master corrotto

Questa e' carina...

In presenza di un database Master corrotto l'istanza di SqlServer potrebbe non partire, pertanto sara' necessario procedere al restore di una copia valida del Master backuppata precedentemente. Ma, per poter effettuare il restore, occorre che l'istanza di Sql sia su... e ... ci troviamo in una situazione di stallo

Una soluzione drastica e' reinstallare SqlServer, ma esiste una via piu' comoda: reinstallare solo i DB di sistema tramite il seguente comando (documentato nel BOL)

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<Instance> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> e, poi, procedere al restore del "nostro" master.

Questa info l'ho letta nel seguente post dedicato a questa "complicata" situazione: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/09/26/772550.aspx 

Inoltre in quel post viene anche illustrato il procedimento, leggermento piu' lungo, da seguire qualora ci si trovi in presenza di un Sql Express

Non se la cosa era nota a tutti (tranne che a me)... In ogni caso mo' me la sono appuntata qui.. non si sa mai.....