Delete and create a new log file with minimum size in SQL Server

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 log file in SQL Server

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.

create new log file in SQL Server

On Attach Database box, click Add. Browser to the database (.mdf) file then click OK

Delete and create a new log file with minimum size in SQL Server

Finally, click OK. And you can see the new log file with the minimum size.

You may like following SQL Server tutorials:

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

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

>