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

Question

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):

USE master;
GO
ALTER DATABASE MyDB SET READ_ONLY;
ALTER DATABASE MyDB SET READ_WRITE;
GO

2nd: detach and attach the database back again.

These were the two situations in which I could have is_media_read_only updated to 1 as you can see on the picture:

Now it was time to revert the pen-drive to read_write again, so I went back to diskpart and ran:

select disk 2
attributes disk clear readonly

When I changed Disk 2 to read write mode back again the is_media_read_only would only update to 0 upon a detach and attach procedure. I even tried to restart the SQL Server instance hoping it would update the value, but no luck.

Until a detach and attach of the database SQL Server would leave me with value 1 for is_media_read_only even though the disk was no longer READ_ONLY.

Thus my questions: What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files? Could the behavior shown with this experiment be a bug of SQL Server?

Answer 1

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.

Answer 2

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_only changes 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.