How do I find which database uses a file

Question

Using MS SQL Server 2005.

On one of our servers we're running out of room, so I'm going through the databases, shrinking, removing, backuping etc.

The largest file in the Data directory (an .mdf no less, not a .ldf) is not named according to our common naming-convention (it should reflect the database's name). I cannot move the file, so I know it's in use, but I have no idea which database uses it.

Is there a way of figuring this out other than traversing the many, many databases on this server?

I tried

SELECT * FROM sys.database_files

But that just gave me the files in use by the Master database.

Edit I kept looking, and found the following:

SELECT * FROM sys.sysaltfiles

This lists the filenames and the names of the files in the context of the server. It also lists the dbid, but this does not seem to be the same as the database_id in sys.databases.

Answer 1

You get the files in use by the Master database since you have selected the master as your current database. If you don't have that many databases, you can traverse all databases manually and do the select on them.

Try

SELECT * FROM sys.master_files