SQL DBA Posts - What are database files? How to retrieve database files information from SQL Server?

There are three basic types of files in SQL Server Database, primary data file, secondary data file and log file.

Primary data files 
This data file is mandatory whenever you create a new database, this file contains all the startup parameter of a database when SQL Server try to bring database online or when you create a new database on SQL Server. The common/recommended extension of primary data files is “.mdf” and it holds the system objects of a database such as tables, store procedures, views and functions.

Secondary data files
These are optional user defined data files; they hold user defined database objects such as tables, store procedures, views and functions. There could be multiple secondary files of a database. The common/recommended extension used for Secondary data files is ".ndf" 

Log Files
Log files contain transactional information of a database’s day to day processing and are very important for database recovery process. All the transaction sequence and information is stored in these files. Every database has to have one log file in order to be operational.

How to retrieve information of current database or databases files?

There are multiple ways to retrieve this information; however below is couple of easy ways that you can use to grab this information quickly.

Method 1.

Run below T-SQL in master database , Below query will give you name, physical location of the data and log files along with the sizes of these files.

select name,physical_name,size from sys.master_files

Method 2.

a)      Right click on the database in question

b)      Go to properties and click on Files
Fig 1 - How to retrieve database files information

Fig 2 - How to retrieve database files information