September 10, 2010 at 7:03 pm
Hi,
I've got a partitioned database and every time I do the index rebuild, I get the files to increase in size with a lot of free space after the rebuild is finished. If I shrink the files, they grow again after another index rebuild. For example a 15GB file grows to 21GB. I even tried to use SORT_IN_TEMPDB option and rebuild it partition by partition, but no luck:
ALTER INDEX [MyIndex] ON [MyDB].[dbo].[MyTable]
REBUILD PARTITION = 1 WITH (SORT_IN_TEMPDB = ON)
ALTER INDEX [RCX_position_detail_PID] ON [MyDB].[dbo].[MyTable]
REBUILD PARTITION = 2 WITH (SORT_IN_TEMPDB = ON)
ALTER INDEX [RCX_position_detail_PID] ON [MyDB].[dbo].[MyTable]
REBUILD PARTITION = 3 WITH (SORT_IN_TEMPDB = ON)
ALTER INDEX [RCX_position_detail_PID] ON [MyDB].[dbo].[MyTable]
REBUILD PARTITION = 4 WITH (SORT_IN_TEMPDB = ON)
ALTER INDEX [RCX_position_detail_PID] ON [MyDB].[dbo].[MyTable]
REBUILD PARTITION = 5 WITH (SORT_IN_TEMPDB = ON)
ALTER INDEX [RCX_position_detail_PID] ON [MyDB].[dbo].[MyTable]
REBUILD PARTITION = 6 WITH (SORT_IN_TEMPDB = ON)
ALTER INDEX [RCX_position_detail_PID] ON [MyDB].[dbo].[MyTable]
REBUILD PARTITION = 7 WITH (SORT_IN_TEMPDB = ON)
GO
Is there any way to recover that free space and keep those files small?
Thanks.
September 10, 2010 at 7:16 pm
I recommend not shrinking it like that. Shrinking after you reindex is causing your indexes to fragment again.
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
September 10, 2010 at 7:24 pm
How do I recover the space then? The older partitions do not change, so I need to shrink them and then rebuild the indexes without blowing out the files again. I've got about 50GB to recover.
September 10, 2010 at 7:33 pm
If the older partitions are not changing, then why rebuild those indexes?
If the data is changing and the pages are getting split, then an index will become fragmented and will need to be rebuilt.
If you aren't changing data in a partition that has a partitioned index on it - then you should be fine to not rebuild it on a regular basis. Monitor the indexes and determine if they are getting frag'd.
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
September 10, 2010 at 7:44 pm
Ok, I've got over 50GB of space in those older files, which I need to recover. If I shrink the files, I willl fragment the indexes. If I fragment the indexes, I have to defragment them again. If I defragment the indexes, I will blow out the files, and need to shrink them. If I shrink the files...
:hehe:
September 10, 2010 at 7:47 pm
And the circle goes on.
Unless you are really hurting for disk space, I would not shrink them.
If you are really hurting - you can try to shrink them to about 50% of the free space in those particular files. You may still end up with some fragmentation.
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
September 10, 2010 at 7:54 pm
So there is no way to rebuild the indexes without blowing out the files? What that SORT_IN_TEMPDB option is about then? Isn't it supposed to prevent the file blowout by using TEMPDB?
🙁
September 10, 2010 at 8:00 pm
Roust_m (9/10/2010)
So there is no way to rebuild the indexes without blowing out the files? What that SORT_IN_TEMPDB option is about then? Isn't it supposed to prevent the file blowout by using TEMPDB?🙁
Nope, all that does is move the sorting of the data for the index from the data file to tempdb. There is some additional transaction log usage that is moved to tempdb, but the new index still has to be built in the data file.
You could try using the truncateonly option of SHRINKFILE. If there is space at the end of the file, that will get released - but, no pages are moved so nothing would get fragmented. This might not recover any space.
If you are that tight on space, then you need to get more space. The solution is not to shrink your data files and log files, but to expand the disk to accomodate the business requirements (growth) of the system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 10, 2010 at 8:04 pm
Eureka! I should just drop the indexes and re-create them and then only re-index the current partition!
😛
September 10, 2010 at 8:12 pm
Roust_m (9/10/2010)
Eureka! I should just drop the indexes and re-create them and then only re-index the current partition!😛
That might work - but, be aware that during that operation those tables will suffer performance issues. And, you want to make sure you do that in the appropriate order. Drop all non-clustered indexes first, then drop the clustered index - recreate the clustered index, then recreate the non-clustered indexes. If you drop the clustered index first - all non-clustered indexes have to be rebuilt. And then, when you create the clustered index again - all non-clustered indexes would have to be rebuilt again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 10, 2010 at 8:18 pm
Thats fine, this is a one off operation anyway. So the plan is:
1. Shrink the files to just over the minimum size.
2. Drop non-clustered indexes
3. Drop clustered indexes
4. Re-create clustered indexes
5. Recreated non-clustered indexes
6. Put reindexing in place which only rebuilds the most recent partition
September 10, 2010 at 9:21 pm
Looks like a viable option - for a one off situation.
Good luck.
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
October 4, 2010 at 6:59 pm
No, it did not work, on step 4 the files grow again for some reason. 🙁
October 4, 2010 at 7:02 pm
Did you trim the files all the way down to no free space, or did you leave some room in them?
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
October 4, 2010 at 7:25 pm
I left few gigs in the files, besides, after dropping all indexes, there were even more space, but when I recreated the first clustered index on the biggest table, the files blew out again even when using "sort_in_tempdb" option.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply