En effet, DBCC SHRINKFILE ne fait que tronquer la partie inactive en fin du fichier de transaction.
Si le transaction log fait 25Go et que "le pointeur de transaction" se trouve actuellement en fin de fichier (ex: à l'offset 24.7 Go, ce qui est assez fréquent); dans ce cas, DBCC SHRINKFILE ne libèrera que 300Mo.
Il faudra attendre un roll-over vers le début du fichier de transaction pour avoir un résultat probant.
Lorsque que le transaction log fait 25 Go et qu'il étouffe littéralement le système de fichier, il n'est pas forcement possible d'attendre patiemment l'opération de Roll-over sur le transaction log. Libérer de l'espace disque peut être impératif pour la bonne conduite d'autres opérations sql.
C'est là qu'intervient précisément sp_force_shrink_log. Cette store procédure va déplacer le pointeur de transaction dans le transaction log en simulant des activités sql sur une table temporaire. Une fois le roll-over effectué (ce qui arrivera tôt ou tard), le transaction log sera efficacement tronqué et la stored procedure pourra interrompre son traitement.
Malheureusement, la store procedure d'origine prévue pour SQL 7 ne fonctionne pas correctement sur SQL Serveur 2005. En effet, le résultat retourné par DBCC LogInfo (utilisé par sp_force_Shrink_log) a été modifié depuis SQL 7.
Voici donc une petite mise à jour (faite par mes soins) du script sp_force_shrink_log pour SQL Serveur 2005 (sp_force_shrink_log_2005.sql).
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
/*
*************************************************************
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 2005.
Switch context to proper db to execute.
Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
exec pubs..sp_force_shrink_log
Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
zanevsky@azdatabases.com
Note: D Meurisse, *no_company_info* , 12/01/2009, v5.1 - 12/01/2009
Updated this wonderfull script to make it running on SQL 2005.
Updated the description and change version to v5.1
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
Output Params:
--------------
Return:
Results:
---------
Locals:
--------
@err Holds error value
Modifications:
--------------
V5.1 - Meurisse D. - 1 dec 2009 - small changes to make it working with Sql 2005
************************************************************* */
@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
/* drop table #LogInfo
drop table #LogFiles
*/
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
/* CreateTime datetime */
CreateLSN varchar(50)
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
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*/ CreateLSN ) 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 )
print 'xx.10'
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 */ CreateLSN ) 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
if object_id( 'sp_force_shrink_log') Is Null
select 'sp_force_shrink_log Not Created'
else
select 'sp_force_shrink_log Created'
go
ressources:
- Voir cet article traitant de la version Sql 7 de la stored procedure sp_force_shrink_log (sur SQL Server Central).
- Voir l'article "Utilitaires pour SQL Server 7" sur ce blog.
- Voir l'article "Réduire la taille de TempDB" sur ce blog (pour un usage efficace de DBCC SHRINKFILE)
Aucun commentaire:
Enregistrer un commentaire