shrink salesforce database sql server 2008r2

  • 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

  • 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

  • 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]

  • 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.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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