September 25, 2012 at 3:18 am
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
September 25, 2012 at 3:26 am
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?
September 25, 2012 at 3:27 am
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.
September 25, 2012 at 3:58 am
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.
September 25, 2012 at 4:02 am
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.
September 25, 2012 at 4:24 am
Thank you very very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
September 25, 2012 at 4:46 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 25, 2012 at 5:06 am
You can consider using split backups
September 25, 2012 at 6:07 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply