Wednesday, 20 May 2015

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

Posted by at 1 comment:

Thursday, 14 May 2015

Troubleshooting With Damaged Database File in SQL Server

The SQL Server performs its input/output operations by using Windows API’s like ReadFile, ReadFileScatter, WriteFile and WriteFileGather. After these input/output operations, SQL server views those errors that are related to these APIs. If any of these APIs call fail with Operating System error, then SQL Server displays the Error 823.

There is a logical consistency problem encountered with input/output operations while transferring the data. Error 824 displays this logical consistency.

What is Error 824?

In SQL server, Error 824 indicates that there is a major problem in the storage system or in the hardware part. The Error is encountered when the database file is found damaged. This Error also indicates that the SQL server finds something wrong on the page while Windows didn’t find any error in the page.

Causes of Error 824

The error 824 shows the following information:

  • The suspected file of the database on which the input/output operation is executed.
  • To which database this file belongs.
  • Which page number was involved while performing input/output operation?
  • Was the performed operation a write or read operation?

As this Error indicates to issues with the storage system or the hardware part of the system, so for the solution of this problem, it is very important to look for issues in the hardware.

Inspection of Hardware Failure

First, check whether it is a hardware problem or not, if it is a hardware failure problem, then check the hardware and fix the problem. Otherwise, if it is a data corruption issue, then try to exchange various hardware components to segregate the problem.

Finally, it can be useful to change to a new hardware system. This change may include, re-formatting of the disk drives and installation of the operating system again.

Note:If the problem is not related to the hardware and you have a clean backup, then restore your database from backup.
Note:Use PAGE_VERIFY CHECKSUM option while changing the databases.

Use Following Steps To Solve The Problem Manually

  • Check the suspected pages in msdb.
  • Calibrate the database consistency.
  • Quickly turn On the PAGE_VERIFY CHECKSUM option if it is off.
  • Check the Windows Event logs for the error that is reported by the Operating System.
  • Utilize the SQLIOSim.
  • Work with your device manufacturer.
  • Evaluate the filter drivers if available.

In case the following manual method won’t work, then you should go for a third party SQL recovery tool.

Posted by at No comments:

Monday, 4 May 2015

Microsoft will release public preview of SQL Server 2016 this summer

Posted by at No comments:
Subscribe to: Posts (Atom)