TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
SQL DBA Posts - What are database files? How to retrieve database files information from SQL Server?
three basic types of files in SQL Server Database, primary data file, secondary
data file and log file.
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
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 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
multiple ways to retrieve this information; however below is couple of easy
ways that you can use to grab this information quickly.
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.