June 5, 2017 at 12:19 pm
I'm using Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor). We have avail group setup. My primary log drive keeps getting close to filling up. I've done the whole dance of setting to simple and shrinking to log however it eventually simple build back up to what it was before I shrunk it. As it stand the log backups happen three times a day and a full backup happens at 10pm daily. If I increase the frequency of the log backup will it help with lessoning the file growth? if so why? Not totally following why when it frees up space after trans backup the file keeps growing?
June 5, 2017 at 1:17 pm
I must be doing something wrong because the db whose log filewas 30 gigs had an mdf the was 1 gig. I just don't get why it is continuouslygrowing. I was under the impression the log file would free up space after thetrans backup
June 5, 2017 at 1:19 pm
the largest table has 15180 records
June 5, 2017 at 1:25 pm
You're engaging in a technology version of insanity. Doing the same thing over and over, but expecting different results. Given a certain log backup strategy, you need a certain amount of transaction log space. Shrinking the log does not good, as with your workload, it will need to grow again.
First, you should not be setting to simple and back to manage the log space. Your log backups allow the log space to be reused, though replication/mirroring/AGs can cause the log to grow if all the replicas can't confirm the log records are written.
Your total log backup space will not shrink during a period,and you haven't mentioned if you back up to the same disk or not.
The total log space needed for the day is the same, but if you back up more often, the log can be re-used, so a smaller log can exist. Let me explain it like this.
Let's assume you have a transaction log that gets 2 transactions an hour. You have enough log space for 4 transactions in your log file. You back up 3 times a day (every 8 hours). Here's your log size:
1:00am - 2 transactions
2:00am - 4 transactions
3:00am - 6 transactions (log grows)
4:00am - 8 transactions (log grows)
5:00am - 10 transactions (log grows)
5:00am - 12 transactions (log grows)
5:00am - 14 transactions (log grows)
8:00am - log backup with 14 transactions. Log is large enough for 14 transactions
9:00am - 2 transactions
10:00am - 4 transactions
11:00am - 6 transactions
12:00pm - 8 transactions
1:00pm - 10 transactions
2:00pm - 12 transactions
3:00pm - 14 transactions
4:00pm - log backup with 14 transactions. Log is large enough for 14 transactions
4:30 - you shrink the log back to 4 transaction size
5:00pm - 2 transactions
6:00pm - 4 transactions
7:00pm - 6 transactions (log grows)
8:00pm - 8 transactions (log grows)
9:00pm - 10 transactions (log grows)
10:00pm - 12 transactions (log grows)
11:00pm - 14 transactions (log grows)
12:00am - log backup with 14 transactions. Log is large enough for 14 transactions
Repeat this every day.
I would also recommend you read this: http://www.sqlservercentral.com/articles/Administration/64582/
June 6, 2017 at 3:06 am
Snargables - Monday, June 5, 2017 12:19 PMI'm using Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor). We have avail group setup. My primary log drive keeps getting close to filling up. I've done the whole dance of setting to simple and shrinking to log however it eventually simple build back up to what it was before I shrunk it. As it stand the log backups happen three times a day and a full backup happens at 10pm daily. If I increase the frequency of the log backup will it help with lessoning the file growth? if so why? Not totally following why when it frees up space after trans backup the file keeps growing?
3 times a day is likely not sufficient to help manage the log. More than this you're saying that you are willing to lose 8 hjours of data assuming you backup the log every 8 hours which equates to 3 times a day.
Please provide more detail on the exact schedules for your log backups
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply