By Liran Vaiman, Valinor SLA Manager & Data Expert
Recently I encountered an issue on one of our client’s databases, which was part of an Always-On Availability Group. I needed to access the file location of this database and expected it to be on the H Drive. To make sure, I used the GUI in SSMS to find the file location. Surprisingly, it informed me that they are on the G drive.
So I figured that the GUI SSMS must be using a query that gets the data from sys.database_files. I queried the sys.database_files via the DMV, to see where the mdf files are, and, as I assumed would happen, it returned locations different from where I expected.
When I ran the following select query, to compare between the two DMVs:
SELECT A.file_id, A.physical_name AS database_files_physical_name, B.physical_name AS master_files_physical_name
FROM sys.database_files A
JOIN sys.master_files B ON A.file_guid = B.file_guid
The locations it returned were different – once on the H drive and once on the G drive.
I had to know what the reason was for this difference. I reviewed online materials and saw that the physical name listed in the sys.master_files is taken from the operating system file name. However, in regard to the physical name listed in the sys.database_files, I found the following: “If the database is hosted by an AlwaysOn readable secondary replica, physical_name indicates the file location of the primary replica database. For the correct file location of a readable secondary database, query sys.sysaltfiles.”
The reason for the difference is because of the special environment of Always-On groups. The sys.master_files view is system-wide and shows all the files on the instance you are connected to, but the sys.database_files checks where the Always-On source files were, as far as it was concerned