SQL SERVER – Get a Row Per File of a Database as Stored in the Master Database

Each database has a minimum of two files associated with the database. If a database has more than one filegroup it will have many files associated with one database. Following quick script will give you recordset per file of a database which is stored in master database.

SELECT name, physical_name, type_desc, state_desc
FROM sys.master_files

Above script will return following result set.

It is very clear from this example that we can get the list of the file names from sys.master_files. Additionally, it is possible that a database has more than two data file and each of the files will be visible in the resultset.

Here are few best practices to remember:

• Keep your data and log file on a different physical drive for optimal performance
• File fragmentation does not matter as much on SSD as much as they used to matter on HDD
• It is general practice to have your TempDB on a separate physical drive
• Never run anti-virus database on your production server as it increases lots of IO and eventually reduces the performance
• Make sure to not store your Data file and Log file on a different drive than the drive where you have installed windows
• Always take backup and test the backup by restoring it frequently

Reference : Pinal Dave (https://blog.sqlauthority.com)