How to delete Transaction log file in SQL server : Demo

We can remove log files from the database? Yes, but only if a file isn't in use, and you cannot remove the first ("primary") log file.

Deleting transaction log needs couple of steps to perform in order otherwise you can corrupt your database.

1. Take full backup of database.
2. Take transaction log backup.
3. Confirm log file is not in use.
4. Clear the log file by using shrinkfile with and parameter “Empty file”
5. Delete log file using “REMOVE FILE” option

Below code shows how to perform all steps

Example Code :

/*************Create Database *****************/

CREATE DATABASE [delete_logfile] ON  PRIMARY
( NAME = N'delete_logfile', FILENAME = N'C:\delete_logfile.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'delete_logfile_log', FILENAME = N'C:\delete_logfile_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

/*************Add log File *********/

ALTER DATABASE [delete_logfile]
ADD LOG FILE ( NAME = N'delete_logfile_log1',
FILENAME = N'C:\delete_logfile_log1.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

/***********Check log file if in use***********/

DBCC LOGINFO(delete_logfile)
----> See that log file isn't used at all (Status = 0 if log file not in use)

/**************Clear Log file before delete*************/

USE [delete_logfile]
GO
DBCC SHRINKFILE (N'delete_logfile_log1' , EMPTYFILE)
GO

/************* Delete Log file ***************/

USE [delete_logfile]
GO
ALTER DATABASE [delete_logfile]  REMOVE FILE [delete_logfile_log1]
GO
------> Output : The file 'delete_logfile_log1' has been removed.