Recently I was trying to demonstrate how to move the files in Always On setup to one of my existing clients during Comprehensive Database Performance Health Check and learned something interesting. In this blog, I am going to share my learning about the mismatch between catalog views sys.master_files and sys.database_files.
Before we start let’s look at the documentation for both:
- master_files: Contains a row per file of a database as stored in the master database. This is a single, system-wide view.
- database_files: Contains a row per file of a database as stored in the database itself. This is a per-database view.
This means that the information is stored at separate places (master vs. database itself) and there could be situations when both are not in Sync state, which means the location may not be the same.
My client had a database in Always On availability group and they wanted to change the logical name of LDF file. I was able to reproduce it in my lab servers.
Create Database on Primary and take a backup so that it can be added to the availability group.
USE master GO CREATE DATABASE [SQLAuthority] ON PRIMARY (NAME = N'SQLAuthority', FILENAME = N'F:\DATA\SQLAuthority.mdf' ) LOG ON (NAME = N'SQLAuthority_xxx', FILENAME = N'F:\DATA\SQLAuthority_log.ldf') GO BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuthority.bak' WITH FORMAT GO
Create Availability group and add database created in step 1
ALTER database on primary and change the logical file name for the transaction log file.
USE [SQLAuthority] GO ALTER DATABASE [SQLAuthority] MODIFY FILE (NAME=N'SQLAuthority_xxx', NEWNAME=N'SQLAuthority_log') GO
Now check logical name between primary and secondary.
:connect PRIMARY USE master GO SELECT 'Primary',file_id ,name ,physical_name FROM sys.master_files WHERE database_id = DB_ID('SQLAuthority') GO USE SQLAuthority GO SELECT 'Primary' ,file_id ,name ,physical_name FROM sys.database_files GO :connect SECONDARY USE master GO SELECT 'Secondary' ,file_id ,name ,physical_name FROM sys.master_files WHERE database_id = DB_ID('SQLAuthority') GO USE SQLAuthority SELECT 'Secondary' ,file_id ,name ,physical_name FROM sys.database_files
Here is the output:
As you can see in highlighted that sys.master_files on secondary still have an old logical name but database_files had the correct name. I think this is because of the fact that ALTER command which we ran on primary has been replayed into the database due to Always On data movement.
To fix this mismatch of data between two catalog views, database restart is needed. Since we can’t take the database offline and online due to the availability group, we can do any of two things:
- Restart secondary replica OR
- Perform failover, make secondary as primary and again failover to get same roles as earlier.
When I looked further into ERRORLOG, it looks like “Starting up database” in ERRORLOG fixes the mismatch, which is the startup of the database. I have not tried mirroring, but I believe it would happen there as well.
Reference: Pinal Dave (https://blog.sqlauthority.com)