CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008mdf
Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

Any way to quickly tell which database, if any, is attached to a .mdf file?

Assume SQL Server 2005 / 2008 with a large number of databases. Is there any way to quickly tell which database, if any, is attached to a particular .mdf file?

We've dropped some databases over time and would like to clean up some lingering .mdf's to clear up space on the server. Currently the only way I know of is to look at the properties of each database, one by one, in Management Studio and make a list of the files they're attached to. Looking for something a little more efficient than this, if anything exists.

Upvotes: 8

Views: 11359

Answers (6)

Raskolnikov
Raskolnikov

Reputation: 3999

private bool IsDbAttached()
        {
            const string isAttachedSqL = @"SELECT count(*)
                                        FROM sys.master_files
                                        WHERE  DB_NAME(database_id) = @DbName";

            bool isAttached = false;
            try
            {
                using (var connection = new SqlConnection(this.connectionString))
                using (var command = new SqlCommand(isAttachedSqL, connection))
                {
                    command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = "dbName";
                    connection.Open();
                    var count = command.ExecuteScalar();
                    isAttached = (int)count > 0;
                }
            }
            catch
            {
                throw;
            }

            return isAttached;

        }

Upvotes: 0

Mark S. Rasmussen
Mark S. Rasmussen

Reputation: 35476

You could also use OrcaMDF for this:

using (var file = new MdfFile(@"C:\Database.mdf"))
{
    var bootPage = file.GetBootPage();
    Console.WriteLine(bootPage.DatabaseName);
}

This'll allow you to query the mdf's for their database name without attaching them to the database server. Note that this should be done on the primary data file, in case there are multiple files. Disclaimer - I'm the author of OrcaMDF.

Looping through all files in the data directory, it'd be easy to join that with sys.databases and see which ones don't match up, and are hence non-attached mdf files.

Edit: Posted a more thorough example on my blog: http://improve.dk/archive/2011/05/19/checking-which-database-is-stored-in-a-deattached-mdf-file.aspx

Upvotes: 4

Remus Rusanu
Remus Rusanu

Reputation: 294307

select db_name(database_id), * from sys.master_files

Will list all the files of all the databases known on the system.

Upvotes: 4

gbn
gbn

Reputation: 432271

sys.master_files contains one row per database for the first file (id = 1) for that database. That is, system tables will always be in fileid = 1 for each database

This is all you need:

SELECT
   DB_NAME(database_id), physical_name
FROM
   sys.master_files

Upvotes: 12

amit_g
amit_g

Reputation: 31250

Low tech solution... move the mdf file to another location. If it is attached, SQL server would not let you move it :)

From command prompt

cd X:\TheDir\Where\MDF\File\Are
mkdir UnusedMdf
move *.mdf UnusedDBFiles
move *.ldf UnusedDBFiles

All the unused files would be moved to UnusedDBFiles.

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

This may help.

declare @files table (
    db_name sysname,
    physical_name nvarchar(260)
)

insert into @files
    exec sp_MSforeachdb 'select "?", physical_name from ?.sys.database_files'

select db_name, physical_name 
    from @files

Upvotes: 9

Related Questions