Logical Name Mismatch: master_files vs database_files

A logical name mismatch means sys.master_files and sys.database_files show different names for the same file. On a standalone server the two views agree. The difference appears on a secondary replica.

Two Views, Two Places

The view sys.master_files has one row for every file of every database. SQL Server stores it in the master database, as one system wide list. The view sys.database_files has one row per file of the current database. It lives inside that database. Both views list the logical name in the name column and the file path in physical_name. The file_id column joins them.

Two copies in two places can drift apart. When they do, the logical name or the path of a file can differ between the views. That is a logical name mismatch. The check below compares the names for one database.

Rename a Logical Name on One Server

The demo creates a database named FileNameMismatchDemo. Its log file has a poor logical name that ends in xxx. The script reads the default data folder of your instance, so it needs no path from you.

USE master;
GO
IF DB_ID(N'FileNameMismatchDemo') IS NULL
BEGIN
    DECLARE @dir nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
    DECLARE @sql nvarchar(max) = N'CREATE DATABASE FileNameMismatchDemo
        ON PRIMARY (NAME = N''FileNameMismatchDemo'', FILENAME = N''' + @dir + N'FileNameMismatchDemo.mdf'')
        LOG ON (NAME = N''FileNameMismatchDemo_xxx'', FILENAME = N''' + @dir + N'FileNameMismatchDemo_log.ldf'');';
    EXEC (@sql);
END;
GO
USE FileNameMismatchDemo;

SELECT mf.file_id, mf.name AS MasterFilesName, df.name AS DatabaseFilesName,
       CASE WHEN mf.name = df.name COLLATE DATABASE_DEFAULT THEN N'Match' ELSE N'Mismatch' END AS NameStatus
FROM sys.master_files AS mf
JOIN sys.database_files AS df ON df.file_id = mf.file_id
WHERE mf.database_id = DB_ID()
ORDER BY mf.file_id;

Now rename the log file. MODIFY FILE with NEWNAME changes the logical name while the database stays online. Then run the same check again.

ALTER DATABASE FileNameMismatchDemo
MODIFY FILE (NAME = N'FileNameMismatchDemo_xxx', NEWNAME = N'FileNameMismatchDemo_log');

SELECT mf.file_id, mf.name AS MasterFilesName, df.name AS DatabaseFilesName,
       CASE WHEN mf.name = df.name COLLATE DATABASE_DEFAULT THEN N'Match' ELSE N'Mismatch' END AS NameStatus
FROM sys.master_files AS mf
JOIN sys.database_files AS df ON df.file_id = mf.file_id
WHERE mf.database_id = DB_ID()
ORDER BY mf.file_id;

Both views changed at once, so a single server shows no mismatch. That is the normal case. Rename Logical File Name of a SQL Server Database covers the rename itself in more detail.

What Happens on a Secondary Replica

A client had a database in an Always On availability group and wanted to rename the log file. A lab setup reproduced the logical name mismatch. The steps were short. Create a database on the primary and take a backup. Create the availability group and add the database. Rename the log file on the primary.

Then compare the views on the secondary. There, sys.database_files showed the new name. The ALTER command replays into the database through Always On data movement. The view sys.master_files still showed the old name. This was a lab observation on the build of that time, so confirm it on your build.

The ERRORLOG gave a clue. The mismatch cleared when the database started up again, which the log records as Starting up database. A restart of the database therefore refreshes the name. An availability group database cannot go offline and online on demand. Restart the secondary replica, or fail over to the secondary and fail back. A failover and a fail back leave the roles as they started. Fail over only when the secondary is synchronized, or the move can lose data.

To compare both replicas from one window, turn on SQLCMD mode in SSMS 22 from the Query menu. The template below needs two replicas, so the demo cannot run it. Replace the server and database names with yours.

-- SQLCMD mode. Replace PrimaryServer, SecondaryServer and YourDatabase.
:connect PrimaryServer
SELECT N'Primary' AS Replica, file_id, name, physical_name
FROM sys.master_files WHERE database_id = DB_ID(N'YourDatabase');
GO
:connect SecondaryServer
SELECT N'Secondary' AS Replica, file_id, name, physical_name
FROM sys.master_files WHERE database_id = DB_ID(N'YourDatabase');
GO

Check Every Database at Once

On a replica that allows read access, one script can compare the two views for every database. It builds a query per online database with STRING_AGG, which needs SQL Server 2017. It returns only the files whose names differ. An empty result means no mismatch in the databases it could read. A database the script cannot read is skipped, so an empty result says nothing about it.

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(CAST(N'SELECT N' + QUOTENAME(d.name, '''') + N' AS DatabaseName, mf.file_id, mf.name COLLATE DATABASE_DEFAULT AS MasterFilesName, df.name COLLATE DATABASE_DEFAULT AS DatabaseFilesName
FROM sys.master_files AS mf JOIN ' + QUOTENAME(d.name) + N'.sys.database_files AS df ON df.file_id = mf.file_id
WHERE mf.database_id = ' + CAST(d.database_id AS nvarchar(10)) + N' AND mf.name COLLATE DATABASE_DEFAULT <> df.name COLLATE DATABASE_DEFAULT' AS nvarchar(max)), N' UNION ALL ')
    FROM sys.databases AS d
    WHERE d.state_desc = N'ONLINE' AND HAS_DBACCESS(d.name) = 1);
EXEC sys.sp_executesql @sql;

On the test server, the script returns no rows. A row names the database, the file and the two names. Fix a mismatch with the restart or failover above, and run the script again. Run it after every rename on the primary. Use the SQLCMD template instead when the secondary does not allow read access.

You could argue that a stale name is cosmetic, because queries and backups keep working. That is mostly true. The trouble starts in scripts. A script that reads sys.master_files on the secondary can build a statement from the old name.

What to Remember

Treat a logical name mismatch as a sign that the master copy on the secondary missed the rename. Compare the views before you trust a name. Rename logical files on the primary, and check the secondary afterward.

The fix is a restart of the database. In an availability group, that means a replica restart or a failover and back. A logical name mismatch is easy to miss, so add the check script to your routine after any file rename. Run the cleanup script when you finish with the demo.

USE master;
GO
DROP DATABASE IF EXISTS FileNameMismatchDemo;

A logical name is not a label on the file, it is a record kept in two places.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.