Here’s a little error that I stumbled across the other day when trying to migrate the data files of an Availability Group secondary node onto a nice new SAN.
I started off by running the following command against the secondary server.
ALTER DATABASE SQLUndercover MODIFY FILE (NAME = 'AG01', FILENAME = 'E:DATAAG01.mdf')
There’s nothing wrong with that, right? Well that’s what I thought until a nasty little error message popped up.
Msg 5004, Level 16, State 4, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.
What are you talking about SQL, I’m on a secondary node, the database is never going to be writable?!
I’ll be totally honest here, I’m not 100% sure why we’re seeing this error but there’s an easy fix to it. You need to make sure that ‘Readable Secondary’ for that node is set to No
Run the original command again…
The file “AG01” has been modified in the system catalog. The new path will be used the next time the database is started.
Excellent, that’s fixed our little issue. If anyone knows exactly why this happens, I’d love to hear from you.