Last week, I re-learnt something which, according to me, is a little counter-intuitive. I was studying read-only databases out of academic interest and noticed something interesting:

When a database is marked as Read-only, the underlying file/file-groups are not marked Read-only.

Here’s a simple test wherein we:

1. Create a test database with multiple file-groups (the test works equally well with a single file-group)
2. Set the database to READ_ONLY
3. Check the file-group properties

USE master;
GO
--Create the test database
CREATE DATABASE ReadOnlyDB
ON
PRIMARY  (Name=ReadOnlyDB_Primary,
          FileName='C:DatabasesSQL2012ReadOnlyDB_Primary.mdf'
         ),
FILEGROUP SecondaryFG
        (Name=ReadOnlyDB_Secondary,
         FileName='C:DatabasesSQL2012ReadOnlyDB_Secondary.ndf'
        )
LOG ON (Name=ReadOnlyDB_Log,
        FileName='C:DatabasesSQL2012ReadOnlyDB_Log.ldf'
       );
GO

USE master;
GO
--Set the database to READ_ONLY
ALTER DATABASE ReadOnlyDB SET READ_ONLY;
GO

USE ReadOnlyDB;
GO
--Check the File & File-group properties
SELECT sfg.is_read_only,sfg.*
FROM sys.filegroups AS sfg;
GO
SELECT sdf.is_read_only,sdf.*
FROM sys.database_files AS sdf;
GO

As can be seen from the output, none of the file-groups were marked as read-only even though the database is read-only. To confirm that the database is indeed read-only, let us attempt to create a table on the database.

USE ReadOnlyDB;
GO
CREATE TABLE TestTbl (Id INT IDENTITY(1,1),
                      Name VARCHAR(50)
                     )
ON SecondaryFG;
GO

Msg 3906, Level 16, State 1, Line 1

Failed to update database “ReadOnlyDB” because the database is read-only.

A possible explanation

The fact that the file-groups are not marked read-only even though the database is read-only is counter-intuitive.

The only possible explanation that I have is that primary file-groups cannot be marked read-only which is why SQL Server does not automatically attempt to mark the file-groups and log files as read-only.

Further Reading

• SQL Server Myth: Log files are removed when a database is made READ_ONLY [Link]

Until we meet next time,

Be courteous. Drive responsibly.