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_filesAbove 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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
2 Comments. Leave new
so much attention over American elections that even SQL got ELECT command?
how about witting more than 10 lines?
Saved the day again! Many thanks, Pinal. You have saved me hours of boring work.