Post reply

Reclaim unused disc space

  • December 12, 2019 at 10:38 am

    #3707305

    Hi

    I have a database that by mistake grew to almost 1 Tb! We located the error and cleaned out the records not needed/faulty created. This leaves us with 50% unused space. I would like to reclaim as at least 300 Gb since we will never hit the roof again, that has been secured.

    I have read many forums and articles about this but haven't found a solution yet, so please help.

    AND please don't ask why, and if and so ! If there was another way I wouldn't raise the question, so I would like a straight answer ho to do this.

    Thanks in advance.

  • December 12, 2019 at 11:11 am

    #3707308

    You might want to try a different search engine!  There's plenty of information out there.  Here's what you need to do.

    John

  • December 12, 2019 at 11:38 am

    #3707309

    Hi John

    Not the right direction ! Everywhere I read people a screaming ..... DO NOT USE SHRINK on a datafile ! Pay attention, it is not the transactionlog I'm trying to free up space from.

    I'm looking for options like

    - back up -> restore to a new db/datafile or something in that way

    - Add a new data file and copy over data and delete the primary (but not sure that is possible)

    I'm looking for someone who did this and can tell how to !

  • December 12, 2019 at 11:45 am

    #3707310

    Pay attention,  I never mentioned the transaction log.  And just because people scream, or write in capitals, it doesn't mean they're right.   There's absolutely nothing wrong with shrinking as a one-off operation.  Make sure you leave some free space and rebuild your indexes when you're done, though.

    Now, if you don't want to accept what I say (and it's perfectly OK for you not to) then feel free to try one of the other things you've suggested.

    John

  • December 12, 2019 at 1:05 pm

    #3707342

    Hi

    I did indeed read through these as well, but I must admit, that it's not the way I would try to go. Mostly because of the time it takes (many hours) and with a risk for ending up in an error situation.

    So I would like to hear from others who actually did manage this in another way than using shrink with a bit of how-to.

  • December 12, 2019 at 1:34 pm

    #3707368

    There's no need to have such an unpleasant attitude. People on this forum are generally friendly and helpful. You'll get the best responses if you are too.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • December 12, 2019 at 1:43 pm

    #3707372

    Sorry if my attitude was misunderstood, communication is a difficult thing 🙂

    I did not mean to put up an attitude, but yes I'm frustrated. If you read through all kinds of postings across many forums and experts, then they all recommend not to use the shrink operation. I even read the article from a former employee who actually worked within that area who also recommend not to use it!

    So I'm actually looking for someone who actually solved a similar problem and who was able to tell what he/she did and in which order and the final result.

    I do apologize for the misunderstanding.

     

  • December 12, 2019 at 1:51 pm

    #3707376

    Good response. I'd help if I could, but I'm just a lowly developer 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • December 12, 2019 at 1:51 pm

    #3707377

    If you are worried about shrinking the datafile then the standard advice is to create a new datafile and migrate the data into it.

  • December 12, 2019 at 2:00 pm

    #3707381

    Hi Jonathan

    Ok, and can the original datafile then be deleted ?

    Have You tried this ?

  • December 12, 2019 at 2:03 pm

    #3707393

    Empty file by migrating the data to other files in the same filegroup

    Migrate all data from the specified file. This option allows the file to be dropped using the ALTER DATABASE statement. This option is equivalent to executing DBCC SHRINKFILE with the EMPTYFILE option.

    https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-ver15

    If you don't want a new datafile then you could migrate all the data back again to the original file and then delete the new file.

    Or just shrink the file and rebuild all the indexes afterwards.

     

  • December 12, 2019 at 3:13 pm

    #3707425

    One trick I've seen if you don't want to do the entire shrink in one operation is to do the shrink steps in a loop and shrink the size by some number of MB every step until you get down to what you want.

     

    Then do the index rebuilds.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply