08-27-2019 05:12 AM
I have NBU 8.0 installed. I configured SQL backup for SQL 2008 DBs. I have enabled log truncation in NBU policy. Now as far as I know/see, log truncation does not reduce the log file size on disk as this has to be done from SQL via shrink operation. However, I noticed that after log truncation, it size on disk decreases each time. I checked the SQL configuration and no maintenance plans are active and nobody shrank the DB. Finally, I checked the DB properties and auto-shrink was enabled for it.
Does the shrinking operation affect in any way the transactions in the .ldf file? What I mean is, I have configured NBU to take transaction log backups every 1 hour and auto-truncate them. Let's say that some new transactions came in for a few minutes after backup and then the shrinkingoperation kicked in (before next backup). Will these new logs be deleted/affected in any way after shrinking? I'm asking for restore purposes as I don't want any missing logs.
My question is: can we keep the auto-shrink enabled for the DB and take transaction logss backups and auto-truncate? Is it best practise to keep the auto-shrink enabled?
I hope you were able to understand what I am saying.
Have a nice day!
08-27-2019 05:35 AM
MS SQL log shrink operation does not remove not-backed-up transactions in it. They must be first backed up by something (backup sw vendor or native MS SQL tools) and then corresponding space in .ldf file can be shrinked.
Of course you must use Full Recovery model in your DB - you probably use because hovewer without it you would get an error when attempting trx log backup.