What is the best way to shrink and backup our db logs?
I have seen many DBAs that do it :
ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>)
ALTER DATABASE [mydatabase] SET RECOVERY FULL
But I wonder, is this the best way?
Am I affecting my database by losing data when changing the status of the recovery log?
Best Answer
The best way is not to shrink it at all.
You are not losing any data when you shrink the log or when switching recovery models. You are losing recovery points though.
But there's a reason the file was sized that way.
Backup your transaction logs frequently, such as every minute, to minimize its size. If the size is still bigger than what you'd think, LEAVE IT ALONE or figure out which are your large transactions. Those transactions might be able to be broken up, though it is likely an index rebuild that can't be broken up.
To answer your question, I just run my backup log job and then use DBCC SHRINKFILE. I may have to free the procedure cache in tempdb though: https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/