Réduire le nombre de VLF du journal de transactions
Compter les VLF de chaque base SQL Server, examiner le journal d'une base et réduire son nombre de VLF par sauvegarde, troncature et redimensionnement.
Un journal de transactions est découpé en fichiers logiques appelés VLF (virtual log files). Un nombre excessif de VLF ralentit certaines opérations, comme la récupération et les sauvegardes du journal. Cet article donne les requêtes pour compter les VLF par base, retrouver les bases concernées et réduire le journal.
Compter les VLF de chaque base en ligne
Le script lit DBCC LOGINFO pour chaque base en ligne. Le format de sortie de DBCC LOGINFO change à partir de SQL Server 2012 (version 11) : le script adapte donc la table de réception.
--variables to hold each 'iteration'
declare @query varchar(100)
declare @dbname sysname
declare @vlfs int
--table variable used to 'loop' over databases
declare @databases table (dbname sysname)
insert into @databases
--only choose online databases
select name from sys.databases where state = 0
--table variable to hold results
declare @vlfcounts table
(dbname sysname,
vlfcount int)
--table variable to capture DBCC loginfo output
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version
declare @MajorVersion tinyint
set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1)
if @MajorVersion < 11 -- pre-SQL2012
begin
declare @dbccloginfo table
(
fileid smallint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
else
begin
declare @dbccloginfo2012 table
(
RecoveryUnitId int,
fileid smallint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo2012
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
--output the full list
select dbname, vlfcount
from @vlfcounts
order by dbname
Examiner une base
Une fois la base concernée identifiée, afficher ses fichiers puis sa liste de VLF :
use AppDb
go
EXEC sp_helpfile;
GO
DBCC LOGINFO
GO
Pour voir l'espace utilisé de chaque fichier de la base courante, la requête suivante renvoie la taille, l'espace utilisé, l'espace libre et la croissance automatique :
SELECT DB_NAME() AS [database_name],
name AS [file_name],
[type_desc],
[physical_name],
CONVERT(DECIMAL(10,2),SIZE/128.0) AS [current_size_MB],
CONVERT(DECIMAL(10,2),CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS [space_used_MB],
CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(SIZE/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [percent_used],
CONVERT(DECIMAL(10,2),size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS [free_space_MB],
CONVERT(DECIMAL(10,2),((SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)/(SIZE/128.0))*100) AS [percent_free],
'By ' + CASE is_percent_growth
WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -'
ELSE '' END
+ CASE max_size
WHEN 0 THEN 'DISABLED'
WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END AS [auto_growth]
FROM sys.database_files;
Réduire le journal
Les étapes suivantes sauvegardent le journal, le tronquent, puis redimensionnent le fichier de journal à 1 Go. Le nom logique du fichier de journal est obtenu avec sp_helpfile.
USE AppDb
GO
CHECKPOINT
go
BACKUP LOG AppDb TO DISK='D:\Backup\AppDb_log.bak' WITH COMPRESSION
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AppDb_log,TRUNCATEONLY)
GO
ALTER DATABASE AppDb
MODIFY FILE
(NAME = AppDb_log,SIZE = 1GB)
GO
DBCC LOGINFO
GO
Notes
La sauvegarde du journal n'est possible qu'avec un modèle de récupération qui conserve les journaux (FULL ou BULK_LOGGED). Remplacer AppDb et AppDb_log par le nom de la base et le nom logique du fichier de journal. Le chemin de sauvegarde est un exemple.