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.

SQL SERVER - Get a Row Per File of a Database as Stored in the Master Database twofiles-800x202

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)

Database, SQL Scripts, SQL Server, SQL System Table
Previous Post
SQL SERVER – Introduction to Statistical Functions – VAR, STDEVP, STDEV, VARP
Next Post
SQLAuthority News – Fix : Remote Desktop Copy Paste Stop Working

Related Posts

2 Comments. Leave new

  • so much attention over American elections that even SQL got ELECT command?

    how about witting more than 10 lines?

  • Anton Liebscher
    January 23, 2010 10:12 pm

    Saved the day again! Many thanks, Pinal. You have saved me hours of boring work.


Leave a Reply