Always on database in suspect mode and suspended data movement.

Always on database in suspect mode and suspended data movement.

What is suspect mode of database?

SQL database is marked as suspect when primary group file is corrupt and when SQL Server tries to start, database cannot be recovered during that time. 

There can be a lot of reasons for a database to go in suspect mode, some of them are:

• Hardware failure

• Improper shutdown of the database server

• Corruption of the database files

• Unavailable device files

• Unavailable database files

• Database resource used by operating system

• SQL Server incorrectly asserts free data page space when a row is inserted

Issue:

My drive where the 'tempdb' is located is completely filled up and there is no space left on drive causing the 'tempdb' in suspect mode.

This also suspended the data movement between databases in Always on group from primary to secondary.

Step 1:

Identified tempdb filled up the drive space and database is in suspect state

    Step 2: 

    Restarted SQL services to clear space on tempdb db and Now the database is online but the Always-on dashboard report shows critical warning and database is not synchronizing. 

    Secondary replica database data movement is suspended (fail-over dash board report shows data movement got suspended)

    Step 3:

    Expand Always-on High Availability pane and select availability databases.

    Right click on the database and select resume data movement.

     Step 4:

     Issue resolved. Now the database is completely synchronized.

    Appreciate your suggestions. Please comment your feedback and reach me out via email

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Linkedin