Two methods to Shrink the log file - why change Recovery Model?

Question

Post reply

Two methods to Shrink the log file - why change Recovery Model?

HookSQLDBA7

SSChampion

Points: 10709
More actions
August 26, 2019 at 11:57 pm

#3674552
We are running SQL Server 2012 SP4 in FULL Recovery Model with a weekly full backup, nightly differential backups, and 10 minute log backups. Very rarely, I have to truncate our production log file. When I restore a production backup to a non-production environment, I sometimes truncate and shrink the log file. I noticed there are two ways I can shrink the log files:
1. Execute these three statements and then take a FULL Backup. (The below script works starting from SQL Server 2008):
USE SampleDB
ALTER DATABASE SampleDB SET RECOVERY SIMPLE (actually truncates the log file - remove inactive entries)
DBCC SHRINKFILE (SampleDB, ?Desired_size?); (actually shrinks the log file by de-allocating the truncated space)
ALTER DATABASE SampleDB SET RECOVERY FULL
Then be sure to make a full backup of the database after truncating and shrinking the log file.
2. Execute one Shrinkfile Statement with TRUNCATEONLY option.
USE [SampleDB]
GO
DBCC SHRINKFILE (N'SampleDB_Log' , 0, TRUNCATEONLY)
GO
Question 1 - does the first method break the backup chain and is the reason why a new full backup is required?
Question 2 - why not just used the one statement since it truncates and shrinks the log file? In addition, it appears that a full backup is not required?
Thanks for you suggestions/responses in advance. I may be missing something regarding when/why to use these 2 methods.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

Yes, SQL Server uses the transaction logs to apply all changes to the previous backup, if you set it to simple then shrink it would lose this data so not be able to apply these changes. In the case where recovery is needed, you would only be able to recover to the last backup taken.
TRUNCATEONLY just frees any unused space from the end of the file. If you do this in full model it won't delete any of the data it would need to recover so you won't be able to completely shrink the log file.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

My question is why are you shrinking the transaction log? If properly managed it should grow to a specific point then remain static. I saw this while deployed and made a change to the transaction logs initial size and growth. The change I implemented reduced the need for shrinking the transaction log to just those instances where unusual activity, mass data import or delete, resulted in extraordinary growth of the the transaction log or when a data cut was being created from a copy of a production database.

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 4

Thanks for your responses Jonathan and Lynn!

Jonathan, I think your response provides some pros/cons of the two methods.

Lynn, regarding your question, "My question is why are you shrinking the transaction log?", as I stated in my original post, I very rarely have to truncate our production log files. Really, we may do this once a year or once every two years after a system review where we delete a lot of old data. We only shrink the transaction logs after those instances you mentioned, "instances where unusual activity, mass data import or delete, resulted in extraordinary growth of the the transaction log." I agree with you that "If properly managed it should grow to a specific point then remain static."

In my original post, I was just trying to get an understanding of why there are two methods to truncate and shrink the log file? What are the pros/cons of each method? Hence the questions:

Question 1 - does the first method break the backup chain and is the reason why a new full backup is required?

Question 2 - why not just used the one statement since it truncates and shrinks the log file? In addition, it appears that a full backup is not required?

Thanks again for your suggestions/responses.