Mike
Tips, tricks and solutions for software developers.
 

Truncate SQL Server transaction log file

By Mike Gledhill

The code below creates a SQL Server stored procedure to let you easily truncate your database's transaction log file. It also sets the recovery method to Simple.

You run the stored procedure like this:

exec [dbo].[TruncateTransactionLog]

It will truncate your transaction log file, and display the "before" and "after" size of your log file :

truncate database

Here's the Stored Procedure script:

CREATE PROCEDURE [dbo].[TruncateTransactionLog]
AS
BEGIN
  --
  --  This Stored Procedure will truncate our Log file.
  --
  --    exec [dbo].[TruncateTransactionLog]
  --
  DECLARE @DatabaseSize nvarchar(20)
  DECLARE @DatabaseLogName nvarchar(100)
  DECLARE @OldLogSize nvarchar(20)
  DECLARE @NewLogSize nvarchar(20)
  DECLARE @SQLCmd nvarchar(2000)
  
  SELECT @DatabaseSize = cast((size*8)/1024 as nvarchar) + ' Mb'
  FROM sys.database_files
  WHERE type_desc != 'LOG'
  
  SELECT @DatabaseLogName = name,
         @OldLogSize = cast((size*8)/1024 as nvarchar) + ' Mb'
  FROM sys.database_files
  WHERE type_desc = 'LOG'
  
  IF (@DatabaseLogName IS NOT NULL)
  BEGIN
      SET @SQLCmd = 'BACKUP LOG [' + DB_NAME() + '] WITH TRUNCATE_ONLY DBCC SHRINKFILE([' + @DatabaseLogName + '], 1)'
      EXEC (@SQLCmd)
  END
  
  SELECT @NewLogSize = cast((size*8)/1024 as nvarchar) + ' Mb'
  FROM sys.database_files
  WHERE type_desc = 'LOG'
  
  SELECT DB_NAME() as 'Database name', @DatabaseSize as 'Database size', @OldLogSize as 'Old log size', @NewLogSize as 'New log size'
END

How it works

First, we have to find the internal name of your database's Log file.

SELECT name FROM sys.database_files WHERE type_desc = 'LOG'

For example, your Northwind database will probably have a log file with an internal name of Northwind_Log, but it could have any name, so we need to look up the name, before attempting to do anything with the log file.

Then, we specify the database name, and this internal Log name in the following command:

BACKUP LOG [database_name] WITH TRUNCATE_ONLY
DBCC SHRINKFILE(internal_log_name, 1)

For example:

BACKUP LOG [Northwind] WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Northwind_log, 1)

Comments