June 25, 2014 at 8:28 am
I have a Database that is 642 GB's. It has 375 GB after a purge of a large number of records.
I think that releasing some of the free space would be warranted.
I also need to rebuild the Indexes and change the fill factor on most of the tables.
What options should I choose from the GUI or T-SQL?
Release unused space, Reorganize pages before releasing unused space and shrink file to ???
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 25, 2014 at 2:41 pm
I'd rebuild the indexes before I did a shrink as you might shrink below what you need for the rebuild and then the rebuild will take longer due to a growth operation.
I'd also look at what my anticipated growth will be and only shrink down to a size that leaves as least a year's worth of growth.
If you don't have issues with lack of space on the storage subsystem I wouldn't even bother to shrink it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 25, 2014 at 2:58 pm
Jack Corbett (6/25/2014)
I'd rebuild the indexes before I did a shrink as you might shrink below what you need for the rebuild and then the rebuild will take longer due to a growth operation.I'd also look at what my anticipated growth will be and only shrink down to a size that leaves as least a year's worth of growth.
If you don't have issues with lack of space on the storage subsystem I wouldn't even bother to shrink it.
Thank you!:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 25, 2014 at 3:10 pm
Jack Corbett (6/25/2014)
I'd rebuild the indexes before I did a shrink as you might shrink below what you need for the rebuild and then the rebuild will take longer due to a growth operation.I'd also look at what my anticipated growth will be and only shrink down to a size that leaves as least a year's worth of growth.
If you don't have issues with lack of space on the storage subsystem I wouldn't even bother to shrink it.
If you run a shrink after you rebuild the indexes, it will more than likely severely fragment the indexes and another round of rebuild or defrag will be needed.
June 25, 2014 at 3:13 pm
Michael Valentine Jones (6/25/2014)
Jack Corbett (6/25/2014)
I'd rebuild the indexes before I did a shrink as you might shrink below what you need for the rebuild and then the rebuild will take longer due to a growth operation.I'd also look at what my anticipated growth will be and only shrink down to a size that leaves as least a year's worth of growth.
If you don't have issues with lack of space on the storage subsystem I wouldn't even bother to shrink it.
If you run a shrink after you rebuild the indexes, it will more than likely severely fragment the indexes and another round of rebuild or defrag will be needed.
Not likely. It will severely fragment those indexes. Just plan on it.
Shrink leaving 2x your largest table as free space if you must shrink.
Otherwise don't shrink. The database will likely grow into it's new found space.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply