Get information about filegroup, data file name and path along with size in SQL Server

Get information about filegroup, data file name and path along with size in SQL Server

As a DBA, it happens quite a few times that I have to find total number of file groups available for my database along with the file that particular file group is associated with. I may also need to know the physical path of that database file along with the size of data file.

I can query “Sys.Database_files” and “sys.FileGroups” system table to find this important information about my database.

Here is small yet useful query I used to execute in order to get said information.

SELECT
dbfile.name AS DatabaseFileName,
dbfile.size/128 AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id

Here is the output of above query in my laptop:

FileGroup

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

2 thoughts on “Get information about filegroup, data file name and path along with size in SQL Server

  1. Pingback: Backup and Restore Filegroup in SQL Server

Comments are closed.