I'm trying to move a log file of a database that is part of an availability group. I have been following steps from the below article:
At first this worked fine for me in a test environment. When I tried it in a production environment the database on the secondary went into "Recovery Pending" state and I can't get it out.
I checked to ensure that the dB is looking in the right place for the log file, and it is. It just doesn't seem to actually use the new file. If I start and stop SQL service, the dB comes back up and is fine.
Here are the steps I'm going through and what is happening at each step:
--------------------------------------
:Connect DEVSQL --This is currently PRIMARY
USE[master]
GO
ALTER AVAILABILITY GROUP [DP-AG-DEV] MODIFY REPLICA ON N'DEVSQL' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
GO
ALTER AVAILABILITY GROUP [DP-AG-DEV] MODIFY REPLICA ON N'SQL10' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
--Modify the location of the data and transaction log files on all the replicas
:Connect DEVSQL
ALTER DATABASE [AG-Test] MODIFY FILE (NAME='AG-Test_log',FILENAME='L:\MSSQL\LOGS\AG-Test_log.ldf')
go
:Connect SQL10
ALTER DATABASE [AG-Test] MODIFY FILE (NAME='AG-Test_log',FILENAME='L:\MSSQL\LOGS\AG-Test_log.ldf')
go
All is good so far. Both the Primary and the Secondard have had their logical files changed, which has not taken affect yet because there has been no failover.
--Make SQL10 the PRIMARY
:Connect SQL10
ALTER AVAILABILITY GROUP [DP-AG-DEV] FAILOVER;
GO
SQL10 is now the Primary for this AG. And, as expected, the database [AG-Test] is in "Recovery Pending" because it is now looking for the log file in the new location. I need to move the file to the new location.
:Connect DEVSQL
--Enable XP_CMDSHELL
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
go
reconfigure
go
--MOVE FILES
xp_cmdshell 'move D:\MSSQL\LOGS\AG-Test_log.ldf L:\MSSQL\LOGS\'
go
--Disable XP_CMDSHELL
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',0
go
reconfigure
go
The log file has been moved (and it really has), and now I need to tell SQL to recover the dB (bring it out of "Pending Receovery")
--Initiate the database recovery
:Connect DEVSQL
ALTER DATABASE [AG-Test] SET ONLINE
GO
This is where the script is failing, returning the error:
Msg 1468, Level 16, State 5, Line 5
The operation cannot be performed on database "AG-Test" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
I can not get the dB to recognize the log file at it's new location.
If I restart the SQL Service, it comes back fine, which seems to indicate to me that it is not a permission problem and confirms that the file is in the right place.
How do I force SQL to look for the log file again without restarting the service?
Any advise is appreciated. Thanks,
Dave
I dispensed with the complication of trying to move database files without suspending data movement in the AG.
My final process for moving data and log files for databases in an AG was as follows:
(1) Pause replication for all AGs on the server
(2) Logically move the database files
(3) Stop the SQL servive
(4) Physically move the data and/or log files to the new location
(5) Start the SQL service
(6) Resume replication
This was a great solution for us as it isolated all changes to one server in the AG at a time. I did this for one node, failed over to that node, change the second node and failed back. All went off without a hitch.
October 23, 2015 at 12:50 pm
Thanks for sharing the steps; It worked like a charm. One observation I have is that when I run sp_helpfile on the database after the move it still shows the old location; have you noticed that?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply