2

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?

  • Through this TSQL query 'master.sys.sysaltfiles '. You can see all 'mdf' and 'ldf' files of your sql instance. – Md Haidar Ali Khan Jan 4 '17 at 6:03
  • @MdHaidarAliKhan. The answer below mentions that this view is deprecated – Zach Smith Jan 4 '17 at 9:00
  • Edited my response to answer your size question. – hot2use Jan 4 '17 at 9:55
  • 1
    @Zach Smith,the values returned by sys.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available. – Md Haidar Ali Khan Jan 4 '17 at 10:14
4

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.

  • 1
    Thank you for the help. This solution worked except that the 'name' field in sys.master_files returned a value that I couldn't find in SSMS. I've updated my question to show the solution I used. Do you know why sys.master_files.name refers to and why that name would be different to what I see in SSMS? – Zach Smith Jan 4 '17 at 9:42
  • 1
    The name in the sys.mater_files view is the logical name of the physical file and can differ from the name in the sys.databases view. You can have a database called AdminDB which was developed as Admin and attached as AdminDB2. The name in sys.master_files would display as Admin and Admin_log and the name in sys.databases view would be AdminDB2 even thought the physical file could be .....\AdminDB.mdf and ....\AdminDB_log.ldf. – hot2use Jan 4 '17 at 9:53
1

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

  • sys.database_files only showing master file (mdf & ldf). Not user database(mdf & ldf). – Md Haidar Ali Khan Jan 4 '17 at 6:09
  • Actually, this is a per-database view so it must be run under the proper database context. – SQL_Underworld Jan 4 '17 at 6:13
  • Thanks. This is a useful tool if you want to look at a single database. But I found the db name => file name correlation mostly not what I was expecting so had to look at all the DBs holistically – Zach Smith Jan 4 '17 at 9:38
1

@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

  • 1
    sys.sysaltfiles is deprecated. – SQL_Underworld Jan 4 '17 at 14:10
  • 1
    @SQL_Underworld, Yes. But as of my knowledge it's working upto SQL server 2012 and SQL server 2014. – Md Haidar Ali Khan Jan 5 '17 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.