To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed. – when moving files on an Availability Group secondary.

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:\DATA\AG01.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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: