Grant Carter, Senior Premier Field Engineer
Mark Weber – Principal Premier Field Engineer
Norm Eberly – Senior Premier Field Engineer
Charles Allard – Senior Premier Field Engineer
Nick Schueler - Senior Premier Field Engineer
Curt Matthews – Senior Escalation Engineer
You may discover on a readable secondary database that is part of an AlwaysOn Availability Group the data and transaction log file locations in sp_helpdb and sys.databases_files may display inaccurate results.
Results from these DMVs and stored procedures may be unexpected when executing them against the readable secondary if the readable secondary was restored with the move option and placed on a different file path as compared to the file path of the primary replica. For example, the primary replica may have its files on the E: drive and the secondary replica may have its files on the L: drive. In the example below, you will see that the database is on the E: drive, but it was manually restored using the move command to the L: drive.
On the primary replica:
create database [ag1db1] on ( name = 'ag1db1_data' , filename = 'E:\SQL\ag1db1_data.mdf' , size = 20MB , maxsize = 20MB , filegrowth = 0 ) log on ( name = 'ag1db1_log' , filename = 'E:\SQL\ag1db1_log.ldf' , size = 20MB , maxsize = 20MB , filegrowth = 0 ) go
On the secondary replica:
restore database [ag1db1] from disk = '\\sql2014n1\Backup\ag1db1.bak' with move 'ag1db1_data' to 'L:\SQL\ag1db1_data.mdf' , move 'ag1db1_log' to 'L:\SQL\ag1db1_log.ldf' , norecovery , stats=10 go
Based on the script above, you can see that the data and log paths are different between the primary and readable secondary replica. The primary files were place on the E:\SQL directory and the readable secondary files were placed on the L:\SQL directory. On the readable secondary replica, the sp_helpdb command returns a file path that is the same as the primary replica and not on the L: drive as the database restore command specified using the move option.
sp_helpdb ag1db1 go
|ag1db1_data||1||E:\SQL\ag1db1_data.mdf||PRIMARY||20480 KB||20480 KB||0 KB||data only|
|ag1db1_log||2||E:\SQL\ag1db1_log.ldf||NULL||20480 KB||20480 KB||0 KB||log only|
If sys.databases on the secondary replica is queried, the result will show the data and log files on the E: drive just as they did with sp_helpdb on the readable secondary replica which is different than the path they were restored to.
use ag1db1 go select name, physical_name from sys.database_files go
The sys.master_files view will report accurate details about database file locations at the readable secondary replica. Unlike sp_helpdb and sys.databases_files, the sys.master_files view reflects the actual paths specified in the restore with move command as opposed to the file paths for the primary replica.
use master go select name, physical_name from sys.master_files where database_id = db_id('ag1db1') go
Reason for Behavior
sp_helpdb calls a stored procedure called sp_helpfile. sp_helpfile is a stored procedure that queries a view called sys.sysfiles. sys.sysfiles gets its data from a system table called sys.sysprufiles. sys.syspruefiles is scoped at the database level. Because the secondary is a mirrored copy of the primary, the data in sys.sysprufiles reflects as it would on the primary, not the secondary, because of its scope. For this reason, the data on a readable secondary will always match the data on the primary replica.
If the availability group were to be failed over and the new readable secondary queried, it would be updated to appear as if its files were on the L: drive to reflect the new primary.
sys.database_files is a view that also uses sys.sysprufiles as its base data. Because of that, the behavior is the same as it is for sp_helpdb or sp_helpfile.
sys.master_files is a view that is scoped at the master database level that calls a system table called sys.sysbrickfiles. The metadata is maintained at the master database level and isn’t mirrored between the replicas like the database scoped system tables. For this reason, sys.master_files demonstrates the correct location of the data and log files for the database on that replica regardless of which replica is primary and which is secondary.
It is important to remember that in order to get the correct locations of where the data and transaction log files are located on a readable secondary replica, sys.master_files will give you the correct location. Relying on sys.database_files or sp_helpdb will give you an inaccurate result if the file locations between the primary and secondary replica are different.