October 25, 2021 at 9:01 pm
I am missing something I guess. Here's our backup Plan
for each we use procedure created by Ola Hallengren (https://ola.hallengren.com) called with specific parameters for the backup type.
Am I wrong saying that immediatelly after the full backup, the transaction log file is useless? So how to reduce the size of this file (actually 75 gigs and still growing)? I thought the full backup would reduce is size. Which command should I add after the full backup to empty the transaction log file?
thank you
Martin
October 25, 2021 at 9:23 pm
The plan is okay.
If you want to shrink the log, you gotta use the dbcc shrinkfile.
I don't think you should be shrinking the log file if its still increasing in size, you should check why its growing so much in the first place, you are going to have the same issues until you fix why its growing so much.
you shrink the transaction log after cleaning the VLFs, these are cleaned after a transaction log backup, not after a full backup.
October 25, 2021 at 9:35 pm
Am I wrong saying that immediatelly after the full backup, the transaction log is useless?
Yes, that is quite wrong.
The transaction log would still be needed to recover to a point-in-time after the full backup. If you never need to recover to point in time / you can recover only to diff backups and still be good, then you don't need to do log backups and should stop doing them.
Command DBCC SHRINKFILE(2, <size_in_mb>) can be used to shrink a log file, but you don't want to shrink below what you actually need in the log file. Otherwise SQL will just grow the log again, and that is huge overhead compared to just leaving the space allocated to the log there.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 26, 2021 at 8:29 am
How are you calling the log backup with Ola’a code?
Are you doing the @copy_only = ‘y’
October 26, 2021 at 11:21 am
on top of the other replies:
Only a log backup marks the log file entries to be overwritten except for open transactions.
DO NOT SHRINK LOG FILES ! ( unless you performed a one time large operation which had the log file exploding )
Chances are your log file will need to grow again ( slow operation ! ) !
If you have the need to have a smaller log file, elevate the frequency by which you run log backups.
If you create a log backup once an hour, you may lose an hour worth of data in case of disaster.
Does that meet your sla's RPO ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2021 at 11:31 am
How are you calling the log backup with Ola’a code?
Are you doing the @copy_only = ‘y’
Like this
EXECUTE DatabaseBackup
@Databases = 'BDNAME',
@Directory = 'G:\BACKUP',
@BackupType = 'LOG',
@CleanupTime = 96; --Keep file for 96 hours.
October 26, 2021 at 11:45 am
This was removed by the editor as SPAM
October 26, 2021 at 11:55 am
Verify and does the log get written to a folder called LOG or LOG_COPY_ONLY.
Also verify that @copy_only on the procedure code is set to N.
Also verify what the log_wait_reuse_desc is in sys.databases
October 26, 2021 at 5:17 pm
Perhaps the logs backups are configured, but not running.
October 28, 2021 at 3:20 pm
One could argue that your plan is fatally flawed.
It should not be a backup plan, it should be a recovery plan.
As such, you likely would have discovered the answers to your questions because you have taken the time to go through each of the possible scenarios and validate that the recovery works, and that you can complete them properly so that the RTO and RPO' s are met.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply