--Shrink Log
if object_id('tempdb.dbo.#file_info','u') is not null
drop table tempdb.dbo.#file_info
create table #file_info
(
dbname varchar(255),
logical_name varchar(255),
physical_name varchar(255)
)
insert into #file_info (dbname)
select '['+name+']' from master..sysdatabases where name not in ('Database1','Database2')
declare @sql varchar(2000), @dbname varchar(255)
declare c_1 cursor for select distinct dbname from #file_info
open c_1
fetch next from c_1 into @dbname
while @@fetch_status <> -1
begin
select @sql = 'update #file_info
set physical_name = filename, logical_name = name
from '+@dbname+'..sysfiles
where dbname = '''+@dbname+'''
and filename like ''%.ldf'''
exec (@sql)
fetch next from c_1 into @dbname
end
close c_1
deallocate c_1
----------------------------------------------------------
declare @shrink_sql varchar(2000),
@shrink_dbname varchar(255),
@shrink_logical_name varchar(255)
declare c_2 cursor for select dbname,logical_name from #file_info
open c_2
fetch next from c_2 into @shrink_dbname,@shrink_logical_name
while @@fetch_status <> -1
begin
select @shrink_sql = '
use '+@shrink_dbname+'
dbcc shrinkfile ('+'['+@shrink_logical_name+']'+', notruncate)
dbcc shrinkfile ('+'['+@shrink_logical_name+']'+', truncateonly)
'
exec (@shrink_sql)
fetch next from c_2 into @shrink_dbname,@shrink_logical_name
end
close c_2
deallocate c_2
go
