主页 / dba / 问题 / 248886
Accepted
Ronaldo
Asked: 2019-09-17 07:34:58 +0800 CST

究竟是什么触发了 sys.database_files 上 is_media_read_only 列的更新？

  • 772

愿意解决与数据库属性错误值相关的这个问题，我is_media_read_only做了一些研究和测试，但最后我无法弄清楚究竟是什么触发is_media_read_only了sys.database_files.

根据sys.database_files文档，该列is_media_read_only应具有以下两个可能值之一：

1 = 文件在只读媒体上。

0 = 文件在读写媒体上。

有了这些信息，我对两个不同版本的 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)

在我的笔记本（Drive E:）中插入一个笔式驱动器并创建一个数据库，如下所示：

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

查询 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

结果是这样的：

之前查询结果

创建数据库后，我打开 CMD 提示符并运行diskpart. 在该实用程序上，我发出了以下命令：

list disk
select disk 2
attributes disk set readonly
attributes disk

磁盘部分

磁盘 2（我的笔式驱动器）现在是只读媒体，所以我预计is_media_read_only值会改变，但是当我sys.database_files再次查询时没有任何变化：is_media_read_only 仍然是 0。所以我开始对数据库进行随机程序，看看是否任何事情都会让 SQL Server 注意到数据库现在位于只读媒体上并将值更新为 1。我可以在两种情况下实现这一点：

第一个：将数据库更改为 READ_ONLY 模式并返回READ_WRITE. （它必须是两个动作，只有其中一个不会起作用）：

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

2nd：分离并重新附加数据库。

正如您在图片中看到的那样，这是我可以is_media_read_only更新的两种情况：1

查询后的结果

现在是时候将笔式驱动器再次恢复为 read_write 了，所以我回到diskpart并运行：

select disk 2
attributes disk clear readonly

is_media_read_only当我再次将磁盘 2 更改为读写模式时，只会0在分离和附加过程中更新。我什至尝试重新启动 SQL Server 实例，希望它能更新值，但没有运气。

直到数据库的分离和附加 SQL Server 会给我留下价值1，is_media_read_only即使磁盘不再是READ_ONLY.

因此我的问题是：究竟是什么触发了UPDATE该列is_media_read_only的sys.database_files？此实验显示的行为是否可能是 SQL Server 的错误？

sql-server system-tables

2 个回答

  1. Best Answer

    我是 SQL Server 产品团队的工程师。这种只读媒体行为并非设计使然，我们针对它发布了修补程序。

    为了描述这种行为，只读媒体检查是在打开文件时完成的（例如，DB 启动，DB 状态更改，如 readonly->readwrite）。SQL 然后为内存中的文件维护一个只读媒体标志，并且可以将其持久化到元数据中（在系统目录上）。当其他文件元数据碰巧被修改或数据库状态发生变化时，该标志可能会被持久化到元数据中。这里真正的问题是，一旦它在元数据（主要数据文件上）上得到强化，即使媒体不再是只读的，我们也没有正确重置它。可以重置标志的一个条件是附加，但它只适用于辅助数据文件。

    所需的行为是当打开文件并且媒体不再是只读的时，只读媒体标志被关闭。因此，当数据库重新启动、其状态更改、恢复或附加时，is_media_read_only应该为每个文件正确反映它。

    使固定

    知识库文章 4538378中记录了该修复程序。

    • 7

  2. 当尝试对文件本身进行更改时，该值似乎会更新，例如 LowlyDBA 建议的。但仅当磁盘已设置为只读时，辅助数据文件不再需要进行任何修改。如果仍有更改要做，则会显示不同的错误。

    例如，当创建一个包含一个数据文件的数据库时F:\，我们稍后会将其设置为只读。

    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

    运行 diskpart 命令后，正如预期的那样，不会对以下内容进行任何更改is_media_read_only：

    USE Test
GO
select name,physical_name,is_media_read_only,is_read_only
from sys.database_files;

    结果

    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

    如果我们尝试增加文件大小：

    USE [master]
GO
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'testreadonly', SIZE = 6144KB )
GO

    我注意到它有两种方式，要么是设备未准备好的错误消息：

    消息 5149，级别 16，状态 3，第 21 行修改文件在尝试扩展物理文件“F:\ReadonlyDisk\testreadonly.ndf”时遇到操作系统错误 21（设备未准备好。）。

    这是一个糟糕的状态，数据库也将无法再次离线和在线，因为 sql server 需要在辅助数据文件处于一致状态之前对其进行修改。

    磁盘设置为 read_only，但仍需对辅助数据文件进行更改。

    另一个错误信息显示数据库可以再次设置在线和离线，并且sql server 知道磁盘的状态，但不能进行任何更改。

    MODIFY FILE 在尝试扩展物理文件“F:\ReadonlyDisk\testreadonly.ndf”时遇到操作系统错误 19（媒体被写保护。）。

    当出现第二条错误消息时，例如通过更改文件大小，将更新状态。

    use master  
go
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'testreadonly', SIZE = 10240KB )
GO

    导致is_media_read_only列被更新：

    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

    将.mdfor.ldf文件放在只读磁盘上时，数据库在重新启动时将不再联机。由于这些文件是在数据库恢复的分析步骤中修改的。

    您可以通过创建另一个数据库来验证这一点，将所有 3 个文件 ( .mdf, .ndf, .ldf) 放在同一个磁盘上并将其设置为脱机并再次联机：

    ALTER DATABASE [Test] SET OFFLINE

ALTER DATABASE [Test] SET ONLINE

    自上次离线后修改了 .mdf 和 .ldf --> 在线

    在此处输入图像描述

    因此，理论上您可以将辅助文件放在只读磁盘上，is_media_read_only当 sql server 知道它在只读磁盘上时，该列将得到更新。

    在您的情况下重新启动数据库或实例时，它无法知道这一点，因为没有对辅助数据文件进行任何修改。如果您的次要日期文件必须在恢复期间UNDO或REDO恢复过程中进行修改，您的数据库将不会联机。

    在.mdf或.ldf文件上尝试此操作时，您的数据库将无法联机，您将收到一条错误消息，例如

    FixupLogTail 期间文件“文件位置”上的操作系统错误 19（媒体受写保护。）。

    因为它们是在数据库启动时修改的。

    因此，我们的两个实验都表明，is_media_read_only某些文件修改会发生变化。这似乎不一致，但在某种程度上它是一种模式。有了这个结论，您会说这个问题超出了该标志的“正常”行为吗？

    好吧，我会说所有实际的文件修改都可能/将触发它，但是如果必须进行文件修改或数据库处于不一致状态，那么数据库将根本不会上线/出现其他问题。如果在数据库处于一致状态时修改失败，例如手动增长文件，则会发生错误并is_media_read_only更新。

    再次在这里，将磁盘更改为只读时不进行文件修改，运行

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

    然后再次将磁盘更改为读写并再次运行两个 alter database 语句：

    在此处输入图像描述

    表明is_media_read_only没有变回是由于文件没有被“测试”再次在可写磁盘上。

    是的，那个测试。就像这样：第 1 部分 - 将驱动器更改为只读；将数据库更改为只读；将数据库更改为 read_write；is_media_read_only 为 1（完美）。第 2 部分 - 将驱动器更改为 read_write；将数据库更改为只读；将数据库更改为 read_write；is_media_read_only 仍然是 1（它应该变回 0 以支持所有文件修改都会触发 is_media_read_only 更新的结论）。

    将数据库更改为只读并再次返回时，不会修改辅助数据文件，如编辑中所示。所有实际尝试的文件修改仍会更改is_media_read_only状态。

    状态与不修改辅助文件的is_media_read_onlyalter 语句无关，这就是这些alter 语句的行为方式。

    您可以通过将数据库设置为只读和读写并检查辅助数据文件的最后修改日期来自行测试。

    如果您想知道为什么当磁盘设置为只读并且运行更改数据库命令而不尝试更改辅助数据文件时触发更新的原因，我无法测试该部分，在运行语句时的错误日志。这可能与该 sql server 在删除is_media_read_only = 1数据库时也不会尝试删除磁盘上的辅助数据文件有关。更少的错误可能性。

    • 6

相关问题