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

Question about Shrinking Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 3:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 1:44 AM
Points: 34, Visits: 211
Hello all,
I have read loads of articles about the danger of shrinking and
I am under the impression that the reason is because it causes index fragmentation,
Defraging/rebuilding the index increases the transaction log e.t.c

My question is this...
If I have a large database, and I decide to remove tables, archieve data e.t.c would shrinking the database reduce the backup size?
The backup is massive and it takes a long time to copy it from one storage facility to another and also takes time to restore.
So if i clean up the database and then shrink it, will it reduce the backup size?
Thanks for listening
Post #1363872
Posted Tuesday, September 25, 2012 3:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
Shrinking does't affect backup size. Becuase backup anyway contains only the used pages. Backup doesn't contain empty pages.

Have you considered compressed backup?
Post #1363880
Posted Tuesday, September 25, 2012 3:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 5,216, Visits: 5,109
If you drop tables or move data from the main database to an archive database then you are effectively creating white space.

While white space is backed up in the backups it doesn't occupy any space, it is simply a marker to say white space, so that when the database is restored it knows how to create the files as they where at the point of backup.

So in essence, no you dont need to shrink the file to make the backup smaller, SQL will handle that for you.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1363881
Posted Tuesday, September 25, 2012 3:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 1:44 AM
Points: 34, Visits: 211
Have you considered compressed backup?
[/quote]

Hi Ten Centuries and Hall of Fame,
the backup already has compression turned on.
Please forgive me if I seem daft, but I am new to this...
If I have a DB that is 100 gig in allocated size however the data in the DB is only 50 gig, are you saying that the backup will be 50 gig and not 100 gig?
If that is so, then if I am able to archive/delete/cleanup the DB and reduce the data from 50 gig to 40 gig wouldn't my backup them be 40 gig?
as you said the backup only contains used pages and not empty pages and backed up white space occupys no space
Please do have patience.
Post #1363897
Posted Tuesday, September 25, 2012 4:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 5,216, Visits: 5,109
Yes, that is correct, if the db is sized at 100GB but only has 50GB used then the backup would be around 50GB (give or take), same again, if the data went to 40GB out of 100GB then the file will be 40GB (give or take).

Compression will obivously change that value depending on how much compression you can get and the data types used in the database.





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1363900
Posted Tuesday, September 25, 2012 4:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 1:44 AM
Points: 34, Visits: 211
Thank you very very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Post #1363916
Posted Tuesday, September 25, 2012 4:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
If you know for sure that you've removed data permanently from the database and the data is not going to just grow to refill the space you freed up, you can do a one-time shrink on the database. The problem most people run into is repeatedly shrinking, growing, shrinking, growing, shrinking... Which leads to major issues. Shrinking once, not followed by another batch of growth, is not a big deal.

But...

I'd be very sure that you're not going to grow again. If you even suspect it might be an issue, I'd leave it alone and not shrink.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1363938
Posted Tuesday, September 25, 2012 5:06 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 6:00 AM
Points: 3,478, Visits: 1,074
You can consider using split backups


Post #1363947
Posted Tuesday, September 25, 2012 6:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 1:44 AM
Points: 34, Visits: 211
Thanks SSChampion,
for the info. Infact the database will grow as time goes by and the main reason I was thinking of shrinking is that
I thought it would affect the backup. As the shrinking isn't going to affect the backup,
I am going to drop the idea of shrinking as it is not worth the hassel. The database is still going to grow anyway!
Thanks for all your help
Post #1363964
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse