How can I view file information on a mirror database?

Question

Post reply

How can I view file information on a mirror database?

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
November 8, 2010 at 7:00 pm

#231100

The sys.database_files catalog view does not allow querying a mirror database.
How is one to obtain info on the database files/locations of a mirror db?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

SELECT fileid ,

groupid ,

size ,

maxsize ,

growth ,

status ,

perf ,

dbid ,

name ,

filename

FROM master.sys.sysaltfiles

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Steve Jones - SSC Editor (11/8/2010)
SELECT fileid , groupid , size , maxsize , growth , status , perf , dbid , name , filename
FROM master.sys.sysaltfiles

Thank you! While I was googling, I also came across another view with similar output: sys.master_files.

Either one will do for me, thanks again!

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 3

sys.master_files is the 2005 and above equivalent of sysaltfiles. Expect sysaltfiles to be deprecated, so I recommend getting in the habit of using sys.master_files.

---------------------------------------------------------------------

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

george sibbald (11/9/2010)
sys.master_files is the 2005 and above equivalent of sysaltfiles. Expect sysaltfiles to be deprecated, so I recommend getting in the habit of using sys.master_files.

Thanks, I saw that, I'm using sys.master_files in my code.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 5

Thanks for the update, George. Old guy here used to lots of deprecated stuff that I need to update in the bio-kb.