October 4, 2010 at 8:17 pm
Couple of questions. What is the fill factor on the index(es)? How big is each index (in the case of the clustered index, each row)?
October 4, 2010 at 11:15 pm
This is sp_spaceused for the biggest table:
name, rows, reserved, data, index_size, unused
mytable, 38380502 ,80544904 KB,76807032 KB,3710032 KB,27840 KB
The fill factor on the indexes is 90%.
October 5, 2010 at 1:36 am
Doing a REBUILD (or recreate) of a clustered index always uses up a lot of space.
One way around this is to REORGANIZE it instead, this does not increase the data file (but can take longer and not be as efficient).
If your clustered index is heavily fragmented you can do a REBUILD (or a drop/create, same thing happen), shrink the data file (which will fragment your index a bit) and then do a REORGANIZE to fix the fragmentation.
It might be quicker to go right away with the REORGANIZE though, just test both.
October 5, 2010 at 6:48 am
1) reorg might work, but you would need to do it from the point of having minimal size files (with a bit of free space) and indexes in place. That means your indexes will be fragmented to hell because you had to have just done a shrink. That means reorg will take a kajillion hours to run and create a tlog big enough to put the library of congress in. Probably not viable solution.
2) What stands a better chance of doing what you want is to move the data OFF of the filegroup altogether (so it is completely empty). Then put the data back in it with proper clustered index build. Then create NC indexes.
3) I gotta say it is a darn shame in this day and age that you are fretting over a paltry 50GB of disk space!! :blink:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 5, 2010 at 5:13 pm
TheSQLGuru (10/5/2010)
1) reorg might work, but you would need to do it from the point of having minimal size files (with a bit of free space) and indexes in place. That means your indexes will be fragmented to hell because you had to have just done a shrink. That means reorg will take a kajillion hours to run and create a tlog big enough to put the library of congress in. Probably not viable solution.2) What stands a better chance of doing what you want is to move the data OFF of the filegroup altogether (so it is completely empty). Then put the data back in it with proper clustered index build. Then create NC indexes.
3) I gotta say it is a darn shame in this day and age that you are fretting over a paltry 50GB of disk space!! :blink:
This is probably the best chance of success at this point. The file should only grow to the size that is needed as you move the data back into the filegroup. It should be done via filegroups and not just files.
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 5, 2010 at 5:24 pm
TheSQLGuru (10/5/2010)
3) I gotta say it is a darn shame in this day and age that you are fretting over a paltry 50GB of disk space!! :blink:
Wish I could argue this point, but the truth is 50 gigs can be worth an arm and a leg if the SAN is overloaded and to get more space, they need a new SAN, which needs approvals, which needs god himself to give the company more money because the CEO is NOT going to be missing his Wednesday Tee Fees.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2010 at 5:40 pm
TheSQLGuru (10/5/2010)
3) I gotta say it is a darn shame in this day and age that you are fretting over a paltry 50GB of disk space!! :blink:
$320 per 50GB per month x 12 months = $3840 a year
TheSQLGuru (10/5/2010)
2) What stands a better chance of doing what you want is to move the data OFF of the filegroup altogether (so it is completely empty). Then put the data back in it with proper clustered index build. Then create NC indexes.
When I was partitioning the data it took the whole Sunday from 7am till 10pm to put the data into the filegroups, which stretched beyond the maintenance window I have. If I have to move the data back and forth it will take even longer. Besides, the files got bigger then they should be as a result of the partitioning. Might just have to abandon the idea.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply