How to use Table Sys.master_files In SQL
The sys.master_files system-view provides detailed information about all files associated with the SQL Server instance, including database files. We can use this view to retrieve the size of database files.
USE [YourDatabaseName];
GO
SELECT
DB_NAME(database_id) AS DatabaseName,
name AS FileName,
size/128.0 AS FileSizeInMB
FROM
sys.master_files
WHERE
type_desc = 'ROWS';
OUTPUT:
|
DatabaseName |
FileName |
FileSizeInMB |
|---|---|---|
|
YourDatabase |
YourDatabase.mdf |
5120.0000 |
Explanation:
- DB_NAME(database_id) retrieves the name of the database.
- name retrieves the name of the database file.
- size/128.0 calculates the file size in megabytes (MB). SQL Server stores the size of database files in 8KB pages, so dividing by 128 converts it to MB.
- type_desc = ‘ROWS’ filters the results to show only data files.
How to Get SQL Server Database Size
The ability to keep track of our SQL Server databases is very important to maintaining our storage resources effectively and within the proper performance limits. Knowing how large databases are can put us in a position to set up storage capacity which indicates the beginning of solving issues related to disk space, and improving database performance.
In this article, We will learn about how to Get SQL Server Database Size by understanding various methods along with the implementation and so on.