AlwaysOn Readable Secondaries Can Display Misleading Data & Log File Paths

[mssql-support]

First published on MSDN on Jun 29, 2016
Written By:

Grant Carter, Senior Premier Field Engineer

Reviewed By:

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



Problem

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.



Scenario

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

name	fileid	filename	filegroup	size	maxsize	growth	usage
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

name	physical_name
ag1db1_data	E:\SQL\ag1db1_data.mdf
ag1db1_log	E:\SQL\ag1db1_log.ldf

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

name	physical_name
ag1db1_data	L:\SQL\ag1db1_data.mdf
ag1db1_log	L:\SQL\ag1db1_log.ldf

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.