Safely removing SQL DB whitespace

Domo · 14 May 2018 at 15:44

We have an SQL DB which is 500GBs but of that only 300GBs is data. In order to port it to a new host we have to reduce the size.

1. Will a backup and restore get rid of the whitespace (I'm assuming it won't)
2. If not, how can I safely reduce the DB size - i.e. what steps to I need to perform after shrinking? Most (if not all) articles mention the risks of fragmentation.
3. Can any of you recommend a process to do this which is safest?

Thanks.

Domo · 14 May 2018 at 15:53

There is a command. Casting my mind back, shrinking t-logs via DBCC carried no risk - but I'm concerned with the impact shrinking the datafile might have. RE platform it's SQL.

Domo · 14 May 2018 at 15:55

I think in the old days SQL did automatically shrink during the backup and restore process. Not anymore. SQL holds onto the space it grows into. The current situation suggests growth properties for said DB are wrong (probably set to auto).

Domo · 14 May 2018 at 17:01

We also run maintenance plans out of SSMS. Still need to shrink down this DB file though. This thread (and I'm not being critical of you guys

) is typical of content I've been reading online. It's like there's no clear answer or I'm framing the question incorrectly - again

.

To cut to the chase, what harm can or will dbcc shrink database do and will it even do what I'm expecting it to? I.e. reduce the DB size from 500GBs to 300GBs.