Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DB Shrink Expand / Collapse
Author
Message
Posted Sunday, December 16, 2012 7:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:42 PM
Points: 27, Visits: 345
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
Posted Sunday, December 16, 2012 11:53 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1397102
Posted Monday, December 17, 2012 12:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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
Posted Monday, December 17, 2012 1:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:42 PM
Points: 27, Visits: 345
Thanks for the confirmations!
Post #1397122
Posted Tuesday, December 18, 2012 4:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 4, 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
Posted Tuesday, December 18, 2012 5:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 »

Add to briefcase

Permissions Expand / Collapse