Best Practice for full backup and transaction log truncation

Question

Best Practice for full backup and transaction log truncation

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

deubel_m SSC-Addicted Points: 465 More actions · Answer 1

anbu678@gmail.com wrote:

I would like to understand how to create auto job to take the full backup, transaction backup + truncating the log files(without making the database to simple recovery mode and truncating)

If your backup works well there is no need to truncate logfiles anyway.

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Dummy Reply to force page 2 to display. This "Post 16" bug is STILL not fixed. Going on 2 years. Sigh....

This reply was modified 8 months, 3 weeks ago by homebrew01.
This reply was modified 8 months, 3 weeks ago by homebrew01.
This reply was modified 8 months, 3 weeks ago by homebrew01.
This reply was modified 8 months, 3 weeks ago by homebrew01.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

While not clear, it seems as though you have a database in FULL recovery which has not had a log back in 3 or 4 years. The log file must be enormous! As you obviously do not understand how SQL Server works, I suggest you try the following on a test system first:

Set the database to SIMPLE recovery.
Shrink the log file.
Set the log file to a sensible size.
Make the log growth setting sensible.
Set the database to FULL recovery
Do a FULL backup of the database.
Start doing log backups at a sensible interval which is normally every 5 minutes up to every hour. This will not shrink the log file but it will allow the space to be re-used so ideally the log file should not grow.

Details on sensible log file settings can be found here:

https://www.sqlshack.com/sql-server-transaction-log-administration-best-practices/

I would be inclined to automate the backups with Ola Hallengren's Maintenance Solution

This reply was modified 8 months, 3 weeks ago by Ken McKelvey.