13

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.

gbn
  • 69,809
  • 8
  • 163
  • 243
Jeffrey Bane
  • 389
  • 2
  • 6

4 Answers4

15

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.

Jeffrey Bane
  • 389
  • 2
  • 6
  • 1
    This wizard is just awful. – usr Jul 08 '12 at 19:48
  • I know, I'm so used to command line where you specify FILL_FACTOR and not free %, wish it were consistent. –  Jul 08 '12 at 21:32
  • Don't reindex then shrink, it's just a waste of time! See my answer for more info. – JNK Jul 09 '12 at 13:43
  • 1
    JNK I know what you mean with don't shrink after a rebuild since that will fragment everything again. However, in this particular scenario where Jeff by accident added 90% free space to every page, I don't see any other way to reclaim the space then to: rebuild with fillfactor 90, shrink file, however you would have to do another rebuild with fillfactor 90% again. Or would there be another way to reclaim the space back. (Well maybe a new filegroup and then rebuild with drop onto the new filegroup but that doesn't work for everybody) – Edward Dortland Aug 29 '12 at 16:02
2

Rebuilding indexes causes extra slack space in the DB. This is a natural by-product of the reindexing process - the server is building a new, hopefully contiguous version of the index alongside the current version, then drops the current version when complete.

Shrinking after reindexing is pointless!

You will just re-fragment the index! Shrinking removes the slack space by reallocating data within the DB.

Here's a nice article from Paul Randal, who when working at Microsoft was in charge of the DBCC code including shrinks, on why you shouldn't shrink.

JNK
  • 17,956
  • 5
  • 59
  • 97
0

You should have used the rebuild option SORT_IN_TEMPDB=ON.

In your case the actual data file(s), where the tables in question are located, have been used for sorting the indexes. Much of that 120GB space is still unused and will be filled with page data when needed.

You could see the used/free space status with this query (taken from here):

use [YourDatabaseNameHere]
go

select
    name,
    cast((size/128.0) as int) as TotalSpaceInMB,
    cast((cast(fileproperty(name, 'SpaceUsed') as int)/128.0) as int) as UsedSpaceInMB,
    cast((size/128.0 - cast(fileproperty(name, 'SpaceUsed') AS int)/128.0) as int) as FreeSpaceInMB
from
    sys.database_files

For shrinking a specific database file (data or log), you can see some info here. A warning before that, releasing unused space is taking quite a while for 100+ Gb databases (depends also on the storage speed), so just let it run.

Marcel N.
  • 188
  • 1
  • 8
  • YEah it wont' format, I'll add it to the question, one moment. –  Jul 08 '12 at 16:54
  • @JeffBane: Can you add this info to your question, in a quote? I can't make out what's in there. – Marcel N. Jul 08 '12 at 16:55
  • 1
    When rebuilding an index you would need twice the space of the index + 20% for the sorting. So in general to rebuild every index in your db you only need 120% of your biggest index in your DB. If you use SORT_IN_TEMPDB, you only win 20%, you still need an aditional 100% in your data file. Further more, using sort in tempdb increases your IO load drastically, since instead of Writing the index one time to the datafile, you now write it one time to the tempdb and then write it to the data file. So that is not always ideal. – Edward Dortland Aug 29 '12 at 16:11
-1

Without any other details I suspect you need to do a transaction log backup before you can reclaim its space.

See what select name, log_reuse_wait_desc from sys.databases tells you. If the log_reuse_wait_desc column says LOG_BACKUP then it can't reclaim space until you've done a backup of the tran log.

dpw
  • 217
  • 1
  • 5