TRUNCATING VIRTUAL LOG FILE COUNT IN ALL THE DATABASES IN SQL SERVER

The below query will truncate the virtual log file count if it is greater than 10, I have chosen 10 here for my testing purpose.


--variables to hold each 'iteration'
SET NOCOUNT ON
declare @query varchar(100)  
declare @dbname sysname  
declare @vlfs int 
DECLARE @LogFileName varchar(1000)
DECLARE @VirtualLogFile TABLE
(Sno INT IDENTITY(1,1),dbname VARCHAR(1000),VLFCOUNT INT)
DECLARE @I INT
DECLARE @Count INT
DECLARE @Stmt VARCHAR(4000)
DECLARE @DatabaseName VARCHAR(1000)
DECLARE @DatabaseFiles TABLE
(IDNo INT IDENTITY(1,1),LogFileName VARCHAR(2000),DatabaseName VARCHAR(2000))
DECLARE @LastTable TABLE
(Sno INT IDENTITY(1,1),dbName VARCHAR(1000),LogFileName VARCHAR(1000))

--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  
INSERT INTO @VirtualLogFile
(dbname,VLFCOUNT)
select dbname, vlfcount  
from @vlfcounts  
WHERE vlfcount >10-->Change Virtual log file count here
order by dbname
INSERT INTO @DatabaseFiles
(LogFileName,DatabaseName)
SELECT DataFileName=name,db_name(database_id)
FROM sys.master_files
where type_desc='LOG'
INSERT INTO @LastTable
(dbName,LogFileName)
SELECT dbname,LogFileName
FROM @VirtualLogFile V INNER JOIN @DatabaseFiles DF
ON V.dbname=DF.DatabaseName
SET @I=0
SELECT @Count=COUNT(*) FROM @VirtualLogFile
WHILE @I<@Count
BEGIN
    SET @I=@I+1
SELECT @DatabaseName=dbname,@LogFileName=LogFileName
FROM @LastTable
WHERE Sno=@I
SET @Stmt='USE '+ @DatabaseName+'
GO
DBCC SHRINKFILE (N'+''''+@LogFileName+''''+',0,TRUNCATEONLY); 
'
PRINT @Stmt
END