Question about Shrinking

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Question about Shrinking

Question about Shrinking

Rate Topic

Display Mode

Topic Options

Author

Message

YarHad

Posted Tuesday, September 25, 2012 3:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 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

Suresh B.

Posted Tuesday, September 25, 2012 3:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 1,075, Visits: 5,119

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

anthony.green

Posted Tuesday, September 25, 2012 3:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

YarHad

Posted Tuesday, September 25, 2012 3:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 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

anthony.green

Posted Tuesday, September 25, 2012 4:02 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1363900

YarHad

Posted Tuesday, September 25, 2012 4:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 1:44 AM
Points: 34, Visits: 211

Thank you very very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Post #1363916

Grant Fritchey

Posted Tuesday, September 25, 2012 4:46 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1363938

vyas

Posted Tuesday, September 25, 2012 5:06 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056

You can consider using split backups

Post #1363947

YarHad

Posted Tuesday, September 25, 2012 6:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 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 »

Permissions