Post reply

DB Shrink

  • December 16, 2012 at 7:52 pm

    #271701

    I have a database that is 5.81836 GB in size with 2.3623 GB free space (due to old data being removed).

    The customer frequently needs to ftp a compressed backup of this database to the vendor but thinks shrinking the database itself will make the compressed backup even smaller than it's current size of 480MB. Am I correct in explaining that the compressed backup size will not reduce further even if I perform a shrink on the database as the backup has already removed this free space?

    Thanks

    Steve

  • December 16, 2012 at 11:53 pm

    #1569019

    1) you can use the back compression method of sql server 2008

    2) is the database is in full recovery mode ?

    3) if the vendors request is one time and you can afford then you can go for dbcc shrink database follwed by index rebuild BUT Its usually a pretty bad practice for most databases. The database needs a certain amount of free space to be able to function under regular DML so removing all the free space is just going to cause it to grow again. Also, the data move operation does not 'intelligently' place the data lower in the file, it just moves it to the first free space it can find - this causes index fragmentation and can lower performance.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 17, 2012 at 12:44 am

    #1569027

    shindle 17293 (12/16/2012)

    Am I correct in explaining that the compressed backup size will not reduce further even if I perform a shrink on the database as the backup has already removed this free space?

    Yup. Backups only include the data, not the free space. Shrink just removes the free space. You might get a tiny reduction in the backup because backups work on the extent level (allocated extents) while shrink works on the page level (free pages), but it likely won't be noticable

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 17, 2012 at 1:06 am

    #1569032

    Thanks for the confirmations!

  • December 18, 2012 at 4:59 am

    #1569486

    The database files can be shrunk manually, either as a group or individually, or the database can be set to shrink automatically at specified intervals.Files are always shrunk from the end.

    For example-

    if you have a 5-GB file and specify 4 GB as the target_size in a DBCC SHRINKFILE statement,

    the Database Engine will free as much space as it can from the last 1 GB of the file

    You can perform this operation via some third party tool like this

  • December 18, 2012 at 5:13 am

    #1569493

    itsmemegamind (12/18/2012)

    You can perform this operation via some third party tool like this

    Or you could save the expense of a completely unnecessary tool, as all that will do is call SQL's Shrinkfile operation.

    So spam somewhere else please.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply