January 23, 2018 at 2:53 am
Hello Experts,
This is driving me crazy, hope some of you can shed some light what is going on.
Scenario:
1. I create two identical databases (AdventureWorks2012), both set to FULL recovery mode.
One of them is set to participate in synchronized AlwaysOn (AdventureWorks2012), the other remains standalone (AdventureWorks2012_Standalone)
2. I insert 1 million rows in both databases.
3. I create full backup-log backup-full backup from both databases, without any additional transactions in them.
4. Results:
AOn, FULL: 1846 MB
AOn, LOG: 1055 MB
AOn, FULL: 791 MB
Standalone FULL: 791 MB
Standalone LOG: 1055 MB
Standalone FULL: 791 MB
I know that the full backup contains some of the transaction log, in order to be able to fully recover the database.
But, why is the AlwaysOn database contains the complete log in the FULL backup? (Patch level SP3 CU5)
What am I missing?
Thank you.
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
January 23, 2018 at 5:34 am
Has the Availability group processed the changes between the databases (Primary to secondary) when you do the backup? It's going to retain changes as uncommitted until they go through the process while the other database without the AG will have committed those logs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 23, 2018 at 5:36 am
Oh, and since everyone and their brother will shortly be correcting me, saying that they're uncommitted isn't strictly speaking true. They were committed, but are not yet transmitted, hence they're kept around. Sorry for the lack of clarity.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 23, 2018 at 6:11 am
Hi Grant,
Thank you for your answer.
When I'm doing the backups, everything is in sync and committed. I've already tried the automatic failover too (without data loss), with the same results regarding the backups.
I can replicate this behavior as many time I want, but only with the AlwaysOn databases.
Are there any more ways to check if all changes been processed?
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply