Release unused space from SQL Server database [duplicate]

Question

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?

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.