Sproc to shrink transaction log

---- Written by karen
---- Date: July, 14 2002
---- This sp truncates all transaction log files in the 
---- current database and shrinks the files to the size 
---- specified in MB. 
---- the stored procedure must be created and executed in 
---- the target database.

if exists (select * from sysobjects where name='shrinklog') drop proc shrinklog
go
create proc ShrinkLog @mb int = 10
as
begin
declare @log varchar(20)
declare @log1 varchar(20)
declare @db varchar(10)

set @db=db_name()
backup log @db with truncate_only

declare clog cursor
for select name from sysfiles where filename like '%.ldf%'
open clog 
fetch next from clog into @log
while (@@fetch_status<>-1)
begin
set @log1=rtrim(@log)
dbcc shrinkfile(@log1,@mb)
fetch next from clog into @log
end
deallocate clog
end


GO