Find Out Which Databases Have Multiple Files

By Lori Brown | Helpful Scripts

Oct 18

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. 

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.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at  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!

  • […] Lori Brown has a couple of quick scripts to help find databases made up of several data or log files: […]

  • >