Environment
> Database server JBSAG1 and JBSAG2 are on primary datacentre located in East US.
-> Database server JBSAG3 is on secondary datacentre in Korea Central.
-> Database servers JBSAG1, JBSAG2 and JBSAG3 are part of failover cluster without shared storage.
-> Database JBDB on replicas JBSAG2 and JBSAG3 exhibits a status of “Not Synchronizing / Suspect”. Below is the snapshot from SQL Server Management Studio and Always on Dashboard,
-> Checking SQL server error log on Synchronous Replica JBSAG2,
2021-02-20 15:10:23.760 spid43s Always On Availability Groups connection with primary database established for secondary database ‘JBDB’ on the availability replica ‘JBSAG1’ with Replica ID: {66be35ff-3906-4c64-bfbd-d88618df4221}. This is an informational message only. No user action is required.
2021-02-20 15:10:23.760 spid43s The recovery LSN (37:4698:1) was identified for the database with ID 5. This is an informational message only. No user action is required.
2021-02-20 15:10:24.790 spid39s Error: 5123, Severity: 16, State: 1.
2021-02-20 15:10:24.790 spid39s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\SQL\JBDB1.ndf’.
2021-02-20 15:10:24.790 spid39s Error: 5123, Severity: 16, State: 1.
2021-02-20 15:10:24.790 spid39s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\SQL\JBDB1.ndf’.
2021-02-20 15:10:24.790 spid39s Error: 5123, Severity: 16, State: 1.
2021-02-20 15:10:24.790 spid39s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\SQL\JBDB1.ndf’.
2021-02-20 15:10:24.800 spid39s Error: 5183, Severity: 16, State: 1.
2021-02-20 15:10:24.800 spid39s Cannot create the file “JBDB1”. Use WITH MOVE to specify a usable physical file name. Use WITH REPLACE to overwrite an existing file.
2021-02-20 15:10:24.800 spid39s Always On Availability Groups data movement for database ‘JBDB’ 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.
2021-02-20 15:10:24.800 spid39s Parallel redo is shutdown for database ‘JBDB’ with worker pool size [1].
2021-02-20 15:10:24.810 spid39s Error: 3313, Severity: 21, State: 2.
2021-02-20 15:10:24.810 spid39s During redoing of a logged operation in database ‘JBDB’ (page (0:0) if any), an error occurred at log record ID (37:4725:1). Typically, the specific failure is previously logged as an error in the operating system error log. Restore the database from a full backup, or repair the database.
-> Checking SQL server error log on Asynchronous Replica JBSAG3,
2021-02-20 15:13:56.700 spid43s Always On Availability Groups connection with primary database established for secondary database ‘JBDB’ on the availability replica ‘JBSAG1’ with Replica ID: {66be35ff-3906-4c64-bfbd-d88618df4221}. This is an informational message only. No user action is required.
2021-02-20 15:13:56.880 spid43s The recovery LSN (37:4698:1) was identified for the database with ID 5. This is an informational message only. No user action is required.
2021-02-20 15:13:58.200 spid58s Error: 5123, Severity: 16, State: 1.
2021-02-20 15:13:58.200 spid58s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\SQL\JBDB1.ndf’.
2021-02-20 15:13:58.200 spid58s Error: 5123, Severity: 16, State: 1.
2021-02-20 15:13:58.200 spid58s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\SQL\JBDB1.ndf’.
2021-02-20 15:13:58.200 spid58s Error: 5123, Severity: 16, State: 1.
2021-02-20 15:13:58.200 spid58s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\SQL\JBDB1.ndf’.
2021-02-20 15:13:58.200 spid58s Error: 5183, Severity: 16, State: 1.
2021-02-20 15:13:58.200 spid58s Cannot create the file “JBDB1”. Use WITH MOVE to specify a usable physical file name. Use WITH REPLACE to overwrite an existing file.
2021-02-20 15:13:58.220 spid58s Always On Availability Groups data movement for database ‘JBDB’ 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.
2021-02-20 15:13:58.220 spid58s Parallel redo is shutdown for database ‘JBDB’ with worker pool size [1].
2021-02-20 15:13:58.220 spid58s Error: 3313, Severity: 21, State: 2.
2021-02-20 15:13:58.220 spid58s During redoing of a logged operation in database ‘JBDB’ (page (0:0) if any), an error occurred at log record ID (37:4725:1). Typically, the specific failure is previously logged as an error in the operating system error log. Restore the database from a full backup, or repair the database.
-> From the error message it is clear that an additional data file C:\SQL\JBDB1.ndf was added in the primary replica. The created secondary file is created fine on the primary replica, but it is not getting created on the secondary replica due to error “The system cannot find the path specified.”.
-> I checked if location C:\SQL is valid and present on JBSAG2 and JBSAG3. That location was not present in JBSAG2 and JBSAG3. I created those location on each of these servers.
-> I checked the status of the database JBDB and it was suspended due to this error,
-> I resumed the data movement using below query,
:CONNECT JBSAG2
ALTER DATABASE [JBDB] SET HADR RESUME;
GO
:CONNECT JBSAG3
ALTER DATABASE [JBDB] SET HADR RESUME;
GO
-> Issue got resolved. Below is the status of Availability group dashboard,
-> That was an easy fix. But what if the file that was created on primary replica on a specific drive that is not available on Secondary replicas. For example, NDF or LDF file is created on H:\SQL folder and H: drive is not present in secondary replicas.
-> One option will be to remove the database from Always On and then restore the database to required drive and configure Always ON all over again for that database. This process will take good amount of time depending on the database size and also the database will be left with no HA/DR till the configuration is completed.
-> Second option will be to Add drive H: on secondary replicas.
-> Third option will be to follow below steps. This can only be possible if you have not already added the additional data or log file already. You cannot follow this approach if you have already added the files and you are seeing “CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file” message in secondary replica.
-> Consider a scenario where you have to add a secondary data file or log file to a location in Primary replica that is not available in secondary replica. In this case I will try reproducing the issue by adding an additional log file at primary on a location that is not available in secondary replicas. I am adding additional log file due to below reason,
- The drive that holds the LDF file is full. we dont have more space on that drive. Adding more disk space is not possible at this moment. Tlog backup cannot free up space due to Availability group.
- The size of log file has reached 2 TB. Tlog backup cannot free up space due to Availability group. Maximum size for a TLOG file can be 2 TB only.
-> Please note that adding additional log files to a database is not recommended and is done strictly for abover reasons.
-> The below procedure can be followed for both data and log file if the location where the new file that will be created in primary replica will not be available at secondary replica.
-> Remove the secondary database from Availability group using below query,
:CONNECT JBSAG2
ALTER DATABASE [JBDB] SET HADR OFF;
GO
:CONNECT JBSAG3
ALTER DATABASE [JBDB] SET HADR OFF;
GO
-> The database in secondary replica will be in restoring state,
-> Now add the additional log file in primary replica using below query.
USE [master]
GO
ALTER DATABASE [JBDB] ADD LOG FILE
( NAME = N'JBDB1_Log', FILENAME = N'F:\SQL\LOG\JBDB1_Log.ldf'
, SIZE = 512000KB , FILEGROWTH = 512000KB )
GO
-> Perform a log backup using below query on database JBDB in primary replica,
BACKUP LOG [JBDB] TO DISK = N'\\jbsdc\Logship\JBDB_20Feb2021_addfile.trn' WITH NOFORMAT, NOINIT, NAME = N'JBDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-> Restore the log backup on secondary replica JBSAG2 and JBSAG3 using with Move option to move the LDF file to a location that is available in secondary replicas,
:CONNECT JBSAG2
RESTORE LOG [JBDB]
FROM DISK = N'\\jbsdc\Logship\JBDB_20Feb2021_addfile.trn'
WITH FILE = 1
,MOVE N'JBDB1_Log' TO N'G:\log\JBDB1_Log.ldf'
,NORECOVERY, STATS = 10
GO
:CONNECT JBSAG3
RESTORE LOG [JBDB]
FROM DISK = N'\\jbsdc\Logship\JBDB_20Feb2021_addfile.trn'
WITH FILE = 1
,MOVE N'JBDB1_Log' TO N'G:\log\JBDB1_Log.ldf'
,NORECOVERY, STATS = 10
GO
-> Join back the secondary databases to availability group,
-> Check the Always On availability group dashboard to check if everything works fine,
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.