November 25, 2013 at 8:46 am
I have SQL Server 2000 database, ran index rebuild job over the weekend, but the data file increase after the index rebuild. How can I regain the space? Thank you!
November 25, 2013 at 8:51 am
Grace09 (11/25/2013)
I have SQL Server 2000 database, ran index rebuild job over the weekend, but the data file increase after the index rebuild. How can I regain the space? Thank you!
i'd expect to see the log file increase in size due to the rebuild.
if the data went up in size, to me that kind of implies that someone used a fill factor on the index rebuilds that had not been there before, does that sound posible?
if you run exec sp_spaceused in the database in question, what are your specific results?
for example, here's the results of mine:
database_name database_size unallocated space
--------------- ------------------ ------------------
MyDatabaseName 44540.50 MB 7463.08 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
33536048 KB 15968088 KB 17526760 KB 41200 KB
so, based on unallocated space, i could try to get back 7.4 gig or so, but in my case, i don't want to do that, that space , for me, is fine for future growth, and i'd leave it alone.
Lowell
November 25, 2013 at 9:35 am
Normal, fully expected. SQL needs space to put the new index. A rebuild creates the new index then drops the old. So if there wasn't space in the data file for the new index to be put down, the file would have to grow.
As for reclaiming, why? The file's just going to grow again if you do, and shrinking will fragment all your indexes, wasting all the time spent rebuilding them. Free space in a data file is a good thing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply