SQL AG restoring the tlog stuck

Steven Balderrama 21 Reputation points
2020-08-29T21:23:32.013+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,758 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2020-08-31T02:41:15.647+00:00

    Hi @Steven Balderrama ,

    Did the database meet the requirement for AG database? Please refer to Checklist: Requirements (Availability Databases).

    Please try to add the database to AG again from SSMS UI and choose ‘Automatic seeding’ option as below screenshot.

    21395-annotation-2020-08-31-103301.jpg

    Please refer to Add a Database to an Always On availability group to get more information.

    If this is not work, please share us a screenshot about your issue. And check the error log to get some useful information.

    Best regards,
    Cathy

    =======================================

    If the response helped, do "Accept Answer" and upvote it.

  2. Shashank Singh 6,246 Reputation points
    2020-08-31T07:35:28.91+00:00

    As i understand the whole problem is the transaction log restore which is not finishing no matter what method you take. The first thing I would check is whether the secondary server has Instant File initialization present or not. if not this could seriously slow your restores but please note IFI is not applicable to log files this is just a prerequisite I want you to check. The other reason which I see is 170 GB log file where only 10 GB is ONLY being used. What is happening is when trying to restore the database is trying to create 170 GB file and is taking time, may be you also have lot of VLF's in database which has caused fragmentation of log file and hence it is taking time.

    Run dbcc loginfo(db_name) to check amount of VLF's in source database. if you have some 800 or 1000 that is not a good number and you must reduce it first.

    Lastly check SQL Server errorlog to see if you have any errors generated during log restore.

    Solution:

    Can you go ahead and shrink the log file, yes log file shrinking is not a good thing but since you are stuck with long going transaction log restores shrinking will reduce work of SQL Server on secondary replica. This will also reduce VLF's

    0 comments