September 20, 2008 at 8:59 am
I use the following sql in SSIS to shrink databases, still de database does not shrink.
If i use the option tasks, Shrink, database within SQL server management studio the database shrinks however.
Need i add an extra option within the dbcc shrinkdatabase command?
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @dbnaam varchar(255),
@cmd varchar(255)
declare cur cursor for
select name from master..sysdatabases
where name not in ('model', 'master', 'msdb')
open cur
fetch next from cur into @dbnaam
while (@@fetch_status <> -1)
begin
set @cmd='DBCC SHRINKDATABASE ('''+@dbnaam+''', 25,TRUNCATEONLY)'
exec (@cmd)
fetch next from cur into @dbnaam
end
close cur
deallocate cur
END
September 20, 2008 at 10:24 am
Shrink with the truncate_only command will release unused space at the end of the file to the file system. If the free space within the file is scattered around, as is normal for a SQL database, shrink with truncate only won't do a thing.
You can shrink without that option and SQl will reorganise all the pages within the DB to get the free space to the end of the file where it can be released. If you do that, you really need to rebuild all your indexes straight after (which will probably cause the DB to grow again) as a shrink will shuffle the pages of the indexes very, very badly.
Why are you shrinking all of your user databases and tempDB on a regular basis?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2008 at 3:38 pm
Thanks Gail,
Its a developers environment with little diskspace on which we occassionally have to load and process data thats about as big as the total available diskspace.
Regards,
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply