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 12»»

Need to get some disk space back from a table, need to shrink... Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 9:54 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 728, Visits: 5,231
OK, I know all the reasons I shouldn't be shrinking a database, but there's really no other option. Also, this is a one-time only shrink! Recently I had to add a clustered index to a Heap of about 1/2 billion rows. Problem being, there wasn't enough disk space to just add the CI. BUT there was enough to "duplicate" the table into a new table with the CI already defined. Once this process completed, we truncated and dropped the old table.

This freed up about 80GB within the DB file. The problem is, we now only have about 8GB free on the disk, and the SAN admin was complaining the other day about the storage being used by the various servers. So I can't request more storage for this VM...

I've read the articles on why shrinking is a bad idea (the fragmentation that *will* result,) and I want to both try to minimize the index rebuild, and the log usage.

So my thinking is to shrink the file by a couple GB at a time (maybe 10GB,) then do an index rebuild, then repeat. Possibly if the shrink at that size doesn't take too long, I'll do several in a row. The DB is in Simple recovery, so this should also keep the log from exploding. I can work on this on the weekend without worrying about the users.

I'm going to leave some free space in the file to avoid growth later, and the DB doesn't grow all that much (it seems so far they add X rows and delete Y rows, and X almost equals Y)

Does this sound like it should work?

Thanks,
Jason
Post #1450225
Posted Tuesday, May 7, 2013 10:09 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: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502
It would be best to do a shrink to the target size that you want to get the database file to, and then run a reindex or reorganize on all tables.

For the larger tables, you will probably want to run an index reorganize to prevent an reindex from expanding the data file again.

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



Post #1450230
Posted Tuesday, May 7, 2013 10:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 17,723, Visits: 15,587
Another option is to find the largest table in the database, multiply it by 1.5. Once you have that number, shrink the file to leave at least that amount of free space in the data file.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1450234
Posted Tuesday, May 7, 2013 10:16 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 728, Visits: 5,231
SQLRNNR (5/7/2013)
Another option is to find the largest table in the database, multiply it by 1.5. Once you have that number, shrink the file to leave at least that amount of free space in the data file.


Which sounds like a nice round number to me. Doesn't free up quite as much as I'd like, but still frees up about 30GB
Post #1450236
Posted Tuesday, May 7, 2013 10:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 17,723, Visits: 15,587
jasona.work (5/7/2013)
SQLRNNR (5/7/2013)
Another option is to find the largest table in the database, multiply it by 1.5. Once you have that number, shrink the file to leave at least that amount of free space in the data file.


Which sounds like a nice round number to me. Doesn't free up quite as much as I'd like, but still frees up about 30GB


I should probably have included why to do that in my post.

The reason is to avoid the multiple IO penalties to grow/shrink the file. If you leave that kind of free space, it permits the reindex operation to function without (hopefully) causing the database to grow.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1450238
Posted Tuesday, May 7, 2013 10:23 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 728, Visits: 5,231
So just one big shrink operation, rebuild the CI and NCIs, and done, correct?
Post #1450240
Posted Tuesday, May 7, 2013 10:24 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 17,723, Visits: 15,587
That would be correct. After the shrink, you will need to rebuild due to the fragmentation the shrink will cause.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1450241
Posted Tuesday, May 7, 2013 10:34 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 728, Visits: 5,231
Gotcha.

If it weren't for the disk space, I wouldn't be bothering with this, but...

Come to find out, part of the reason this wasn't an issue with the previous table is it was page-compressed...
Kicking around the idea of doing this to the new table, but my concern after reading up on it on MSDN is it will essentially (again) duplicate the table while it compresses it. Which will put me right back here again...

But, sp_estimate_data_compression_savings estimates it will cut the size down to 1/3 of what it is now, so even if it only halves it, that's still a lot of disk back...

Thanks again!
Jason
Post #1450245
Posted Tuesday, May 7, 2013 11:29 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 728, Visits: 5,231
Well, thanks for the suggestions, but continued research, come to find out the other DBs on the server live on another drive entirely.

Plus I freed up ~40GB by deleting my backup file from the weekend when I started trying to add the Clustered Index.

So, no shrinking, no compression for me!
Post #1450267
Posted Tuesday, May 7, 2013 11:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 17,723, Visits: 15,587
That's always a nice surprise



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1450271
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse