Wednesday, April 11, 2012

How to Shrink database in SQL Server

--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
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
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
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
close c_2
deallocate c_2