we all know that by default, the auto-shrink feature of all user database in MS SQL server is disabled and what I knew so far is, no matter what this value is, when we delete/archive data out from user database, the .mdf file size will be smaller no matter this feature on or off, am I right?
how is your option on this as previous experience this, .mdf file size will be smaller whenever large amount of data has been deleted.
ALSO, we do not shrink datafile as it can create >90% of index fragmentation, right? only log can be shrink by release the tail of inactive transaction log, right?
please help to clarify that and how can we make sure that auto-shrink option is really on ? e.g., once enable this option to yes from per user database option, should we do a full backup for it to take effect?