Correct process for reducing SQL Log file size

0
votes

SQL 2008 R2. Full back-up every night with replication to a 2nd server for reporting. Business critical multiple databases. SQL Log files in danger of exceeding available disk space

Shrinkfile did not seem effective. Created backup of log file and then chose GUI option to shrink log file. Having done this log file size is much more manageable. When is it safe to delete the Log file backup, or is it never?

1
sql-serverlogfile
Are you referring to the transaction log? - a_horse_with_no_name
@a_horse_with_no_name he seem to be mixing both questions, backups and transaction log files. - Ricardo C
I regret that I was not clear enough originally. There are nightly backups of the databases and then much more frequent backups of the Log Files throughout the day. Having successfully (I think) reduced the Log File size (it does not seem to have grown again), when can I delete the Log File backup that was taken? Is it redundant once a new full database backup has been taken and will the new smaller Log File backup be sufficient without keeping the full backup of the Log File that I had to take to get it to shrink. Hope I have made my question (and lack of understanding) clearer? - CVee

1 Answers

0
votes

You need to keep the transaction log backups for the full backups that you will potentially use to recover the database. E.g. if you keep the last 7 full backups, you just need to keep the log backups for the last week so you can recover the db to any point of time in the week if something is wrong.