Table of Contents
SQL Server Error : 1480 Details
SQL Server Error: 1480
Severity: 10
Event Logged or not: No
Description:
The mirrored database “%.*ls” is changing roles from “%ls” to “%ls” due to %S_MSG.
Severity 10 Description:
Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.
Cause of Error SQL Server 1480
Availability Group issues – Failover
In a availabillity Group when second server of the Availability Group issues this information message is recorded with error sql server 1480, is that when an availability group fails over there are other actions may need to be performed. A application might need some data updated in the database to reflect what the primary replica is. Perhaps the cluster witness configuration needs adjusting. Maybe services need turning off on the old primary replica.
SQL Server raises an error 1480 when the Availability Group fails over. This occurs in both the new and old primary replica instances. The text of this error from sys.messages reads:
The %S_MSG database “%.*ls” is changing roles from “%ls” to “%ls” because the mirroring session or availability group failed over due to %S_MSG. This is an informational message only. No user action is required.
Note that this error is raised for each database in the Availability Group
The failover process goes through three stages on each replica. The old primary replica will transition from “PRIMARY” to “RESOLVING” to “SECONDARY,” and the old secondary replica will transition from “SECONDARY” to “RESOLVING” to “PRIMARY.” These responsibilities will appear in the error notice above and will be utilised to fine-tune the warning.
There are three criteria for which we could issue an alert. The first is that there was a failover. Second, there was a failover, and this instance is now the primary replica. The third reason is that there was a failover, and this instance is now a secondary replica. The script below will generate alerts for the final two conditions:
/*
Use this below alert to run a job when the replica becomes primary
*/
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'AG Failover is Detected - Now Primary',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@event_description_keyword=N'"RESOLVING" to "PRIMARY"',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
/*
Use this below alert to run a job when the replica becomes secondary
*/
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'AG Failover is Detected - Now Secondary',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@event_description_keyword=N'"RESOLVING" to "SECONDARY"',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
DBAs in some circumstances choose to employ a third-party programme to monitor their servers, particularly when they have a large number of them. When you only have a few servers to manage, having all of your alerts come from SQL Server Agent isn’t a big deal, but when you have a larger environment to manage, it’s helpful to have a dashboard to monitor what’s going on. You also get numerous notifications per server for each failover for alert 1480, which is the alert for the Availability Group is changing responsibilities. You can create a custom alert that only notifies once every failover if you utilise the AlwaysOn health session.
When it comes to Availability Groups, there are just a few notifications we want to be notified about. The most important ones are:
When the primary alert number 1480 was assigned to the role,
If the availability group is set up for automatic failover but is not ready, alert number 41405 is issued.
Let’s take a look at the AlwaysOn health Extended Events Session and see how you’d query it for the relevant error numbers
Reading sql server error log location from SQL Query
Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.
USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go
By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.
For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html
Solution for Resolving the Error
This error sql server 1480 is reported when changing roles in Availability Group.
| changing roles | SQL Server error 1480 | It gives you a message once the AG replica changes its role, such as secondary to primary. |
This is just informational message and nothing to worry
SQL Server Error Code and solution summary
SQL Server Error 1480
Severity: 10
Event Logged or not: No
Description:
The mirrored database “%.*ls” is changing roles from “%ls” to “%ls” due to %S_MSG.
This error sql server 1480 is reported when changing roles in Availability Group.
| changing roles | SQL Server error 1480 | It gives you a message once the AG replica changes its role, such as secondary to primary. |
This error sql server 1480 is just informational message and nothing to worry