Recently I encountered an issue on one of our client's DB which was part of an Always On Availability Group.
When I used the GUI (SSMS) to find the DB's file location I saw that they are on the G drive (while they were actually on H drive):
To be sure, I Checked the sys.master_files DMV – surprise!, Files are on H drive…
Apparently, the SSMS is using a query that gets the data from sys.database_files.
Querying the DMV sys.database_files – showed us that the locations for the mdf files are not where they should be.
When I ran the following select, to compare between the two DMVs, I saw that for the same files – we're getting two different locations:
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
What is the reason for this difference?
When going over books online, I saw that the physical name in the sys.master_files is from the Operating-system file name.
While in the view sys.database_files, physical name indicates the "file location of the primary replica database."
If you need the correct file location of a readable secondary database, you need to query sys.master_files.
Is this a BUG in SSMS? Did Microsoft intended for this behavior?