Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

We have deleted a large part of the data from a two-digit terabyte sized database, and would now like to release some of the space back to the operating system. The database is currently about 80 % unused space. It will never reach its former size again (the load has been transferred elsewhere).

We have tried running SHRINKDATABASE on a copy of the database on a separate server (non-production). Running for 2 days (on our hardware) it was able to release the space.

I'm assuming it will take much longer on the database in production, as this is heavily used (mostly for querying now though, less so inserts).

Only hours of downtime (at a time) is acceptable on the production system. Some performance degrade over extended periods would be acceptable too.

Any ideas on how to release this amount of unused space, without having excessive downtime, or introduce severe performance degrade during the process?

share|improve this question

marked as duplicate by Mike Walsh, Max Vernon, Mark Storey-Smith, billinkc, Paul White Nov 24 '13 at 3:55

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

Just deleted my previous answer as I started a proposed canonical question for this type of question. In case that flagging doesn't work, a short answer for you here.

You have a couple options. Big "Warning" here is - Shrinking isn't always a good thing, it fragments your indexes and takes some time. So proceed cautiously.

  1. You could shrink it in smaller "chunks" using DBCC SHRINKFILE. Pros: No development really required, potentially less downtime than migration. Cons: You are still shrinking a database, you still have to deal with fragmented indexes, but you should do some index rebuilds anyway because you've removed a lot of data.
  2. You could build a new database and select data into it. Pros: You aren't shrinking. You are starting "fresh" and clean. You can use this as a time for other cleanup potentially. Cons: Probably more effort than just shrinking in chunks, may require a larger down window depending on how complex the environment is. Potential risk for missing something in the scripting.
  3. If you chose the shrink route, remember to rebuild your indexes and deal with the resulting fragmentation.
share|improve this answer
1  
Excellent, thanks. Think we going to start out with option 1 and then if we get tired of waiting go to option 2 :-) –  user3021123 Nov 26 '13 at 15:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.