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 SSMSsys.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?
Best Answer
Querying the attached databases
You should query the
sys.master_files
table like this:...or you could restrict the output to only relevant columns:
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.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:
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:
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)