Shrinking Databases

If there is unused space, it will be already used “to insert and update some new data”, and if there isn’t doing shrink will change nothing: shrink does not delete your data, all it does is moving data at the beginning of the file to make space at the end to give it back to OS.

Shrinking data file can be useful when you had a data file of 2Tb, and 1 Tb of data was deleted and you don’t plan to insert another Tb of data in next 10 years.

You can imagine your data file as a box 1m x 1m x 1m. If you have only a half of the box full of toys, even if you don’t use shrink you can put other toys into this box (make insert/update). What instead shrink does, it gathers all the toys in 1 corner and then cut your box to make it 50cm x 50cm x 50cm. This way your room (OS) now has more free space because your toy box takes only the half of space it took prior to shrink.

…And if your box was already full, you cannot add more toys even if you try to do shrink.

if the log is full, shrinking log should not be a problem, right?

Shrinking log is another process, nothing can be moved inside log file, in this sense of course shrink cannot make much harm as in the case of data file: it does not require server resources, it does not cause any fragmentation, etc. But if it succeeds or not depends on the cause of your “log is full”.

If your log is full due to full model, shrinking log file will not change anything: the log is retained to give you the possibility of having the log backup chain (or to make possible mirroring, or log shipping, etc).

If instead your database recovery model is simple, and there was some trouble with a transaction that was open for long period of time, or there was a huge data loading (maybe with full logging such as insert into without tab lock) and your log file became bigger than data file, and you found and fixed the problem and you don’t need such a huge log file, yes you can shrink it to a reasonable size, and it’s not harmful.

-- Data file: shrink only after a large, permanent deletion.
-- The target size is in MB. Leave headroom so the file doesn't grow straight back,
-- and rebuild fragmented indexes afterwards (shrinking fragments them).
USE [database]
GO
DBCC SHRINKFILE (N'BillSum', 51200)   -- example: 50 GB, never 1 MB
GO

From my SQL Server DBA handbook.