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)
