DB Shrink

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » DB Shrink

DB Shrink

Rate Topic

Display Mode

Topic Options

Author

Message

shindle 17293

Posted Sunday, December 16, 2012 7:52 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:59 PM
Points: 12, Visits: 161

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

Post #1397058

Bhuvnesh

Posted Sunday, December 16, 2012 11:53 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1397102

GilaMonster

Posted Monday, December 17, 2012 12:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 37,726, Visits: 29,985

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 2008, MVP
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

Post #1397113

shindle 17293

Posted Monday, December 17, 2012 1:06 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:59 PM
Points: 12, Visits: 161

Thanks for the confirmations!

Post #1397122

itsmemegamind

Posted Tuesday, December 18, 2012 4:59 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 04, 2013 12:43 AM
Points: 10, Visits: 45

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

Post #1397701

GilaMonster

Posted Tuesday, December 18, 2012 5:13 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 37,726, Visits: 29,985

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 2008, MVP
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

Post #1397710

« Prev Topic | Next Topic »

Permissions