Find Out Which Databases Have Multiple Files

This might be kind of basic but since I am working on a comprehensive script to discover things that a DBA really needs to know about, I made a couple of queries that will produce a list of the databases that have multiple files along with the locations of the physical files.  One query finds multiple database files (mdf’s) and the other looks for multiple transaction log files (ldf’s).  This will also find the Filestream file locations.  Since I often have to take on instances without ever having seen them, it is good to know about little things like this. 

Databases with multiple files is a good thing.  It allows you some flexibility with regards to file placement.  If the files are all in the same file group, it can help performance. 

SELECT DB_NAME(f.database_id) AS DBName, f.name AS LogicalName, f.type_desc AS FileType, f.physical_name AS PhysicalPath
FROM sys.master_files f
JOIN (select mf.database_id, COUNT(*) AS CntDBs
FROM sys.master_files mf
WHERE mf.type <> 1
GROUP BY mf.database_id
HAVING COUNT(*) > 1) AS X
ON (f.database_id = X.database_id)
WHERE f.type <> 1
ORDER BY DB_NAME(f.database_id), f.name

For transaction log files, having multiple files does not help performance.  However, I have come into places where it was necessary at some point to have a second log file created.  It does not hurt anything but it does not really help either but I like to know about cases like this so that I can clean up extra unnecessary files if possible.  I like to keep things as simple as possible in my production environments.

SELECT DB_NAME(f.database_id) AS DBName, f.name AS LogicalName, f.physical_name AS PhysicalPath
FROM sys.master_files f
JOIN (select mf.database_id, COUNT(*) AS CntDBs
FROM sys.master_files mf
WHERE mf.type = 1
GROUP BY mf.database_id
HAVING COUNT(*) > 1) AS X
ON (f.database_id = X.database_id)
WHERE f.type = 1
ORDER BY DB_NAME(f.database_id), f.name

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com.  We will be happy to help!  Leave a comment and feel free to track back to us.  We love to talk tech with anyone in our SQL family!