SQL Server Data Files and Log Files
What are These Files?
When you create a database in SQL Server, data files and transaction log files are automatically
created.
datafile (.mdf)
The data files hold the data and objects such as tables, indexes, stored procedures, and views.
logfile (.ldf)
The transaction log files record all transactions and the database modifications made by each
transaction.
Transaction log files contain the information that is required to recover all transactions in the
database.
1. Finding the Location of Data Files and Log Files
query sys.master_files system catalog view.
USE master;
SELECT
name 'Logical Name',
physical_name 'File Location'
FROM sys.master_files;
This will return a list of all data files and log files for the SQL Server instance.
Note:
"NAME" is the logical name of the file
"FILENAME" is the new path of the file.
The path in Windows path will look like this as example:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDB.mdf
2. Finding Files for a Specific Database
to find the location of the files for a particular database,
I. By one of those queries.
USE HR; USE master;
SELECT SELECT
name 'Logical Name', name 'Logical Name',
.,physical_name 'File Location' physical_name 'File Location'
FROM sys.database_files; FROM sys.master_files
WHERE database_id = DB_ID;
This will return only the data and log files for the HR database.
II. or by SQL Server Management Studio (SSMS)
right-clicking on the database name in and choosing "Properties" then "Files"
- note the filegroups in both cases
3. Define filegroup with data file, log file on creating DB
USE master;
CREATE DATABASE HR
ON PRIMARY
(NAME = 'P_HR', FILENAME = 'D:\HRDB\hr.mdf', SIZE = 8192KB, FILEGROWTH = 65536KB)
LOG ON
(NAME = 'HR_log', FILENAME = 'D:\HRDB\hr_log.ldf', SIZE = 8192KB, FILEGROWTH =
65536KB)
;
4. Add Database Files and Filegroups
ALTER DATABASE HR ADD FILEGROUP Audit_FG;
GO
ALTER DATABASE HR ADD FILE (NAME= 'S1_HR', FILENAME = 'D:\HRDB\hr1.ndf', SIZE
= 8192KB, FILEGROWTH = 65536KB),
(NAME= 'S2_HR', FILENAME = 'D:\HRDB\hr2.ndf', SIZE = 8192KB, FILEGROWTH =
65536KB)
TO FILEGROUP Audit_FG;
GO
ALTER DATABASE HR ADD LOG FILE
(NAME = 'HR_log1', FILENAME = 'D:\HRDB\hr_log1.ldf', SIZE = 8192KB,
FILEGROWTH = 5%)
5. Define FILEGROUP FOR TABLE
- Default creation
USE HR;
CREATE TABLE department(
deptname varchar(30),
building varchar(15),
primary key(deptname)
);
- On specific filegroup
CREATE TABLE auditing(
audit_num integer primary key
ON Audit_FG;
6. Moving Database Files
- As it is not mentioned in the database creation script, the database's data and log files are
created in the default location.
- Administrators often face the task of moving user database files to a new location. The
reasons for these changes can be:
separating the location of the log and data files,
moving files to a new disk due to a shortage of storage,
distributing files between several disks in order to improve performance, and soon.
Solution:
1. take the database offline.
USE master
GO
ALTER DATABASE HR SET OFFLINE
2. Then, physically move the data and log files to new locations:
3. The next step is to ALTER the database to use new paths of its files:
ALTER DATABASE HR MODIFY FILE (NAME ='S1_HR', FILENAME = 'E:\Logfile\hr1.ndf')
ALTER DATABASE HR MODIFY FILE (NAME ='HR_log1', FILENAME = 'E:\Logfile\
hr_log1.ldf')
This should be done for all the files that we want to relocate.
4. bring the database online
ALTER DATABASE HR SET ONLINE
NOTE: necessary permissions to the new folders are required to bring the database online
7. Remove Database Files
USE master
GO
ALTER DATABASE HR REMOVE FILE HR_log1
How to shrink datafile????
To new session drop DATABASE HR
