Labels

How to delete Transaction log file in Microsoft 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.

No comments:
Write comments

Please do not enter spam links

Services

More Services