Big DB with lots of free space. Can I shrink?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 Administration » Big DB with lots of free space. Can I shrink?...

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Big DB with lots of free space. Can I shrink?

Expand / Collapse

Author

Message

Posted Monday, January 07, 2013 7:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 5:19 AM
Points: 7, Visits: 23

I'm a new hire (developer....there are no DBAs) on a project whose database occupies 370GB on a drive with 1GB of free space. I was able to truncate about 2 dozen tables full of years of what should have been temp data and delete everything older than 4 years (customer requirement) from a few dozen other tables. The data file now has ~60% free space. I wrote some delete/truncate routines that should keep future growth at a minimum and I need to reclaim some of this space for new project requirements. I know shrinking isn't usually a good idea but I'm not sure I have much choice right now. Getting extra disk capacity will take weeks. I've read that shrinking creates very high fragmentation and an index rebuild will be required. I also read that the index rebuild can cause the database to grow larger than it was before the shrink, and if that happens I'm hosed. I need to do something ASAP. Any suggestions/recommendations would be greatly appreciated.

--------------------------------------------------------------

"Daterbase, taterbase, gatorbase."

Post #1403625

Posted Monday, January 07, 2013 8:04 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

In that kind of situation, yeah, shrink the database, and rebuild the indexes. See if you have the option to rebuild them in tempdb, since that can help reduce the re-growth.

The real problem with shrinking a database is when it's done automatically, or when it's done repeatedly "because it keeps growing". A one-off because it was bloated is a different story. That's sometimes necessary/desirable.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1403631

Posted Monday, January 07, 2013 8:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 5:19 AM
Points: 7, Visits: 23

Thanks GSquared.

In my case tempdb is on the same disk, so will sorting in tempdb still help?

Also, do I actually need to worry about about the indexes taking up the ~170GB I'm about to free up, or am I just being paranoid?

--------------------------------------------------------------

"Daterbase, taterbase, gatorbase."

Post #1403665

Posted Monday, January 07, 2013 8:46 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

It depends on the size of the indexes. They have to have enough room to be rebuilt. How big are they?

Rebuilding in tempdb won't necessarily help with overall disk usage, but it might help keep the primary database shrunk.

Different databases, different experiences, on this kind of thing. Do you have a test server that you can copy the database to and try it there?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1403671

Posted Monday, January 07, 2013 9:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 5:19 AM
Points: 7, Visits: 23

Ah, that makes sense. Thanks.

I'm gathering index sizes now. With over 500 tables it will take some time.

We do have a test database but we don't have direct access to our production database. Getting a backup involves mailing an external drive across the country where the folks who run our data center have to schedule time to put a backup on it and mail it back to us. Fun stuff.

--------------------------------------------------------------

"Daterbase, taterbase, gatorbase."

Post #1403703

Posted Monday, January 07, 2013 9:31 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Fun.

We have a third party hosting solution for one of our production databases and our web servers. Because of non-standard backup retention policies designed by my predecessor, if I want to restore a database on that server, I can't do it myself, but have to get the hosting company to do it for me. If the backup is older than 1 week, they have to get a tape shipped to them from another company (does that make that company "fourth party"?). Headaches by design.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1403706

Posted Monday, January 07, 2013 9:32 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:23 AM
Points: 5,264, Visits: 11,189

just look at the largest tables, you'll get an idea very quickly if any will grab your space back. Make sure the growth factor on the data and log files is such that it won't grow by more than necessary (i.e. a setting like 10%) and don't shrink it so have you no free space left in the first place.

---------------------------------------------------------------------

Post #1403707

Michael Valentine Jones

Posted Monday, January 07, 2013 9:50 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941, Visits: 10,481

If you are doing index rebuilds, you will need enough free space for the largest index you are going to rebuild. Usually, it with be the largest table with a clustered index.

You can use the script on the link below to see the size of your tables:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

You need to make sure that your tables have clustered indexes to rebuild. If they don't, an index rebuild will not help. If possible, you should have clustered indexes on all tables. If you need to rebuild a table that does not have a clustered index, you can use this command:

ALTER TABLE [MyTable] REBUILD

If you have really big tables, you may want to reorganize the indexes instead of rebuild them. It's an online operation and will not need as much free space in the data files.

Rebuild or reorganize will generate a lot of transaction log entries, so make sure that you database log files are large enough to hold them, and make sure you are running transaction log backups frequently if your database is in full recovery.

You can use this script to shrink your database data files:
Shrink DB File by Increment to Target Free Space
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Post #1403716

Posted Monday, January 07, 2013 11:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 5:19 AM
Points: 7, Visits: 23

Thanks Michael.

I verified that the vast majority of the tables, including the larger ones and ones most heavily used, have clustered indexes on the primary key columns, so I should be good in that respect.

I've read in a few places that the decision to rebuild vs reorganize should be based on level of fragmentation, but I guess the rebuild vs reorganize decision will have to be based on whether or not I think I'll have enough space to rebuild.

--------------------------------------------------------------

"Daterbase, taterbase, gatorbase."

Post #1403785

Posted Monday, January 07, 2013 11:47 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

There can be a couple of reasons to rebuild vs reorganize, but you're right that the most usual reason is fragmentation level.

Rebuilding also rebuilds stats, which is often a very good thing. I have a number of tables that I default to rebuild so that stats stay fresh on them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1403789

« Prev Topic | Next Topic »

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse