Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: sys.master_files vs. sys.database_files

What is the difference of the sys.master_files and sys.database_files? I have about 20 databases in my instance but when I query the sys.master_files I do not receive any rows. Why? When I query sys.database_files I get the information about the database files concerning the current database.

like image 946
juur Avatar asked Nov 13 '10 20:11

juur


People also ask

What is SYS Database_files in SQL Server?

sys. master_files lists all data files of all databases on current SQL Server instance on the other hand sys. database_files lists only database data files for current database where the query is executed on.

What is SYS Master_files?

Contains a row per file of a database as stored in the master database. This is a single, system-wide view.

How do I determine the size of my SQL Server database in GB?

If you need to check a single database, you can quickly find the SQL Server database sizein SQL Server Management Studio (SSMS): Right-click the database and then click Reports -> Standard Reports -> Disk Usage. Alternatively, you can use stored procedures like exec sp_spaceused to get database size.


1 Answers

sys.master_files :

Contains a row per file of a database as stored in the master database. This is a single, system-wide view.

sys.database_files :

Contains a row per file of a database as stored in the database itself. This is a per-database view.

So, SELECT * FROM sys.master_files should list the files for each database in the instance whereas SELECT * FROM sys.database_files should list the files for the specific database context.

Testing this here (SQL 2K8), it works as per the above?

Update: If you're not seeing rows from sys.master_files, it could be a permissions issue as BOL states:

The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

Whereas for sys.database_files just requires membership in the public role.

like image 143
AdaTheDev Avatar answered Oct 05 '22 00:10

AdaTheDev