Pros and cons of DBCC SHRINKDATABASE operation I have read MSDN - Microsoft Manual according to this issue, but I still do not have clear understanding, when it is useful to apply this option and when it is not. Please advise.
As you know DBCC SHRINKDATABASE attempts to shrink both data and log files.

Personally, I would probably never use SHRINKDATABASE. Instead I'd use DBCC SHRINKFILE, and even then would only use it on datafiles to resize them correctly, or on log files if I was trying to shrink them back in order to grow them out again to the correct number of virtual log files (VLFs). The other use case for datafiles (if you had multiple) would be if you were looking to remove them; you would first need to shrink with EMPTYFILE.

In a properly configured database, you should never need to use either SHRINKDATABASE or SHRINKFILE. You should have pre-allocated and sized your log and data files correctly, and set caps to avoid files growing out of control. Prevent the log file from needing to grow in the first place through correct sizing (to accommodate the longest possible running open transaction) and regular log backups.

For debate on VLF sizing please check out the SQLSkills article:

Transaction Log VLFs – too many or too few?