When a database is part of AlwaysOn Availability Group, it means whatever commands we are executing on Primary replica database, those commons will also be sent to all the Secondary replicas database via log records.

So, If you are adding a database file – data or log to the Primary Replica, the add file command will also be executed on the secondary replicas.

If you have not placed your database files for your Primary and Secondary replica database on an identical path, then SQL Server will not be able to create that file on the Secondary Replica server. In the result of this, the add file command will fail on the Secondary replica, and it will force the replica to go into the suspended state.

Demonstration:

To demo this, I am going to add a data file to the database “Dharmendra” which is part of the AlwaysOn Availability Group. I will be keeping the new data file on the “V:\” drive on the primary replica, but this drive does not exist on the Secondary replica. Let’s follow the step by step approach;

• After enabling the SQLCMD mode, run the below command using SSMS on any replica to check the current synchronization status of the database.

:Connect SQL2K16N1 --- Key here your primary replica
SELECT @@servername as Servername,
DB_NAME(database_id) AS databasename, 
synchronization_state_desc, 
synchronization_health_desc, 
suspend_reason, 
suspend_reason_desc 
FROM sys.dm_hadr_database_replica_states
WHERE is_local=1
go
:Connect SQL2K16N2 --- Key here your secondary replica
SELECT @@servername as Servername,
DB_NAME(database_id) AS databasename, 
synchronization_state_desc, 
synchronization_health_desc, 
suspend_reason, 
suspend_reason_desc 
FROM sys.dm_hadr_database_replica_states
WHERE is_local=1

Here is the output the command; the database synchronization status is healthy on both the replicas.

 

• Let’s add a data file into the database “Dharmendra” on the Primary replica (SQL2k16N1)

USE [master]
GO
ALTER DATABASE [Dharmendra]
ADD FILE (NAME = N'Dharmendra_3',
FILENAME = N'V:\SQLData\Dharmendra.ndf' ,
SIZE = 5096KB, FILEGROWTH = 1024KB) TO FILEGROUP [PRIMARY]
GO

Note: The “V:\” doesn’t exist on the secondary replica (SQL2k16N2).

• Re-validate the synchronization status of the database

:Connect SQL2K16N1 --- Key here your primary replica
SELECT @@servername as Servername,
DB_NAME(database_id) AS databasename, 
synchronization_state_desc, 
synchronization_health_desc, 
suspend_reason, 
suspend_reason_desc 
FROM sys.dm_hadr_database_replica_states
WHERE is_local=1
go
:Connect SQL2K16N2 --- Key here your secondary replica
SELECT @@servername as Servername,
DB_NAME(database_id) AS databasename, 
synchronization_state_desc, 
synchronization_health_desc, 
suspend_reason, 
suspend_reason_desc 
FROM sys.dm_hadr_database_replica_states
WHERE is_local=1

Here is the output the command; It shows that the redo thread is not able to apply the command that’s why it caused to bring the Secondary Replica database in the suspended state.

• Let’s look at the Secondary Replica error log to understand the issue

2018-07-21 20:20:41.030 spid36s Error: 5123, Severity: 16, State: 1.
2018-07-21 20:20:41.030 spid36s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘V:\SQLData\Dharmendra.ndf’.
2018-07-21 20:20:41.040 spid36s Error: 5123, Severity: 16, State: 1.
2018-07-21 20:20:41.040 spid36s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘V:\SQLData\Dharmendra.ndf’.
2018-07-21 20:20:41.040 spid36s Error: 5123, Severity: 16, State: 1.
2018-07-21 20:20:41.040 spid36s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘V:\SQLData\Dharmendra.ndf’.
2018-07-21 20:20:41.040 spid36s Error: 5183, Severity: 16, State: 1.
2018-07-21 20:20:41.040 spid36s Cannot create the file “Dharmendra_3”. Use WITH MOVE to specify a usable physical file name. Use WITH REPLACE to overwrite an existing file.
2018-07-21 20:20:41.040 spid36s Always On Availability Groups data movement for database ‘Dharmendra’ has been suspended for the following reason: “system” (Source ID 2; Source string: ‘SUSPEND_FROM_REDO’). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
2018-07-21 20:20:41.050 spid36s Error: 3313, Severity: 21, State: 2.
2018-07-21 20:20:41.050 spid36s During redoing of a logged operation in database ‘Dharmendra’, an error occurred at log record ID (34:1168:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

• If this happened on your production system, it is recommended to add the same path on the secondary replica so that you can resume the data movement using below command;

ALTER DATABASE [Dharmendra] SET HADR RESUME;
GO

• If it is not possible to recreate the path on the secondary replica, you need to reinitialize the secondary replica database again.

Thanks for learning and reading the blog!