Error ID: SQL Server error 976
Error Message:
Cause:
Primarily this error occurs when something tries to run against a database in an Availability Group when the instance is acting as the secondary and therefore the databases are read only. For example, SQL Agent jobs may be configured on both the primary and secondary instances to run integrity checks against the databases (known as DBCC CHECKDB). They would succeed on the primary as they are read/write databases, but would fail on the secondary as they are read only. It is possible to configure both instances as read/write, but this requires additional licensing from Microsoft. The error can also occur for other reasons, such as a disconnected replica, suspended data movement or a temporary network issue.
Solutions:
If the error has occurred because of a SQL Agent job trying to run against a database on a secondary instance that is read only, the solution is to amend the job so that it only runs if the instance is the primary. There is no check box to tick unfortunately, it needs a step adding at the beginning of the job to determine whether it is running on the primary or the secondary and then only move onto the next step if it is the primary. If the error has occurred due to another reason, further investigation is required to identify the specific cause (e.g. a network issue).
Need Additional Assistance?
Xynomix and we'll assess your issue and help to action a fix.
Contact Us
On submitting this form, Xynomix will store your details and may contact you in relation to your request. For more information on how we process data, please see our privacy notice.
Testimonials
Business Operations Manager – Manufacturing Industry
“In the manufacturing sector, we need flexibility and immediate assistance when things go wrong. Xynomix have achieved this brilliantly, and make sure that at least one DBA is available when we call with a problem at any time of the day or night.”
ICT Group Manager – Southend-on-sea Borough Council
“We chose Xynomix from the multiple providers approached for our support requirement for a number of reasons. Initially we were impressed with their responsiveness and deep technical knowledge of both Oracle and Microsoft SQL Server databases. Their flexibility in providing mentoring to our on-site team meant increased cost efficiency against the already competitive annual costs and consultancy rates. We’re fully confident in their understanding of our needs and see them as an extension of our own team.”
