Newsgroup: it.comp.software.database
Titolo: Resize file di Log SQL Server 7

Ciao a tutti,
avrei bisogno di ridimensionare il files di log del mio database SQL Server
che è diventato di dimensioni sproporzionare rispetto al file dei dati.
Mi ha suggerito di spegnere brutalmente il server sulla quale esso è
installato, sarebbe stato poi il server stesso a compattarlo quando sarebbe
ripartito, ma ... non è successo assolutamente niente.
Qualcuno di voi puo' darmi suggerimenti su cosa fare.
Grazie in anticipo
Giovanni

 sarebbe ripartito, ma ... non è successo assolutamente niente.
il file di log ti serve per le recovery e i backup "a caldo".
(spiegato molto brutamlente e alla veloce)
se effettui backup a caldo è presupposto che tu effettui dump del 
transaction log su nastro (o chi per esso)
altrimenti puoi dargli l'opzione di truncate log on checkpoint, questo ti 
risolve il problema.
vedi la voce
"TruncateLogOnCheckpoint property"
sul manuale.
per attivare l'opzione da EM, proprietà del DB, opzioni, tronca il log al 
completamento del checkpoint (checkbox).
con questa opzione comunque riesci a recoverare le transazioni perse dopo 
l'ultimo checkpoint, in caso di crash del server. ovviamente, se ti 
corrompi il file dati o ti fumi dei dati, devi restorare dall'ultimo backup 
a freddo...
ciao

 wrote in message
news:aar52o$g2n$1...@fe2.cs.interbusiness.it...

Ciao Giovanni,
per liberare lo spazio occupato nel transaction log devi
eliminare la parte di transazioni che e' stata consolidata
fisicamente dal meccanismo di CHECKPOINT.
Tale operazione puo' essere fatta manualmente eseguendo
un backup del LOG con il comando BACKUP LOG oppure
eseguendo un backup completo tramite il comando BACKUP
DATABASE. In alternativa e' possibile liberare lo spazio
occupato dalle transazioni senza farne il backup tramite
il comando BACKUP LOG nomedb WITH TRUNCATE_ONLY.
N.B. Se il l'opzione del database "Truncate log on checkpoint"
e' attiva, il log viene automaticamente liberato al CHECKPOINT.
Normalmente questa opzione, che puo' risultare comoda in fase di
sviluppo, e' inaccettabile in un ambiente di produzione in
quanto legata alle politiche di backup.
A questo punto lo spazio del(i) file(s) del transaction log
non e' stato automanticamente rilasciato al sistema operativo
(a meno che l'opzione auto-shrink del database in questione
non sia attiva).
Per recupare lo spazio utilizzato fisicamente dal transaction
log e' necessario utilizzare i comandi amministrativi
DBCC SHRINKDATABASE oppure DBCC SHRINKFILE.
Non e' finita qui.... il file di log potrebbe comunque non
rimpicciolirsi, oppure rimpicciolirsi solo in parte, perche'
internamente il log viene diviso in sotto-unita' chiamate
virtual logs ed il rimpicciolimento e' vincolato al fatto che
la parte attiva del log sia all'inizio del file attivo.
E' possibile utilizzare il comando DBCC LOGINFO(nomedb) per
verificare dove si trova la parte attiva del log nell'esempio
sottostante, la parte  attiva e' quella marcata con Status = 2,
si trova in coda al file, la funzione di SHRINK non sara' quindi
eseguita finche' la parte attiva non si muovera' nuovamente
in testa al file ed il virtual log in coda non sara' liberato.
FileId FileSize StartOffset FSeqNo Status Parity CreateTime
------ -------- ----------- ------ ------ ------ -----------------------
2      13041664 8192        0      0      0      1999-03-16 10:27:24.917
2      13041664 13049856    0      0      0      1999-03-16 10:25:56.730
2      13041664 26091520    0      0      0      1999-03-16 10:25:56.730
2      13041664 39133184    0      0      0      1999-03-16 10:25:56.730
2      13041664 52174848    0      0      0      1999-03-16 10:25:56.730
2      13041664 65216512    0      0      0      1999-03-16 10:25:56.730
2      13041664 78258176    0      0      0      1999-03-16 10:25:56.730
2      13557760 91299840    5      2      64     1999-04-06 12:32:27.833
E' possibile far muovere la parte attiva del log in testa al file
continuando a generare transazioni, liberandone lo spazio utilizzato con
i comandi di BACKUP LOG/DATABASE ed eseguendo il comando di DBCC finche'
il file non si ridimensiona.
In coda al messaggio ho allegato una stored procedure che serve
proprio a tale scopo.
Ultima cosa: il transaction log non puo' comunque avere dimensione
inferiore alla dimensione dei singoli virtual log.
Tale dimensione e' determinata in fase di creazione del log (vedere
Books Online per maggiori informazioni).
Esiste anche una pratica non supportata da Microsoft, quindi sconsigliata,
che consiste nel'assicurarsi di non avere utenti collegati al database,
eseguire il comando di CHECKPOINT, fermare il servizio SQL Server,
eliminare il file fisico del transaction log. Alla ripartenza del
servizio verra' creato un file di 2MB per il transaction log.
N.B. questa tecnica non sempre funziona, alcuni utenti hanno
riscontrato problemi gravi utilizzandola quindi va presa con le
dovute cautele.
--
Gianluca Hotz
Backoffice MVP (SQL Server) MCP SQL Server and MCP Windows
Founding member of the Italian User Group for SQL Server
http://www.ghotz.com
http://www.ugiss.org
--
create proc sp_force_shrink_log
----------------------------------------------------------------------------
----
-- Purpose: Shrink transaction log of the current database in SQL Server
7.0.
-- Author:  Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v2 - 02/16/2000
--          
zane...@azdatabases.com
----------------------------------------------------------------------------
----
    @target_percent tinyint = 0,
    @target_size_MB int = 10,
    @max_iterations int = 1000,
    @backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on
declare @db         sysname,
        @last_row   int,
        @log_size   decimal(15,2),
        @unused1    decimal(15,2),
        @unused     decimal(15,2),
        @shrinkable decimal(15,2),
        @iteration  int,
 @file_max   int,
 @file     int,
 @fileid     varchar(5)
select  @db = db_name(),
        @iteration = 0
create table #loginfo (
    id          int identity,
    FileId      int,
    FileSize    numeric(22,0),
    StartOffset numeric(22,0),
    FSeqNo      int,
    Status      int,
    Parity      smallint,
    CreateTime  datetime
)
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles
where status & 0x40 = 0x40
select @file_max = @@rowcount
if object_id( 'table_to_force_shrink_log' ) is null
 exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert  #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity,
CreateTime ) exec ( 'dbcc loginfo' )
select  @last_row = @@rowcount
select  @log_size = sum( FileSize ) / 1048576.00,
        @unused = sum( case when Status = 0 then FileSize else 0 end ) /
1048576.00,
        @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then
FileSize else 0 end ) / 1048576.00
from    #loginfo
select  @unused1 = @unused -- save for later
select  'iteration'          = @iteration,
        'log size, MB'       = @log_size,
        'unused log, MB'     = @unused,
        'shrinkable log, MB' = @shrinkable,
        'shrinkable %'       = convert( decimal(6,2), @shrinkable * 100 /
@log_size )
while @shrinkable * 100 / @log_size > @target_percent
  and @shrinkable > @target_size_MB
  and @iteration < @max_iterations begin
    select  @iteration = @iteration + 1 -- this is just a precaution
    exec( 'insert table_to_force_shrink_log select name from sysobjects
           delete table_to_force_shrink_log')
    select @file = 0
    while @file < @file_max begin
        select @file = @file + 1
        select @fileid = fileid from #logfiles where id = @file
        exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
    end
    exec( 'backup log ' + @db + ' ' + @backup_log_opt )
    truncate table #loginfo
    insert  #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status,
Parity, CreateTime ) exec ( 'dbcc loginfo' )
    select  @last_row = @@rowcount
    select  @log_size = sum( FileSize ) / 1048576.00,
            @unused = sum( case when Status = 0 then FileSize else 0 end ) /
1048576.00,
     @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then
FileSize else 0 end ) / 1048576.00
    from    #loginfo
    select  'iteration'          = @iteration,
            'log size, MB'       = @log_size,
            'unused log, MB'     = @unused,
            'shrinkable log, MB' = @shrinkable,
            'shrinkable %'       = convert( decimal(6,2), @shrinkable * 100
/ @log_size )
end
if @unused1 < @unused
select  'After ' + convert( varchar, @iteration ) +
        ' iterations the unused portion of the log has grown from ' +
        convert( varchar, @unused1 ) + ' MB to ' +
        convert( varchar, @unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where
@unused > 10
union all
select 'you may try running this procedure again with a higher number of
iterations.' where @unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several
Megabytes.' where @unused <= 10
else
select  'It took ' + convert( varchar, @iteration ) +
        ' iterations to shrink the unused portion of the log from ' +
        convert( varchar, @unused1 ) + ' MB to ' +
        convert( varchar, @unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
go