Cannot Reindex.

[Credo]

Hello all,

I'm new to this forum, so please be gentle! :P

I've got a bit of an issue with an SQL server. I've got a 113Gb database, and I'm running out of disk space. Only 5.6Gb free. We have a new server on order, but I'm trying to save space here.

From what I gathered, whoever set up this database initialy left the growth to the default 10% - well, when the DB's 115Gb, that's another 15Gb of unnecessarily large page space every time the DB expands....

I've got most of that solved, and I've started backing up the transaction logs on a regular basis on a network share - so I'm able to some shrinking of the LOG file.

My problem is the re-indexing...

One particular table is the bulk of the database at just over 93Gb in size (37Gb data, 55Gb index). I would really like to re-index this particular table because of it's size, and I bet this thing is so fragmented it's not even funny, as any sort of re-indexing hasn't been done before. But because I've only got about 5 -6 gb left on the HDD, I get the "log file too big" error message, and can't continue. I've even tried it when I had about 13Gb available, and still got the same error message.

I've even set recovery to BULK_LOGGED, but still doesn't work. Anyone have any suggestions on how I can accomplish a proper re-index (as I'm hoping this might also free up some space...)

Thanks in advance for your help!
~C.

[ck9663]

Try deleting some of the tables that you don't need then shrink the database. See if you have enough space to cover your index.

--- CK

[Credo]

Thanks for the reply, but I've already cleaned the table as much as I can. As of now, I'm backing up the transaction logs, then I can shrink the existing log file back down to it's original size - but that has to be done several times a day just to get the indexdefrag completed.

...This sucks! :(