Cuantos VLF es demasiado?

/0 Comentarios/en , /por

Entendiendo que es VLF

De acuerdo al tamaño que tenga nuestro archivo de LOG (.ldf) es importante controlar los VLF (Virtual Log File).

Los VLF, son los archivos virtuales en que se divide nuestro log, y un exceso de los mismos puede provocar una reducción del rendimiento.

Verificando los VLF

  • Verificar cuantos VLF tenemos en nuestra base
  • De manera invidual

 

DBCC LOGINFO
  • De forma masiva
--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 as 'Base', vlfcount  'Cant'
from @vlfcounts  
order by vlfcount desc

En base a la cantidad deberemos seguir el siguiente criterio

  • Menos de 1 MB, ignorar este caso.
  • Hasta  64 MB: 4 VLFs, cada uno de 1/4 del tamaño
  • De 64 MB a 1 GB: 8 VLFs, cada uno de 1/8 del tamaño
  • Mas de 1 GB: 16 VLFs, cada uno  de 1/16 del tamaño

 

Si obtenemos una cantidad mayor tenemos que:

  • Verificar el tamaño del LOG.
  • Reducir el LOG(*).
  • Crear de nuevo el LOG con un tamaño inicial igual al existente antes de reiniciarlo.

(*) será conveniente hacer un backup del mismo

 

-- Detecting and reducing VLFs in SQL Server 2008
-- Glenn Berry 
-- June 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Switch to your database
USE ngmetadata;
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Check individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;

-- Step 1: Compressed backup of the transaction log (backup compression requires Enterprise Edition in SQL Server 2008)
BACKUP LOG [ngmetadata] TO  DISK = N'N:\SQLBackups\ngmetadataLogBackup.bak' WITH NOFORMAT, INIT,  
NAME = N'ngmetadata- Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;
GO

-- Step 2: Shrink the log file
DBCC SHRINKFILE (N'ngmetadata_log' , 0, TRUNCATEONLY);
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Step 3: Grow the log file back to the desired size, 
-- which depends on the amount of write activity 
-- and how often you do log backups
USE [master];
GO
ALTER DATABASE ngmetadata MODIFY FILE (NAME = N'ngmetadata_log', SIZE = 8GB);
GO

-- Switch back to your database
USE ngmetadata;
GO

-- Check VLF Count for current database after growing log file
DBCC LogInfo;