How to Find Which Files Belong to Which SQL Database

In the life of anybody who is actively engaged in SQL server support services, there will come a time when a client takes it upon themselves to refresh a database from their production environment into their dev environment for the purposes of further development on a current data set.

Now this in and of itself is usually a problem free and joyous time for our client, however for a DBA this can present a number of potential problems. The first of which being that these production databases are typically using a full recovery model and as such will continue to generate logs in the dev environment which most likely geared to handle simple recovery model DBs only… and so the SQL support technician invariably wakes up one morning to find a low disk space alarm waiting for them due to the log files blowing out.

We then move onto our next challenge; after identifying the offending log file, the DBA will need to establish which database that log file belongs to. Now as our free spirited client would most probably not have adhered to best practices for naming the database and data files in a way that is intuitive to associate the two – the SQL Support technician will then need to try and identify which database these hefty, unmaintained log files belong to.

Enters the below script which will help you to find which files belong to which SQL database, and then onto correcting the recovery model, shrinking the log file down and continuing on with your day!

select d.name as DatabaseName, m.name as LogicalName, m.physical_name as PhysicalName, size as FileSize
from sys.master_files m 
inner join sys.databases d 
on (m.database_id = d.database_id) 
order by size desc

The SQL Server Support Team
DBA SERVICES PTY LTD