This sql server tutorial, we will discuss what are data files and Log files in sql server? And how to delete the log file and create a new log file with a minimum size in SQL server
Data files and Log files in sql server
There are two types of operating system files named data files and log files are maintained while creating a database. A database requires minimum one data file and one log file.
Purpose of the data file is to store the data and purpose of the logfile is to record the changes made to data which will be useful in transaction management.
Datafiles are classified into primary data files and secondary datafiles. The system required information about the database and also user data is specified in the datafiles but only user data is specified in the secondary data files.
Primary datafiles are saved as the extension .mdf (master datafile),secondary datafiles are saved as the extension .ndf (next datafile) and log files are saved as the extension .ldf (log datafile).
The datafile or logfile specification contains the following five options.
- name: This option is used to provide the logical name of the file i.e, used by sql server to refer to that file.
- filename: This option is used to provide the physical name of the file along with the path where you want to save it in the hard disk.
- size: This option is used to specify the initial memory to allocate to the file which is by default taken in MB. For a log file, the size is 504KB and for a data file, the default size is 1280 KB.
- filegrowth: This option is used to specify how much increment to be done to the file size. Every time it requires additional memory. This is also by default taken in MB and this can also be specified by %.
- maxsize: This option is used to specify the maximum limit for the file size. This is also by default taken in MB. This option can be set as unlimited so that the file size will be incremented up to the maximum memory available on that drive where the file is created.
It is not compulsory that you need to write all file option and also no need to follow any order to write these options while creating a database.
Delete log file and create new log file with minimum size in SQL Server
The Microsoft SQL Server Database Transaction Log (.LDF) file becomes very huge. It’s wasting a lot of disk space and causing some problems if you want to backup and restore the database. We can delete log file and create a new log file with the minimum size.
So to delete log files, follow the Steps given below:
Backup the database and Detach the database, then Right-click on the database => Tasks => Detach.
Delete or rename the big log file (path:C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA)
Attach the database again and Right-click Databases => Attach.
On Attach Database box, click Add. Browser to the database (.mdf) file then click OK
Finally, click OK. And you can see the new log file with the minimum size.
You may like following SQL Server tutorials:
- SQL Server stored procedure tutorial
- Difference between clustered and non clustered index SQL server
- Difference between primary key and unique key in SQL Server
- Difference between primary key and foreign key in SQL server
- Enable remote connections for SQL Server
- Sequence in SQL server
- SQL Server joins tutorial
- SQL Server Functions Tutorial
- SQL DML and DDL commands
- How to Setting maximum memory in SQL server?
- Create Configure and Use SQL Server Database using Microsoft Azure
- How to create SQL Database in Microsoft Azure Portal
- Connect to Azure SQL database from management studio
Here, we learned What are Data files and Log files in sql server and how to delete and create a new log file with minimum size in SQL Server
I am Navneet Singh, Working at C3it software solutions pvt. ltd. in Pune. My core skills are SharePoint 2013, Office 365, SharePoint 2010, SharePoint Designer, SharePoint Designer Workflow, Nintex Workflow, Infopath, Webpart, CSOM/SSOM, Power-shell, C#, ASP.NET and SQL Server.
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.