logfile (whitespace) and excessive growth

Question

logfile (whitespace) and excessive growth

Viewing 8 posts - 16 through 22 (of 22 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

https://www.sqlservercentral.com/articles/managing-transaction-logs

The used space in the log is always what's active. Inactive/truncated space is still there. You always want a pad in your file, as SQL Server manages this.

apostolisep3 SSC Enthusiast Points: 191 More actions · Answer 2

Steve Jones - SSC Editor wrote:

https://www.sqlservercentral.com/articles/managing-transaction-logs
The used space in the log is always what's active. Inactive/truncated space is still there. You always want a pad in your file, as SQL Server manages this.

Thank you Steve!!!!!

apostolisep3 SSC Enthusiast Points: 191 More actions · Answer 3

OK after reading the article i pretty much understand the way the logs work...

the option to truncate the log file is deprecated after sql server 2008, so the way is to backup the log file and then dbcc shrinkfile?

Thanks!

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

apostolisep3 wrote:

OK after reading the article i pretty much understand the way the logs work...
the option to truncate the log file is deprecated after sql server 2008, so the way is to backup the log file and then dbcc shrinkfile?
Thanks!

If you do frequent log backup in full recovery mode, in general it will manage itself. For high transaction application with long running may grow even if we do 5 minutes log backup. If you have no other option then shrink the log file.

You are using alwasyon better check log reuse wait desc all the time and fix based on the value.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

You don't have "copy_only" enabled as an option on the log backups do you?

This will never mark the log as re-usable.

Inherited very large AOAG setup once where the previous DBA had setup the logs as COPY_ONLY which resulted in extreme log file sizes and log backups not truncating the log.

apostolisep3 SSC Enthusiast Points: 191 More actions · Answer 6

Ant-Green wrote:

You don't have "copy_only" enabled as an option on the log backups do you?
This will never mark the log as re-usable.
Inherited very large AOAG setup once where the previous DBA had setup the logs as COPY_ONLY which resulted in extreme log file sizes and log backups not truncating the log.

Hi Anthony,

no there is no copy_only option

i changed the schedule to 1 full per day 4 diff and 16 times log backup per day.

and once a time i run the dbcc shrink logfile but i change the option 'NUL' to a new disk in order to have the latest log file before shrinking the log (dont know if i did well)

Ant-Green SSC Guru Points: 113445 More actions · Answer 7

Shrinking the log is not always wise to do.

I'd suggest going back to the managing transaction log topics and re-reading through that.

The log has grown to that size for a reason, if it keeps growing it needs space and something in the log backup routines is not sitting right or you just have something that needs a 500GB log for example.

The constant shrink/grow operations are not healthy for SQL.

Can you post the code (redacted where needed e.g DB names, file paths etc) for your backup routines and maybe something obvious stands out

apostolisep3 SSC Enthusiast Points: 191 More actions · Answer 8

USE BOSPSe

GO

BACKUP DATABASE [BOSPSe]

TO DISK = N'E:\Backups\BOSPSe.bak'

BACKUP DATABASE [BOSPSe]

TO DISK = N'E:\Backups\bospsediff.bak'

WITH DIFFERENTIAL;

GO

BACKUP LOG [BOSPSe]

TO DISK = 'E:\Backups\BOSPSe.log'

WITH NORECOVERY

there are the 3 scripts running for backup