none
Always on Database show suspended mode how totroubleshoot RRS feed

All replies

  • https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/suspend-an-availability-database-sql-server?view=sql-server-ver15

    Or database is in suspend mode? If it is DB is in suspend mode consider 

    EXEC sp_resetstatus 'DBname';
    ALTER DATABASE DBname SET EMERGENCY
    DBCC checkdb('DBname')
    ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE DBname SET MULTI_USER

    If you get latest good backup  and you can restore it would better than above ......


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 12, 2020 5:29 AM
  • Hello ajitkumar

    Please first confirm that if you AG DB just be suspended or in suspect mode ?

    If your AG database is suspended, you can try below methods to resume it:
    1. Using SSMS>> Expand and right-click Availability Database>> click Resume Data Movement

    2. Using T-SQL: ALTER DATABASE database_name SET HADR RESUME
    3. Using PowerShell

    More detail, please refer to this article: Resume an Availability Database (SQL Server)

    And if the AG DB is in suspect mode, you can remove the DB from AG. Restore from last good backup and re-add it to AG. Or If you can afford data loss and this is a test system you may try DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS but this should be your LAST resort.

    Here is a similar thread you can refer to How to recover suspect database which is a part of Always ON

    Hope it will help. If you have any question, let’s me know. Waiting for your feedback.

    Best Regards!
    Dawn Yang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 13, 2020 2:15 AM
  • When DBCC CHECKDB detects a corruption error, a dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory (Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG). The dump file contains the results of the DBCC CHECKDB command and additional diagnostic output. You could check it for more error information. If the error cannot be fixed and restoring from a backup is not possible, the last option is to rebuild your log file for the corrupted database. Here is the syntax:
    ALTER database YOUR_DB REBUILD LOG on
    (
    NAME = 'YOUR_DB_log',
    FILENAME = 'F:\Program Files\Microsoft SQL
    Server\MSSQL.1\MSSQL\Data\YOUR_DB_newLog
    )
    Wednesday, January 15, 2020 6:17 AM
  • hi

    Always on Database show suspended mode, how to troubleshoot


    ajitkumar

    Have you read the error log and found any entries?



    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum

    Wednesday, January 15, 2020 11:52 AM