Início / dba / Perguntas / 248886
Accepted
Ronaldo
Asked: 2019-09-17 07:34:58 +0800 CST

O que exatamente aciona o UPDATE da coluna is_media_read_only em sys.database_files?

  • 772

Disposto a resolver esse problema relacionado a um valor errado para a is_media_read_onlypropriedade do banco de dados fiz algumas pesquisas e testes, mas no final não consegui descobrir o que exatamente aciona o UPDATE da coluna is_media_read_onlyno sys.database_files.

De acordo com a documentação sys.database_files , a coluna is_media_read_onlydeve ter um dos dois valores possíveis:

1 = O arquivo está em mídia somente leitura.

0 = O arquivo está em mídia de leitura e gravação.

Com essas informações fiz o seguinte experimento com duas versões diferentes do 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)

Conectei um pen-drive no meu notebook (Drive E:) e criei um banco de dados da seguinte forma:

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

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

Este foi o resultado:

Resultado da consulta antes

Depois que o banco de dados foi criado, abri um prompt do CMD e executei o diskpart. No utilitário, emiti os seguintes comandos:

list disk
select disk 2
attributes disk set readonly
attributes disk

DiskPart

O disco 2 (meu pen-drive) agora é uma mídia somente leitura , então esperava que o is_media_read_onlyvalor mudasse, mas quando sys.database_filesconsultei novamente não houve alteração: is_media_read_only ainda era 0. Então comecei a fazer procedimentos aleatórios com o banco de dados para ver se qualquer coisa faria o SQL Server perceber que o banco de dados agora estava em uma mídia somente leitura e atualizar o valor para 1. Eu poderia conseguir isso em duas situações:

1º: alterando o banco de dados para o modo READ_ONLY e de volta para READ_WRITE. (deve ser ambas as ações, apenas uma delas não fará o truque):

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

2º: desanexar e anexar o banco de dados novamente.

Estas foram as duas situações em que eu poderia ter is_media_read_onlyatualizado 1como você pode ver na foto:

Resultado da consulta após

Agora era hora de reverter o pen-drive para read_write novamente, então voltei diskparte executei:

select disk 2
attributes disk clear readonly

Quando mudei o disco 2 para ler o modo de gravação novamente, a atualização sóis_media_read_only seria feita após um procedimento de desconexão e anexação. Eu até tentei reiniciar a instância do SQL Server esperando que atualizasse o valor, mas sem sorte. 0

Até um desanexar e anexar do banco de dados SQL Server me deixaria com valor 1para is_media_read_onlymesmo que o disco não estivesse mais READ_ONLY.

Assim, minhas perguntas: O que exatamente aciona o UPDATEda coluna is_media_read_onlyem sys.database_files? O comportamento mostrado com este experimento pode ser um bug do SQL Server?

sql-server system-tables

2 respostas

  1. Best Answer

    Sou um engenheiro da equipe de produto do SQL Server. Esse comportamento de mídia somente leitura não ocorre por design e emitimos um hotfix para ele.

    Para descrever o comportamento, a verificação de mídia somente leitura é feita quando um arquivo é aberto (por exemplo, inicialização do banco de dados, mudança de estado do banco de dados como readonly->readwrite). O SQL mantém um sinalizador de mídia somente leitura para um arquivo na memória e isso pode ser persistido em metadados (no catálogo do sistema). O sinalizador pode ser persistido em metadados quando outros metadados de arquivo são modificados ou o estado do banco de dados muda. O problema real aqui é que, uma vez que foi endurecido nos metadados (que está no arquivo de dados primário), não o redefinimos corretamente, mesmo que a mídia não seja mais somente leitura. Uma condição que pode redefinir o sinalizador é anexar, mas isso só funciona para arquivos de dados secundários.

    O comportamento desejado é que o sinalizador de mídia somente leitura seja desativado quando um arquivo for aberto e a mídia não for mais somente leitura. Assim, quando um banco de dados é reiniciado, seu estado muda, é restaurado ou anexado, ois_media_read_only deve refleti-lo corretamente para cada arquivo.

    Fixar

    A correção está documentada com o artigo KB 4538378 .

    • 7

  2. O valor parece ser atualizado quando uma alteração é tentada no próprio arquivo, como sugerido pelo LowlyDBA. Mas somente quando o disco tiver sido configurado para somente leitura, quando não houver mais modificações no arquivo de dados secundário. Se ainda houver alterações a serem feitas, um erro diferente será mostrado.

    Por exemplo, ao criar um banco de dados que possui um arquivo de dados no F:\qual definiremos posteriormente como somente leitura.

    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

    Depois de executar o(s) comando(s) diskpart, como esperado, nenhuma alteração é feita em is_media_read_only:

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

    Resultado

    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

    Se tentarmos aumentar o tamanho do arquivo:

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

    Percebi que está acontecendo de duas maneiras, ou a mensagem de erro de que o dispositivo não está pronto:

    Msg 5149, nível 16, estado 3, linha 21 MODIFY FILE encontrou o erro 21 do sistema operacional (o dispositivo não está pronto.) ao tentar expandir o arquivo físico 'F:\ReadonlyDisk\testreadonly.ndf'.

    Este é um estado ruim, o banco de dados também não poderá ficar offline e online novamente, pois o sql server precisa fazer modificações no arquivo de dados secundário antes que ele fique em um estado consistente.

    O disco foi definido como read_only enquanto as alterações ainda precisavam ser feitas no arquivo de dados secundário.

    A outra mensagem de erro mostra que o banco de dados pode ser configurado online e offline novamente e que o sql server conhece o estado do disco, mas nenhuma alteração pode ser feita.

    MODIFY FILE encontrou o erro 19 do sistema operacional (a mídia está protegida contra gravação.) ao tentar expandir o arquivo físico 'F:\ReadonlyDisk\testreadonly.ndf'.

    Quando essa segunda mensagem de erro ocorrer, por exemplo, alterando o tamanho do arquivo, o estado será atualizado.

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

    Resultando na is_media_read_onlyatualização da coluna:

    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

    Ao colocar o arquivo .mdfou .ldfem um disco somente leitura, o banco de dados não ficará mais online ao reiniciar. Visto que esses arquivos são modificados na etapa Análise de recuperação do banco de dados.

    Você pode validar isso criando outro banco de dados, colocando todos os 3 arquivos ( .mdf, .ndf, .ldf) no mesmo disco e configurando-o offline e online novamente:

    ALTER DATABASE [Test] SET OFFLINE

ALTER DATABASE [Test] SET ONLINE

    Com .mdf e .ldf modificados desde o último offline --> Online

    insira a descrição da imagem aqui

    Portanto, em teoria, você pode colocar o arquivo secundário no disco somente leitura e a is_media_read_onlycoluna será atualizada quando o sql server souber que está em um disco somente leitura.

    Ele não pode saber isso ao reiniciar o banco de dados ou instância no seu caso, pois nenhuma modificação é feita no arquivo de dados secundário. Se modificações como durante o UNDOprocesso REDOde recuperação tiverem que acontecer em seu arquivo de data secundário, seu banco de dados não ficará online.

    Ao tentar isso em um arquivo .mdfou .ldf, seu banco de dados não ficará online e você receberá uma mensagem de erro como

    Erro do sistema operacional 19(A mídia está protegida contra gravação.) no arquivo "Localização do arquivo" durante FixupLogTail.

    Uma vez que eles são modificados na inicialização do banco de dados.

    Portanto, ambos os nossos experimentos mostraram que is_media_read_onlyas mudanças ocorrem após alguma modificação de arquivo. Parece inconsistente, mas é um padrão de alguma forma. Com essa conclusão, você diria que esse problema está além do comportamento "normal" desse sinalizador?

    Bem, eu diria que todas as modificações reais de arquivos poderiam / desencadeariam isso, mas se a modificação do arquivo tiver que acontecer ou o banco de dados não estiver em um estado consistente, o banco de dados simplesmente não ficará on-line / surgirão outros problemas. Se a modificação falhar enquanto o banco de dados estiver em um estado consistente, como aumentar manualmente o arquivo, o erro ocorrerá e o arquivo is_media_read_onlyserá atualizado.

    E novamente aqui, nenhuma modificação de arquivo é feita ao alterar o disco para somente leitura, executando

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

    Em seguida, alterando o disco para leitura-gravação novamente e executando as duas instruções alter database novamente:

    insira a descrição da imagem aqui

    Mostrando que a is_media_read_onlynão mudança de volta é devido ao arquivo não ser 'testado' para estar em um disco gravável novamente.

    Sim, esse teste. Ficou assim: parte 1 - mude o drive para read_only; alterar banco de dados para read_only; alterar banco de dados para read_write; is_media_read_only é 1 (perfeito). parte 2 - mude a unidade para read_write; alterar banco de dados para read_only; alterar banco de dados para read_write; is_media_read_only ainda é 1 (deveria ter mudado de volta para 0 para apoiar a conclusão de que todas as modificações de arquivo acionam a atualização de is_media_read_only).

    O arquivo de dados secundário não é modificado ao alterar o banco de dados para somente leitura e vice-versa, conforme visto na edição. Todas as tentativas reais de modificação de arquivo ainda alteram o is_media_read_onlyestado.

    ois_media_read_only estado não tem nada a ver com as instruções alter não modificando os arquivos secundários, é assim que essas instruções alter se comportam.

    Você mesmo pode testar isso configurando o banco de dados somente leitura e leitura e gravação e verificando a data da última modificação nos arquivos de dados secundários.

    Se você quiser uma resposta sobre por que a atualização é acionada quando o disco está definido como somente leitura e os comandos alter database são executados sem tentar alterar o arquivo de dados secundário, não posso testar essa parte, nenhum erro é mostrado ou encontrado no log de erros ao executar as instruções. Pode ter algo a ver com que o sql server também não tente remover os arquivos de dados secundários que estão em um disco is_media_read_only = 1quando o banco de dados é descartado. Menos possibilidades de erro.

    • 6

relate perguntas