July 20, 2024 at 11:58 pm
I took the full backup of the DB
Backup database MyDB to disk = 'D:\backup\MyDB_Full.bak' with compression, copy_only, stats = 5
Restored on the secondary with replace. Restore was successful. When I tried to add the DB into AG, it threw an error. the mirror database has insufficient transaction log data to preserve.
I then took the multiple log backups
Backup log MyDB to disk = 'D:\backup\MyDB_Full.trn' with compression, copy_only, stats = 5
Backup log MyDB to disk = 'D:\backup\MyDB_Full_1.trn' with compression, copy_only, stats = 5
Backup log MyDB to disk = 'D:\backup\MyDB_Full_3.trn' with compression, copy_only, stats = 5
, applied to the secondary but I am still seeing the same error. I checked and no other backups are running for MyDB.
Any suggestion where to look for or how to add the DB back into AG?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 22, 2024 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 22, 2024 at 9:50 am
Can you post the exact error message?
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
July 23, 2024 at 10:04 pm
If there are any additional transaction log backups taken before you complete restoring the logs on the secondary - it will fail with that error. You have to apply all transaction logs from the backup - through current point in time.
With that said - a better option is to enable 'automatic seeding' for your availability group. Drop the secondary database and history - then add the database to the AG. SQL Server will then perform a backup/restore in the background and join the database to the group for you.
See this documentation: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group?view=sql-server-ver16
Also review this documentation: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/tune-compression-for-availability-group?view=sql-server-ver16
If your databases are fairly large - enabling trace flag 9567 could help reduce how long it takes to add a database to the group. But - it does have some downsides so make sure you review the documentation.
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
July 24, 2024 at 6:36 am
I would go with Jeffrey Williams answer.
If you use backup/restore as part of your resyncronisation strategy then you must apply all transaction log backups taken since your original backup when you want to re-attach the DB.
Using automatic seeding in my view is a far easier option. The main thing you must remember is to delete all files for the DB at the target end before you try to rejoin the DB to the AG. MS in their wisdom did not provide the equivalent to the REPLACE option you see in a DB restore, so that is,why you need to delete the files.
Trace flag 9567 will greatly reduce network bandwidth requirements, but as with all compression it uses some CPU to do the compress/decompress. However the CPU saving for reducing the total IO will offset some if not all of the CPU cost for compression.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 26, 2024 at 4:23 pm
All, sorry for not replying to the thread. The secondary server has been offline since Saturday. I have a maintenance window tomorrow so the plan is to reseed via AG GUI. I am going to be using the automatic seeding(Not sure if that will work since the size of the DB is almost 320 GB in size)
Because I am doing automatic seeding, do I have to change the seeding mode to automatic from manual within AG properties or leave it at manual?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 26, 2024 at 4:52 pm
All, sorry for not replying to the thread. The secondary server has been offline since Saturday. I have a maintenance window tomorrow so the plan is to reseed via AG GUI. I am going to be using the automatic seeding(Not sure if that will work since the size of the DB is almost 320 GB in size)
I reseed a 7TB database using automatic seeding with compression enabled using the trace flag. That process runs several times a day to create a test system - and works fairly well.
Because I am doing automatic seeding, do I have to change the seeding mode to automatic from manual within AG properties or leave it at manual?
Yes - you have to change the configuration of the AG and set it up for automatic seeding.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply