November 10, 2021 at 7:10 am
Dear Friends,
I noticed a sudden increase in the size of one of the Databases I am handling. Its a very small Database with minimal data at the moment. The backup set grew from 300 MB to 1 GB and now in two days to over 2 GB. A major change has been with implementation of a Read-Scale 2-Node AlwaysON..I have a full back taken every day but almost no Transactional Log backups. Would be thankful if you can advise what can be the reason..Thank you in Advance.
Best Regards...Arshad
November 10, 2021 at 11:22 am
This is strange. Kindly help us understand if the size of your database has grown or the size of the database backup file has grown? If it is a database then whether the Primary or Secondary? Also if possible can you post the screenshot of AlwaysOn settings that have been configured.
If it is the database backup file then it would be helpful if you can post the screenshot of the backup settings.
You may also check if there is any recent change in the Database Recovery Model?
P.S. Share the screenshots after scrubbing the server and credential details.
November 14, 2021 at 10:26 am
Hi Brahmand,
Yes , its strange , given the size of the Database itself..The logfile eventually went to its fullest of 20GB...As the Drive was full, I had to move the log file to another new disk... Couldn't Shrink too ..Taking a Transaction Log didn't help either....I was left with the only option of Breaking the High availability , and then shrinking by changing the Model to Simple, which enabled Shrinking ..I setup the AlwaysON configuration again , but saw these trace files-like ,in the 'log' folder..Most probably this has something to do with the increase in size of the Logfile. Would be great if you can refer the attached screen shots and advise.Thank you.
Best Regards..Arshad
November 14, 2021 at 12:14 pm
You need to be doing very frequent log backups, or if your only doing it for read scale look at transactional replication instead where you can remain in simple recovery and negate the need to backup the log frequently.
November 15, 2021 at 7:46 am
@Ant-Green Is this increase in the log-file size normal in Read-Scale ? The actual log-file size is just over 0.5 GB only. There are no drastic changes at the application side though. I found a default trace running (probably started from within ALwaysON), which I stopped , but the log file continues to grow albeit with control , after had a log backup...Will schedule it to run frequently and monitor. Your comments will be valuable..Thank you.
November 15, 2021 at 7:50 am
Yes if your in full recovery which you must be for AOAG and not doing log backups then you have made the problem.
FULL/BULK recovery you MUST take log backups to keep the log file at a maintainable size.
November 17, 2021 at 11:09 am
@Ant-Green Yes , Log backups are helping to have a control on the file size. Also, I had found some default trace was running , so stopped that too...Looks fine now..Out the scope of this,, the Log file's control size at 3.3 GB itself is debatable for a database of few hundreds of MBs...Apart from the application side/Front End Code , Transaction management , do you have any comments ? Thank you btw.
Best Regards....Arshad
November 17, 2021 at 5:44 pm
You should not stop the default trace - it isn't something that affects user databases or has any impact on an AG. It allows for tracking changes to the system and can be quite useful when an unexpected event occurs.
To your other question, how often are you now backing up the transaction log? By frequent - that would be at least once an hour and could be every 5 minutes. How frequently you perform them usually is determined by your RTO/RPO requirements - but can also be determined by the amount of activity on your system.
It really doesn't matter how big the database is - what matters is the number of transactions that change data in a given amount of time. If you are deleting, inserting and updating data constantly - which in a transaction management system I would expect that to occur a lot - then a larger transaction log or more frequent log backups would be needed.
Additionally - log records cannot be truncated until that data has been hardened on all secondary instances. If there is a lag - that could also account for not being able to clear the transaction log and cause it to grow larger.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply