How to delete the MSSQL transaction log.

How to delete the MSSQL transaction log.

If your transaction log is big or too much large. it will utilize your space and other issue might be there, so you need to clean up the transaction log time to time. Follow the following steps and clean your LOG size.

1)  first take log backup

-- Backup on Drive
BACKUP LOG TESTDB TO DISK='d:\TEMP.bak' with init


-- Backup and Delete file
BACKUP LOG TESTDB TO DISK='NUL'

 

2) Shrink your Database.


USE TESTDB
GO
DBCC SHRINKFILE ('TESTDB_log', EMPTYFILE);
GO


From GUI mode: Rclick  DB ==> Task ==> Shirinks ==> Database ==> specify size and press OK

3) Now see your log and file size.


SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],
size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS [Available Space In MB]
FROM sys.database_files;

MSDN:
You can reduce the default size of an empty file by using DBCC SHRINKFILE target_size. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.