SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB Shrink


DB Shrink

Author
Message
shindle 17293
shindle 17293
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 650
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12772 Visits: 4077
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;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215596 Visits: 46270
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


shindle 17293
shindle 17293
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 650
Thanks for the confirmations!
itsmemegamind
itsmemegamind
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215596 Visits: 46270
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search