0

I received the following alert:

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x0000000012c000 in file 'C:\VeeamFLR\UK->SB->SQL11_4406bb77\Volume3\DATA\MyDB10_STAGE.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and...

The following script shows sys.database_files and sys.master_files have different physical_name values:

declare @dbname varchar(30)

set @dbname = (select name from sys.master_files where physical_name like 'C:\VeeamFLR\%.mdf')

select name as 'MasterFiles', physical_name as 'FullPath' from sys.master_files where physical_name like 'C:\VeeamFLR\%.%df'order by physical_name desc
exec('USE '+ @dbName + '; select name as ''DatabaseFiles'', physical_name as ''FullPath'' from sys.database_files')

Results:

MasterFiles FullPath

------------------------ ------------------------------------------------------------------------

MyDB10_STAGE C:\VeeamFLR\UK-SB-SQL11_4406bb77\Volume3\DATA\MyDB10_STAGE.mdf

MyDB10_STAGE_log C:\VeeamFLR\UK-SB-SQL11_4406bb77\Volume2\Logs\MyDB10_STAGE_log.ldf

(2 row(s) affected

DatabaseFiles FullPath

------------------------ ------------------------------------------------------------------------

MyDB10_STAGE E:\DATA\MyDB10_STAGE.mdf

MyDB10_STAGE_log D:\Logs\MyDB10_STAGE_log.ldf

(2 row(s) affected)

The sys.database_files value is correct. (Sys.master_files refers to a previous Veeam restore mount point. Not sure why). I've tried a detach and re-attach with the correct path specified:

-- detach:
ALTER DATABASE MyDB10_STAGE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyDB10_STAGE'
GO

-- No need to move physical files as actually already in correct location

USE [master]
GO
CREATE DATABASE MyDB10_STAGE ON
( FILENAME = N'E:\DATA\MyDB10_STAGE.mdf' ),
( FILENAME = N'D:\LOGS\MyDB10_STAGE_log.ldf' )
FOR ATTACH
GO

--Put database in multi user mode again:

ALTER DATABASE MyDB10_STAGE SET MULTI_USER
GO

But this has not updated physical_name in sys.master_files. Can anyone recommend how to update physical_name in sys.master_files to the correct value please?

  • 1
    Have you tried restarting the SQL Server service? – Aaron Bertrand Feb 14 '18 at 14:43
  • No, I hadn't. Restarted it and the problem disappeared. Thanks! – DiamondBeezer Feb 15 '18 at 7:58
0

I couldn't find anything about this in the documentation for sys.database_files or sys.master_files, or even sys.sysfiles or sys.sysaltfiles, but there will likely be several state changes that won't be truly reflected across all file-related catalog views until the SQL Server service has been restarted. If the changes are isolated to a single database, it might be worth trying to set the database OFFLINE and ONLINE again, but I haven't confirmed that will work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.