0

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?

2

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/

1

After you change the recovery model to SIMPLE, perform a full backup, that should truncate your tlog. From MSDN:

After Switching to the Simple Recovery Model If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, we strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.