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 :
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