File di Log pieno
FabriZIo
Il file di log del database 'tempdb' è pieno. Eseguire il
backup del log delle transazioni del database per
recuperare spazio di log.
Come posso comportarmi?
Grazie
FabriZio
Lorenzo Benaglia
news:5b8a01c249ad$6b8cbab0$36ef2ecf@tkmsftngxa12...
Ti inoltro una risposta che Gianluca Hotz diede il 7 maggio 2001 sul ng
news://ugiss.public.ita.sqlserver.server
Ciao!
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
--
Lorenzo Benaglia
UGIdotNET - http://www.ugidotnet.org
Cappelletto Gabriele
illimitato. Probabilmente lo hai limitato.
"FabriZIo" <fabr...@7mates.com> ha scritto nel messaggio
news:5b8a01c249ad$6b8cbab0$36ef2ecf@tkmsftngxa12...