What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files?
I'm an engineer from SQL Server product team. This read-only media behavior is not by design and we issued a hotfix for it.
To describe the behavior, the read-only media check is done when a file is opened (e.g., DB startup, DB state change like readonly->readwrite). SQL then maintains an read-only media flag for a file in memory and this can be persisted into metadata (on system catalog). The flag may be persisted into metadata when other file metadata happens to be modified or the DB state changes. The real problem here is that once it was hardened on the metadata (which is on the primary data file), we did not reset it properly even the media is no longer read-only. One condition that can reset the flag is attach, but it only does for secondary data files.
The desired behavior is that the read-only media flag is turned off when a file is opened and the media is no longer read-only. So, when a database restarts, its state changes, it is restored, or it is attached, the is_media_read_only should reflect it correctly for every file.
Fix
The fix is documented with KB Article 4538378.
The value seems to get updated when a change is attempted on the file itself, such as LowlyDBA suggested. But only when the disk has been set to read only when no more modifications have to happen on the secondary data file. If there are still changes to be made, a different error will be shown.
For example when creating a database that has one data file on F:\ which we will later set as read only.
USE MASTER
GO
CREATE DATABASE Test
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Test1', FILENAME = N'E:\Data\Test.mdf' , SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB ),
( NAME = N'testreadonly', FILENAME = N'F:\ReadonlyDisk\testreadonly.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'E:\Data\Test_Log.ldf' , SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO
After running the diskpart command(s), as expected no change is made to is_media_read_only:
USE Test
GO
select name,physical_name,is_media_read_only,is_read_only
from sys.database_files;
Result
name physical_name is_media_read_only is_read_only
Test1 E:\Data\Test.mdf 0 0
Test_log E:\Data\Test_Log.ldf 0 0
testreadonly F:\ReadonlyDisk\testreadonly.ndf 0 0
If we try to increase the file size:
USE [master]
GO
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'testreadonly', SIZE = 6144KB )
GO
I have noticed it going two ways, either the error message that the device is not ready:
Msg 5149, Level 16, State 3, Line 21 MODIFY FILE encountered operating system error 21(The device is not ready.) while attempting to expand the physical file 'F:\ReadonlyDisk\testreadonly.ndf'.
This is a bad state, the database will also not be able to go offline & online again as sql server needs to do modifications on the secondary data file before it is in a consistent state.
The disk was set to read_only while changes still had to be made to the secondary data file.
The other error message shows that the database can be set online & offline again and that sql server knows the state of the disk, but no changes can be made.
MODIFY FILE encountered operating system error 19(The media is write protected.) while attempting to expand the physical file 'F:\ReadonlyDisk\testreadonly.ndf'.
When this second error message occurs, for example by changing the file size, the state will be updated.
use master
go
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'testreadonly', SIZE = 10240KB )
GO
Resulting in the is_media_read_only column being updated:
name physical_name is_media_read_only is_read_only
Test1 E:\Data\Test.mdf 0 0
Test_log E:\Data\Test_Log.ldf 0 0
testreadonly F:\ReadonlyDisk\testreadonly.ndf 1 0
When putting the .mdf or .ldf file on a read only disk the database will no longer come online when restarting. Since these files are modified in the Analysis step of database recovery.
You can validate this by creating another database, putting all 3 files (.mdf, .ndf, .ldf) on the same disk and setting it offline and online again:
ALTER DATABASE [Test] SET OFFLINE
ALTER DATABASE [Test] SET ONLINE
With .mdf and .ldf modified since the last offline --> Online
So in theory you could put the secondary file on the read only disk and the is_media_read_only column will get updated when sql server knows that it is on a read only disk.
It cannot know this when restarting the database or instance in your case since no modifications are made to the secondary data file. If modifications such as during the UNDO or REDO process of recovery have to happen to your secondary date file, your database will not come online.
When attempting this on a .mdf or .ldf file your database will not come online and you will get an error message like
Operating system error 19(The media is write protected.) on file "File location" during FixupLogTail.
Since they are modified on db startup.
So both our experiments showed that
is_media_read_onlychanges upon some file modification. It seems inconsistent but it's a pattern in some way. With that conclusion would you say this problem is beyond the "normal" behaviour of that flag?
Well I would say all actual file modification could/would trigger it, but if the file modification has to happen or the database is not in a consistent state then the database will simply not come online / other issues arise. If the modification fails while the database is in a consistent state, such as manually growing the file, then the error happens and the is_media_read_only is updated.
And again here, no file modification is done when changing the disk to read only, running
USE master;
GO
ALTER DATABASE Test SET READ_ONLY;
ALTER DATABASE Test SET READ_WRITE;
GO
Then changing the disk to read-write again and running the two alter database statements again:
Showing that the is_media_read_only not changing back is due to the file not being 'tested' to be on a writable disk again.
Yes, that test. It was like this: part 1 - change drive to read_only; alter database to read_only; alter database to read_write; is_media_read_only is 1 (perfect). part 2 - change drive to read_write; alter database to read_only; alter database to read_write; is_media_read_only is still 1 (it should've changed back to 0 to support the conclusion that all file modifications trigger the update of is_media_read_only).
The secondary data file is not modified when changing the database to read only and back again as seen in the edit. All actual attempted file modifications still change the is_media_read_only state.
The is_media_read_only state has nothing to do with the alter statements not modifying the secondary files, this is just how these alter statements behave.
You can test this yourself by setting the database read only and read write and checking the last modified date on the secondary data files.
If you want an answer as to why the update is then triggered when the disk is set to read only and the alter database commands run without attempting to change the secondary data file, I cannot test that part, no errors are shown or found in the error log when running the statements. It might have something to do with that sql server also does not try to remove the secondary data files that are on a disk with is_media_read_only = 1 when the database is dropped. Less error possibilities.