Este tópico descreve como excluir dados ou arquivos de log no SQL Server usando o SQL Server Management Studio ou o Transact-SQL. Este scipt aqui vai resolver :

IF OBJECT_ID('tempdb..#ListaDbOperarcao') IS NOT NULL
BEGIN
drop table #ListaDbOperarcao
END

create table #ListaDbOperarcao (Id int identity(1,1),  NomeBanco varchar(100),LogTamanho_mb decimal,DbTamanho_mb decimal, TotalTamanho_mb decimal)


insert into #ListaDbOperarcao(NomeBanco,LogTamanho_mb,DbTamanho_mb,TotalTamanho_mb)
SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(18,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(18,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
FROM sys.master_files WITH(NOWAIT)
where database_id not in ( DB_ID('master'),DB_ID('ReportServer'),DB_ID('tempdb'),DB_ID('msdb'),DB_ID('model'))
GROUP BY database_id
order by log_size_mb desc

DECLARE @Counter INT, @SqlQry varchar(2000) , @NomeDb varchar(3223) 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
	set @SqlQry = ''
    select @NomeDb =  NomeBanco  from #ListaDbOperarcao where Id = @Counter
	set @SqlQry = @SqlQry +  'Declare @NomeLog varchar(2332) ;'
	set @SqlQry = @SqlQry + ' USE ' + @NomeDb + ' ; '
	set @SqlQry = @SqlQry + ' select @NomeLog = name from sysfiles where name like ''%_Log%'' and fileid = 2 ; '
	set @SqlQry = @SqlQry + ' ALTER DATABASE ' + @NomeDb + ' SET RECOVERY SIMPLE; '
	set @SqlQry = @SqlQry + ' DBCC SHRINKFILE (@NomeLog, 1); '
	set @SqlQry = @SqlQry + ' ALTER DATABASE ' + @NomeDb + ' SET RECOVERY FULL; '


	exec(@SqlQry)
	print @SqlQry
    SET @Counter  = @Counter  + 1
END