August 29, 2020 at 3:08 pm
Hello
I have 2 SQL servers, both are:
Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64) Jul 20 2019 21:42:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
and i have a database that is 500 gigs, with a transaction log size of 170, but its really 10 gigs that is being used, its 170 because of a lot of inserts for nightly reports.
anyways, when i try to add the database (which it does qualify after running the pre-test), it successfully adds it to the Always on, it backs up the full, restores the full to the secondary no problem, then backups up the log, its about 20 gigs, then it restores it to the secondary, however it never finishes, just hangs at 100 percent. I tried this with the AG wizard, i did this on sqlcmd, i even tried to restore the full and tranlog manually, but still always hangs at 100 percent, other databases did fine, is there a command i should use to check the db? right now i do see sessions reading and writing to the databases, but very minor, should i kick them all off while i do this? any suggestions, thoughts etc will help.
thanks in advance
August 30, 2020 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 3, 2020 at 8:06 pm
Your transaction log is too big. Shrink the log, then do a manual full and xlog backup. Restore these on the secondary in restoring state and then add database to availability group, skip synchronization.
On the secondary you can create an event session for [sqlserver.databases_backup_restore_throughput ] to monitor the database restore process
September 4, 2020 at 7:32 am
Whats the growth amount of the transaction log?
How many VLF's does the transaction log have?
If theres more than the usual recommended amount of VLF's (over 512) then you need to go and investigate your log growth settings, to small will result in a large number of VLF's which takes recovery to a whole different worlds time zone as it will just take forever as SQL has to go through each VLF to see if it has recovered everything or not even if there is nothing in the VLF for it to do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply