Cuantos VLF es demasiado?
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;
