Sql-server – Rebuilding Indexes, DB now 10x the size

sql serversql-server-2008sql-server-2008-r2

I have a SQL Server database (2008 R2 SP1) that was about 15 gigs. It turns out maintenance hadn't been running in a while, so I created a maintenance plan to rebuild all the indexes, they were very fragmented.

The job finished and the fragmentation is gone, but now the database is over 120 gigs! I understand that it would have used extra space to do all the rebuilds, but now that the job is done, I would think all that space would be free space, but the free space only shows as 3 gigs, so 117 gigs is being used even though the index rebuild job is finished.

I'm very confused and could use some guidance, I have the get the db back to a reasonable size, we don't have the disk space for this.

Thanks in advance!

Here's the results of both the queries posted:

log_reuse_wait_desc NOTHING

name    TotalSpaceInMB  UsedSpaceInMB   FreeSpaceInMB
LIVE_Data   152             123             28
LIVE_Log    18939           89              18849
LIVE_1_Data 114977          111289          3688

The 3rd file is an .ndf file, that's the one showing only 3688 in unused space, but 111289 in used for about 15 gigs of data.

Best Answer

In the meantime I just figured this out, total brain burp. I had indicated what I thought was a fill factor of 90 in the rebuild job, but it's worded as "change free space percentage to" so by using a value of 90 in there, I was actually using a fill factor of 10!! DOH. No wonder it grew 10x as big. I'm going to rebuild with the correct fill factor then shrink. Thanks to everyone for the input though.

Related Question