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.

My first response was to check the sys.master_files, utilizing the Dynamic Management Views (DMV), and there they were, on the H 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

‍

‍