What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files?

Willing to solve this problem related to a wrong value for is_media_read_only database property I did some research and tests, but in the end I couldn't sort out what exactly triggers the UPDATE of the column is_media_read_only on sys.database_files.

According to sys.database_files documentation, the column is_media_read_only should have one of two possible values:


1 = File is on read-only media.


0 = File is on read-write media.

With this information I did the following experiment with two different versions of SQL Server:

Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) - 12.0.6108.1 (X64)

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

Plugged a pen-drive in my notebook (Drive E:) and created a database as follows:

CREATE DATABASE [MyDB]
 ON  PRIMARY 
( NAME = N'MyDB_01', FILENAME = N'D:\DataBases\MyDB_01.mdf'), 
 FILEGROUP [SECONDARY] 
( NAME = N'MyDB_02', FILENAME = N'E:\DataBasesPendrive\MyDB_02.ndf')
 LOG ON 
( NAME = N'MyDB_log', FILENAME = N'D:\DataBases\MyDB_log.ldf')
GO

Queried sys.database_files:

USE MyDB;
GO
SELECT file_id, name, physical_name, is_media_read_only, is_read_only, state_desc  
FROM sys.database_files;
GO

This was the result:

Once the database was created I opened a CMD prompt and ran diskpart. On the utility I issued the following commands:

list disk
select disk 2
attributes disk set readonly
attributes disk

Disk 2 (my pen-drive) is now a read only media, so I expected the is_media_read_only value to change, but when I queried sys.database_files again there was no change: is_media_read_only was still 0. So I started doing random procedures with the database to see if anything would make SQL Server notice that the database was now sitting on a read only media and update the value to 1. I could achieve this in two situations:

1st: changing the database to READ_ONLY mode and back to READ_WRITE. (it must be both actions, just one of them won't do the trick):

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.

StackExchange Database Administrators Q#248886, answer score: 7