September 20, 2019 at 3:09 pm
I've got a bloated sql salesforce database that i want to shrink. I have a script to incremental shrink the db then rebuilding the indexes. for some reason I'm unable to shrink even when i try and shrink by 100 megs. just spins. Tried taking full backup beforehand and still not shrinking. For example this never completes. dbcc shrinkfile ( Salesforce, 529057 ) . It's trying to shrink by 100 megs. DB is in simple mode
below is shrink script
set nocount on
declare @DBFileName varchar(255)
declare @sz int=0
declare @TargetFreeMB int
declare @ShrinkIncrementMB int
-- Set Name of Database file to shrink
set @DBFileName = 'Salesforce'
-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 264
-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB= 100
-- Show Size, Space Used, Unused Space, and Name of all database files
select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB]= convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB]= convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName]= a.name
from sysfiles a
where a.name= 'Salesforce'
declare @sql varchar(8000)
declare @SizeMB float
declare @UsedMB float
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.0
select @SizeMB,@UsedMB,@TargetFreeMB '@TargetFreeMB',@ShrinkIncrementMB '@ShrinkIncrementMB'
set @sz = @SizeMB-@ShrinkIncrementMB
set @sz = cast(@sz as int)
-- Loop until file at desired size
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin
set @sz = cast((@SizeMB-@ShrinkIncrementMB) as int)
set @sql = 'dbcc shrinkfile ( '+@DBFileName+', ' + convert(varchar(20),(@sz ))+' ) WITH NO_INFOMSGS'
print 'Start ' + @sql + ' at ' + convert(varchar(30),getdate(),121)
exec ( @sql )
--int @sql
print 'Done ' + @sql + ' at '+convert(varchar(30),getdate(),121)
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.0
print 'SizeMB=' + convert(varchar(20),@SizeMB) + ' UsedMB=' + convert(varchar(20),@UsedMB)
end
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
September 20, 2019 at 3:15 pm
what's your growth settings on the files?
and are you in full recovery mode? where is the bulk of the data? is it in the log?
MVDBA
September 20, 2019 at 3:17 pm
Are there LOB columns in the database? If there are LOB columns it takes forever to shrink the database. Or more precisely: it can take a very long time. And it does not help if you only shrink 100 MB at a time. There is no reason not to go for the desired size directly.
But if there is plenty of LOB data, it may be better to bulk out all, and load the data into an empty database.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 20, 2019 at 6:37 pm
well that explains it. THe salesforce db is filled w/ LOB data. I typically use incremental in canse i have to kill it so it wont take hours to roll back. then when i run again it will pick up after it left off. I think i may have to take you suggestion about bulk loading all the tables. Prob take a day or so.
September 20, 2019 at 6:59 pm
You could also add a new filegroup and move the large tables into the new filegroup. Then shrink the original file. The big disadvantage, of course, is that you now have an additional file group in the db.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 20, 2019 at 7:13 pm
so it wont take hours to roll back.
There is no much rollback with shrink. It works with many small transactions as it moves pages around.
Note that to move the tables as Scott says, you need to create new versions of the tables. To my knowing there is no way to move where the LOB data is stored for an existing table.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply