How do you identify database .mdf and .ldf file names?

Question

In the DATA folder for SQL Server (Windows Server 2008):

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

There are two files:

xxx.ldf (63.7GB)
xxx.mdf (5.38GB)

(Incidentally why is the size in the info bar at the bottom of the windows explorer screen vastly different to the 'size' column information? The .ldf file size shows a 4GB difference).

There is no corresponding database by that name (xxx).Is there a query on system tables that I can run to show an output of:

DB name | mdf name  | log name
yardi   | xxx.mdf   | xxx.ldf
test    | test.mdf  | test.ldf
prpdb   | prpdb.mdf | prpdb.ldf
etc

I suspect that the yardi DB is related to the xxx .mdf / .ldf files, this particular database we get a .mdf dump that we restore from every morning.

==== EDIT IN RESPONSE TO ANSWERS ====

Two view are mentioned in the answers below (that I only ran in context of the master DB)

sys.databases
sys.master_files

These views are relatable by the field database_id, and surprisingly I had to use both views because the name columns in each were different.

• sys.databases.name corresponded to the names I see in SSMS
• sys.master_files.name did not correspond to the names I was seeing on the databases

The query that solved my problem is this:

USE master;
GO

SELECT
   ds.[name] as [Database name (as in SSMS)],
   mf.[name] as [master_files name],
   mf.physical_Name as [File name],
   mf.SIZE * 8 / 1024 AS SizeMB
FROM   sys.master_files mf
LEFT OUTER JOIN sys.databases ds ON mf.database_id = ds.database_id
ORDER BY ds.name ASC, SizeMB DESC

==== AND ====

The SizeMB returns a completely different size of the log file compared to what is seen in windows explorer 'size' column, and is also different to the info bar in windows explorer. By several GB. Why?

Answer 1

Querying the attached databases

You should query the sys.master_files table like this:

SELECT * FROM sys.master_files

...or you could restrict the output to only relevant columns:

SELECT NAME,
   physical_Name,
   database_id,
   FILE_ID,
   type_desc,
   state_desc,
   SIZE * 8 / 1024 AS SizeMB
FROM   sys.master_files

This will return a list of database that are attached to your SQL Server instance. This will not list databases that have been detached from your SQL Server instance.

Deprecated View sysaltfiles

You shouldn't be using the sys.sysaltfiles view as this is deprecated and has been added for backwards compatibility only as is mentioned in the reference given by MD Haidar Ali Khan.

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The sys.database_files view

The sys.database_files table as pointed out by SQL_Underworld is only for the database you are currently connected to.

Attaching database files as database

You could attach the database files you have found on your disk with the following command:

USE [master]
GO
CREATE DATABASE [xxx] ON 
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxx.mdf'),
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxx.ldf')
    FOR ATTACH
GO  

This will attach the xxx.mdf and xxx.ldf files to your SQL Server instance as database xxx. You could then rerun the above query to find information about the database.

Detaching a database

After you have found the relevant information you could then detach the database again by running the following query:

USE [master]
GO
EXEC sp_detach_db @dbname = N'xxx'
GO

Size column in sys.master_files

There is a delay in the size reported in sys.master_files according to the MSDN article sys.master_files (Transact-SQL)

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available.

Answer 2

I believe querying sys.database_files will get you what you want.

Answer 3

@Zach Smith, as you said unable to recognize the corresponding (mdf and ldf) file of particlar databse.

I would like to say that through below mention query , you shall get details of every database (mdf & ldf) files. Through DBID you can recognize the particular database (mdf & ldf) file.

select * from master.sys.sysaltfiles;
Go

or through this query you shall get all details about (mdf and ldf) files.

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id;
Go

For your further reference sys.sysaltfiles