LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon