use master
go
set quoted_identifier off
go
Create procedure USP_TRANLOGBAK_THRESHOLD 
@dbname sysname,
@threshold int,
@backuppath varchar(1000)
as
set nocount on
declare @maxcount int
declare @count int 
declare @truncate_only bit 
declare @backupname varchar(800)
declare @status int
declare @query varchar(600)
declare @used int
set @query =''
Create table #logtable (dbname varchar(256), size bigint, Used int, statusx int)
set @query = 'dbcc sqlperf(logspace)'
insert into #logtable exec (@query)

create table #dbtable (id int identity(1,1), dbname varchar(256), status int, used int)
if @dbname = '' 
begin
insert into #dbtable (dbname, status, used) select name,status, used from master..sysdatabases a
join #logtable b on a.name=b.dbname
where   name not in('tempdb','model')  and status & 32 != 32
and status & 64 != 64 and status & 128 != 128
and status & 256 != 256   and status & 512 != 512
and status & 1024 != 1024   and status & 4096 != 4096
and status & 32768 !=32768  
end
else
begin
insert into #dbtable (dbname, status, used) select name,status, used from master..sysdatabases a
join #logtable b on a.name=b.dbname
where   name not in('tempdb','model')  and status & 32 != 32
and status & 64 != 64 and status & 128 != 128
and status & 256 != 256   and status & 512 != 512
and status & 1024 != 1024   and status & 4096 != 4096
and status & 32768 !=32768  
and name = @dbname
end


set @count =1
set @maxcount =  scope_identity()
While @count <= @maxcount
begin
select @dbname =dbname, @status =status, @used=used  from #dbtable where id = @count

set @backupname =@dbname +'_'+convert(varchar(25),getdate(),112)+'_'+replace(convert(varchar(25),getdate(),108),':','_')+'.TRN'
if @status & 8 = 8 
begin
select @truncate_only = 1
end
else
begin
select @truncate_only = 0
end

if @truncate_only = 1 and @used >@threshold
	begin
	set @query = 'set quoted_identifier off BEGIN BACKUP LOG [' +@dbname +'] WITH TRUNCATE_ONLY END'
	print @query
	exec (@query)
	end

if @truncate_only = 0 and @used >@threshold
	begin
	set @query = 'set quoted_identifier off BEGIN BACKUP LOG [' + @dbname + '] to disk = "'+ @backuppath +@backupname+'"  END'
	print @query
	exec (@query)
	end

set @count =@count+1
end
drop table #logtable 
drop table #dbtable

--Usage :
--USP_TRANLOGBAK_THRESHOLD '',45,'d:\'
--USP_TRANLOGBAK_THRESHOLD 'MyDB',80,'d:\backup\'